SQL Server Log Shipping -- Complete Overview.. Part- IV

Next Chapter »

Stored Procedure to Restore Database Backups

Here’s the script I use for creating the “restore_database_backups” stored procedure:

CREATE PROCEDURE restore_database_backups AS

RESTORE DATABASE database_name
FROM DISK = ‘g:mssql7backupdatabase_namedatabase_name_backup_device.bak’
WITH
DBO_ONLY,
REPLACE,
STANDBY = ‘g:mssql7backupdatabase_nameundo_database_name.ldf’,
MOVE ‘logical_name’ TO ‘h:mssql7datadatabase_name.mdf’,
MOVE ‘logical_name’ TO ‘f:mssql7logdatabase_name_log.ldf’

WAITFOR DELAY ’00:00:05′

EXEC sp_dboption ‘database_name’, ‘single user’, true

Where “database_name” refers to the name of the database being restored and where “logical_name” refers to the logical name of the database and log files, respectively.

Now let’s discuss this script one line at a time.

The “CREATE PROCEDURE” line should be self-explanatory for anyone who has ever created a stored procedure.

The “RESTORE DATABASE” line is the command to restore a database.

The “FROM DISK” line is used to tell the “RESTORE DATABASE” command where to find the database backup device. You will of course have to specify a path that is appropriate for your server, along with the appropriate backup device name.

The “WITH” line is used to let us know there are some options coming up.

The “DBO_ONLY” option is optional and sets the database to “DBO USE ONLY” after it is restored. I have included it to help keep out people who should not be in this database once it is restored.

The “REPLACE” option tells the “RESTORE DATABASE” command to replace any pre-existing database with the same name. This is required to prevent errors when restoring the database over an older version of the database.

The “STANDBY” line tells the “RESTORE DATABASE” command not to rollback any uncommitted transactions and to allow read-only access to the database. This option requires you to specify a file, such as, “undo_database_name.ldf” to be used to “undo” any transactions. Locate this file in an appropriate path.

The “MOVE” lines tells the “RESTORE DATABASE” command that the physical files that make up the database will be in a different location on the standby server than they were on the production server. You must specify the path to the physical names of the database and log files on the standby server.

The “WAITFOR” line causes the script to pause for 5 seconds before it executes the next line of code. I have added it here because I found that if you don’t, that the previous “RESTORE DATABASE” command may not be 100% complete before the following command executes. If it isn’t, an error message occurs.

The “EXEC” line is used to put the restored database in “SINGLE USER MODE”. Each time you restore a database, the “RESTORE DATABASE” command takes the database out of “SINGLE USER MODE”. If you don’t set this now, when it comes time to restore again using this stored procedure, you may get an error message if there are any user connections to the database when the stored procedure runs. While this shouldn’t normally happen, I have seen it happen, and this line of code prevents the stored procedure from failing.

Stored Procedure to Restore Log Backups

Wow, that was a lot, but here’s more. Here’s the script for creating the restore_log_backupsstored procedure. Notice how similar it is to the restore_database_backups stored procedure.

CREATE PROCEDURE restore_log_backups AS

RESTORE LOG database_name
FROM DISK = ‘g:mssql7backuplogdatabase_namedatabase_name_log_backup_device.bak’
WITH
DBO_ONLY,
STANDBY = ‘g:mssql7backuplogdatabase_nameundo_database_name.ldf’

WAITFOR DELAY ’00:00:05′

EXEC sp_dboption ‘database_name’, ‘single user’, true

Where “database_name” refers to the name of the database being restored.

Now let’s discuss this script one line at a time.

The “CREATE PROCEDURE” line should be self-explanatory.

The “RESTORE LOG” line is the command to restore a transaction log.

The “FROM DISK” line is used to tell the “RESTORE LOG” command where to find the transaction log backup device. You will of course have to specify a path that is appropriate for your server, along with the appropriate backup device name.

The “WITH” line is used to let us know there are some options coming up.

The “DBO_ONLY” option is optional and sets the database to “DBO USE ONLY” after the transaction log has been restored. I have included it to help keep out people who should not be in this database once it is restored.

The “STANDBY” line tells the “RESTORE LOG” command not to rollback any uncommitted transactions and to allow read-only access to the database. This option requires you to specify a file, such as, “undo_database_name.ldf” to be used to “undo” any transactions. Locate this file in an appropriate path.

The “WAITFOR” line causes the script to pause for 5 seconds before it executes the next line of code. I have added it here because I found that if you don’t, that the previous “RESTORE LOG” command may not be 100% complete before the following command executes. If it doesn’t, an error message occurs.

The “EXEC” line is used to put the restored database in the “SINGLE USER MODE”. Each time you restore a transaction log, the “RESTORE LOG” command takes the database out of “SINGLE USER MODE”. If you don’t set this now, when it comes time to restore again using this stored procedure to restore a transaction log, you may get an error message if there are any user connections to the database. While this shouldn’t normally happen, I have seen it happen, and this line of code prevents this stored procedure from failing.

Enough already. At least this is enough of these two stored procedures. Unfortunately, the detail gets worse before it gets better. Next, you will learn how to create two SQL Server jobs that perform the database and transaction log backups.