Restore Database

When a DBA restores a database, three restore phases must happen. In the copy phase, the database image is created and initialized on disk, and then the full backup is copied. That can be followed by any differential and transaction-log backups. These are done via the RESTORE T-SQL command.

               After the full backup has been applied and any differential and transaction logs have been restored, the DBA allows recovery to run. During the recovery process, SQL Server performs both a redo phase and an undo phase. During the redo phase, all committed transaction records that were in the transaction log but not in the data files are written to the data files. Then, during the redo, SQL Server evaluates the transaction-log records and applies the data modifications to the data files in the database.

               The duration of the redo phase depends on how many data modifications SQL Server 2008 performed, which depends on what SQL Server was doing at the time of the failure and the recovery interval setting. For example, if SQL Server 2008 just finished updating 10 million rows from a table and committed the transaction but was unexpectedly shut down right after, during recovery it would have to redo those data modifications to the data. The SQL Server 2008 recovery interval setting influences recovery time according to how many dirty pages are kept in memory before the checkpoint process must write them to stable media. By default, the recovery interval is set to 0, which means that SQL Server keeps less than a minute of work that is not yet written to the data files. With that setting, during recovery, there is minimal redo work before the database becomes available for users. The higher the recovery interval value, the longer the recovery may take.

               After the redo phase is the undo phase, where any transactions that did not complete are rolled back. Depending on the amount of work and the length of the transactions at the time before shutdown, this phase can take some time. For example, if the DBA was in the middle of deleting 10 million rows, SQL

Server 2008 is required to roll back all those rows during recovery. SQL Server 2008 does make the database available to users while in the undo phase, but users should expect some performance impact while in the redo phase. RESTORE VERIFYONLY does not work on database snapshots. If you are planning to revert (restore) from a database snapshot, then use DBCC CHECKDB to ensure that the database is healthy.

SQL Server provides granular permission for both backup and restoring a database. A Windows or SQL Server authenticated user or group can be given permission to perform the backup and restore operations. To have permission to back up a database, a user must have at minimum the following permissions:

❑ Server role: none

❑ DB role: db_backupoperator

To restore a database, a user must have at minimum the following permissions:

❑ Server role: dbcreater

❑ DB role: db_owner

The RESTORE HEADERONLY option allows you to see the backup header information for all backups for a particular backup device.

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'

The RESTORE LABELONLY option allows you to see the backup media information for the backup device.  So if a backup device, such as a backup file, has multiple backups you will only get one record back that gives you information about the media set, such as the software that was used to create the backup, the date the media was created, etc...

RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.  So for example if you have a full backup you will see all of the data files (mdf) and the log file (ldf).

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 1

The RESTORE DATABASE option allows you to restore either a full, differential, file or filegroup backup.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'

The RESTORE LOG command allows you to restore a transaction log backup.  The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark. 

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'

The RESTORE VERIFYONLY command checks the backup to ensure it is complete and the entire backup is readable. The does not do an actual restore, but reads through the file to ensure that SQL Server can read it in the event that a restore using this backup needs to occur.

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK