SQL DATABASE RECOVERY TECHNIQUES

The backup and restore component of Microsoft® SQL Server™ provides an important safeguard for protecting critical data stored in SQL Server databases.

With proper planning, you can recover from many failures, including:

• Media failure.
• User errors.
• Permanent loss of a server.

As curators of SQL Server databases, we are one day faced with the most dreadful state of database failure. We would have to restore a set of backups in a logically correct and meaningful restore sequence to retrieve all data or as much as possible. Native SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data page.

Recovery Model

Which recovery model is best for the databases in your environment? This setting depends on the criticality of the data and the acceptable data loss in the event of a system failure. SQL Server offers three recovery models that can be implemented for your databases. The appropriate choice depends on your applications and the criticality of your data. These settings can be configured either through SSMS or through T-SQL using the ALTER DATABASE command.

The three database recovery model options are:

1. Simple – With the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log (or incremental changes) backups are not available. The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss because the contents of the database transaction log are truncated each time a checkpoint is issued for the database.
2. Full – The Full Recovery model uses database backups and transaction log backups to provide complete protection against failure. If one or more data files are damaged, restoring the backups permits recovery of all committed transactions using a combination of the database and transaction log backups. Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged and recoverable.

• It backs up the database files, the locations of those files, and portions of the transaction log (from the LSN recorded at the start of the backup to the LSN at the end of the backup).
• All other backup types depend on the existence of a full backup.
• This means you can’t use a differential or transaction log backup if you have never performed a full backup.

3. Bulk Logged – The Bulk-Logged Recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, Bulk load operations, CREATE INDEX as well as text and image operations. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually based on the operations above that are not fully logged. In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.

Backing up databases is the second most important task that a DBA should perform. So what is the most important task? Recovering databases. This is why it is important that a DBA regularly take backups. However, coupled with taking the backups, the DBA should regularly test those backups. The best way to perform these tests is to restore the backups. As a best practice, a process should be put in place to ensure the validity of the backups through restoration. In most cases, a separate instance of SQL Server is configured and the databases are restored on that server. After the restore, user and application access is a good test of whether or not the data is available and valid.

Using SQL Server, you can restore the following:

• An entire database
• A page in a database
• A part of a database
• The database transaction log
• A file or filegroup in a database

As you can see, you have several options available when restoring databases, ensuring that you have the flexibility to address most scenarios that may arise. SQL Server allows you to restore databases using both T-SQL and SSMS.

Prior to restoring a database, you should always consider exactly what you are going to restore. This is especially important when restoring using T-SQL. Assume that you must restore a full backup, a single differential, and single transaction log backup, in that order. When restoring the FULL and DIFFERENTIAL backup, you must include the NORECOVERY keyword in the WITH clause.

Restore Commands

The restore commands are equivalent to the backup commands in terms of syntax. You have the option to execute database or transaction log restores. In addition, there are more commands available that permit checking the validity of the backup file as well as read the contents of the backup file prior to executing a restore.
Specifies the complete restore of the database from a backup device. This can either be a full database, differential or a filegroup restoration. If a list of files and filegroups is specified, only those files and filegroups are restored.

Database:

TSQL

RESTORE DATABASE {databasename} FROM {device} 

Sample: 
USE [master]
RESTORE DATABASE [DBAHQ] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\Backup\DBA.bak' 
WITH FILE = 1, NOUNLOAD, STATS = 5

GO

RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY

Sample:
USE [master]
RESTORE DATABASE [DBAHQ] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\Backup\DBA.bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD,STATS = 5

GO

SQL SERVER MANAGEMENT STUDIO – GUI INTERFACE

Filegroup:

TSQL

RESTORE DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} FROM {device} WITH NORECOVERY 

Sample:
RESTORE DATABASE [football_test] FILE = N'Football',  FILE = N'Football5',  FILE = N'Football6',  
FILE = N'Football8',  FILE = N'Football9',  FILE = N'Football10',  FILE = N'Football11' 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\Backup\Football.bak' WITH  FILE = 1,  
MOVE N'Football' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test.mdf',  
MOVE N'Football5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_0.ndf',  
MOVE N'Football6' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_1.ndf',  
MOVE N'Football8' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_2.ndf',  
MOVE N'Football9' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_3.ndf',  
MOVE N'Football10' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_4.ndf',  
MOVE N'Football11' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_5.ndf',  
MOVE N'Football_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL\MSSQL\DATA\football_test_6.ldf',  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

SQL SERVER MANAGEMENT STUDIO – GUI INTERFACE

Log:

Specifies a transaction log restore is to be applied to the database. Transaction logs must be applied in sequential order from the oldest backup to the most recent backup. SQL Server checks the backed up transaction log to ensure that the transactions are being loaded in the correct database and in the correct sequence. To apply multiple transaction logs, use the NORECOVERY option on all restore operations except the last restore command where the database recovery is needed. In addition, a transaction log restore must be executed following the database restore.

RESTORE LOG {databasename} FROM {device} WITH NORECOVERY 

Sample:
RESTORE LOG [football_test] 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.TSTSQL2K14DEV\MSSQL\Backup\Football.trn' 
WITH FILE=4, NORECOVERY
GO

SQL SERVER MANAGEMENT STUDIO – GUI INTERFACE

Restore

• Regularly rehearse recovering your database before the expected disaster occurs.
• Suspect or corrupt databases aren’t the only reasons to perform restores, though.
• You may, for example, need to send a copy of one of your databases to the home office or to a branch office for synchronization.
• The restore operation will likely miss recovering the last transactions entered by users, so be sure to alert your users of the need to restore.
• Train them how to verify that the last few entries still exist after your restoration effort.
• Some corporations train users to maintain today’s records until tomorrow in case a daily backup and subsequent restore misses all of yesterday’s data entries.

 

Recovery Option

• The RECOVERY option, when set incorrectly, can thwart all your efforts to restore a database.
• The RECOVERY option tells SQL Server that you’ve finished restoring the database and that users should be allowed back in.
• Use this option only on the last file of the restore process.

Restoring to a Location

• SQL Server also remembers where the original files were located when you backed them up.
• Thus, if you backed up files from the D:\ drive, SQL Server will restore them to the D:\ drive.
• In this instance, you need to use the MOVE…TO option. MOVE…TO lets you back up a database in one location and move it to another location.

 

Restore Check

• Finally, before allowing you to restore a database, SQL Server performs a safety check to make sure you aren’t accidentally restoring the wrong database.
• SQL Server compares the database name being restored to the name of the database recorded in the backup device.

 

Online Restore

• Normally a database restore requires exclusive access to the database.
• For larger databases, a restore operation can take hours.
• When the Enterprise Edition of SQL Server is in use, the online option for restores is available.
• Online restores are automatic and require the use of different filegroups so that different filegroups, files, or even individual pages can be restored.
• This could be extremely useful in the event of a torn or damaged page in a large highly busy database.

 

Did you know?

Stellar Database Recovery has been recovering databases since 1993 with over 2 million+ satisfied customers. The Stellar Phoenix SQL Database toolkit is a very powerful but yet very intuitive  which performs all the above-mentioned methods of recovering your database in an automatic way with no specialized access or permissions and programming knowledge. Do you have a corrupt .mdf or .ndf file that needs to be recovered? Look no further because the Stellar SQL Database toolkit is a combination of three tools developed to recover database from corrupt SQL server, extract database from corrupt SQL backup file and to reset SQL Server Password.

Click here to learn more!