SQL Server Database Recovery

The purpose of this article is to detail the manual SQL server database recovery process more commonly known as “a database restore”. SQL server databases can and will recover automatically for example if SQL Server is shut down unexpectedly then when the SQL Service starts up again the SQL Server will recover its databases by rolling forward or back any committed or uncommitted transactions still in the log.

The manual SQL Server database recovery process involves the use of a good full and/or transaction log backup and using these files to restore the database in question to point in time.

For a list of excellent reasons why you need to backup you SQL Server database then have a read of this article. http://www.gre-sqlserver-solutions.com/Backup.html

What you need to recover?

Depending on your recovery mode T/Log backups…We will use T/Log backup in this exercise.

This article will look at a SQL server database in Full recovery mode…we use a Full backup to recover the database and then apply T/Log backups to recover the data to a point in time.

If you are restoring a production database to a point in time then Books Online (BOL) – the help documentation supplied by SQL Server suggests the following process:

You can restore a database to the state it was in at the point of failure if the current transaction log file for the database is available and undamaged. To restore the database to the point of failure:

1. Back up the currently active transaction log. For more information, see Transaction Log Backups.
2. Restore the most recent database backup without recovering the database
3. If a differential backups exist, restore the most recent one.
4. Restore each transaction log backup created since the database or differential backup in the same sequence in which they were created without recovering the database.
5. Apply the most recent log backup (created in Step 1), and recover the database.

The SQL Server database recovery process (Database Restore)

GUI

6. The next screen is the under the general page you can see that the database to be restored is the database that we right-clicked on earlier. It also has the latest backup set for us to restore. If you wanted to do this on a file by file basis you would have select the option in the source for restore section. After ensuring that you have the correct database to restore the correct restore point and the correct backup files…select the tab.


7. on the next screen you get the restore options and restore state. In this case I would select overwrite existing database as I want to force the current database to be over written.

8. When you click ok the restore will begin. (as long as no other users are connected to the database). If you do not have exclusive access to the database you will get the following error returned

9. Ensure you have exclusive access to the database and try the restore again, if you restore is successful you will get the following message.

T-SQL

This section details the manual SQL Server Database Recovery process (Performing a Restore) using T-SQL. The following T-SQL script will perform the exact same restore as described above using the GUI but this time it uses T-SQL. The alter database statement sets the database to single user…which counters the problem we experienced above about having users access. The ROLLBACK IMMEDIATE command rolls back any open transactions so any spids can be killed off immediately.

USE RecoveryTest
GO
ALTER DATABASE RecoveryTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE Master
go

RESTORE DATABASE [RecoveryTest]
FROM DISK = N'C:\DATA\Backup\RecoveryTestFullBackup'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [RecoveryTest]
FROM DISK = N'C:\DATA\Backup\RecoverTestLogBackupOne.TRN'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [RecoveryTest]
FROM DISK = N'C:\DATA\Backup\RecoverTestLogBackupTwo.trn'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
SQL Server Database Recovery