Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Restore a SQL Server Database to a New Database to Minimize Downtime


By:   |   Last Updated: 2009-08-05   |   Comments   |   Related Tips: More > Restore

Problem

At our organization we have a number of existing processes that backup and restore databases for reporting and disaster recovery purposes. As our databases have grown we have began to use a third party product to minimize the backup and restore times. Unfortunately, we need to shrink the restore time even further.  I have seen your tip about using differential backups in a log shipping like scenario. I could see how these would require less time and disk space, but it would be a huge process and mentality change for us.  We issue full backups across all of our databases and would like to continue to do so for consistency's sake.  Do you know of any other options to reduce our backup and restore times?

Solution

As you have mentioned using a third party tool for backup and recovery due to the compression options could offer a great deal of assistance in terms of the time and disk space needed.  These tools should help your overall backup and recovery time as well as your storage needs. One final thought on the compression note, is that SQL Server 2008 offers compression capabilities in the enterprise edition of the product.  So you have the ability to issue a compressed backup with enterprise edition and restore to all SQL Server 2008 editions.

The other item you mentioned was using differential backups. They could also help from a timing and storage perspective, but that does not seem like a viable option due to the process change.  For more information about incorporating differential backups into your SQL Server backup routine check out - Differential Database Backups for SQL Server.

One final thought is to issue a restore to a new database name then issue database rename commands.  This could keep the downtime to a minimum since the restored database on your reporting and disaster recovery SQL Servers would only be inaccessible when the renames occur.  You would also be able to continue with your full backups and not have a different backup paradigm on these particular SQL Server instances.  Unfortunately, with this approach twice the amount of storage is needed on the reporting\disaster recovery SQL Servers during the restore process.  If you have the disk space then it could be a very simple restore code change to make this work.  Let's take a look at some sample code to do so assuming this is a viable option in your environment.

Backup Logic

With the restore and rename change that was outlined, modifications to the backup code should not be necessary assuming you are already backing up the data and restoring to another SQL Server.  As a point of reference, here are some backup related tips:

Create Directory

In this script we will create a sub-directory based on the current date in the root directory specified.

-- 1 - Declare variables
DECLARE @CMD1 varchar(8000)
DECLARE @RestoreRootDirectory varchar(255)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8) 

-- 2 - Initialize variables
SET @CMD1 = ''
SET @RestoreRootDirectory = 'C:\Test\'
SET @CurrentDate = GETDATE()
SET @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)

-- 3 - Create the directory for the current day
SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'mkdir ' + 
@RestoreRootDirectory + @CurrentName + '\' + char(39) 
EXEC(@CMD1) 

Restore Database

This portion of the logic is intended to restore the database to a directory based on the date and restore the database with a date suffix.  In this example, many parameters are hard coded in order to duplicate the logic on other instances.  In a production environment, the hard coded parameters should be updated with variables to automate the restore process across your environment.

-- 1 - Declare variables
DECLARE @CMD1 varchar(8000)
DECLARE @DatabaseName varchar(128)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8)
DECLARE @BackupLocation varchar(128)
DECLARE @RestoreRootDirectory varchar(255)
DECLARE @RestoreParameters varchar(255) 

-- 2 - Initialize variables
SET @CMD1 = ''
SET @DatabaseName = 'AdventureWorks'
SET @CurrentDate = GETDATE()
SET @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)
SET @BackupLocation = 'C:\Backup\AdventureWorks.bak'
SET @RestoreRootDirectory = 'C:\Test\'
SET @RestoreParameters = 'WITH MOVE ' + char(39) + 'AdventureWorks_Data' 
+ char(39) + ' TO ' + char(39) + @RestoreRootDirectory + @CurrentName + 
'\' + 'AdventureWorks_Data.mdf' + char(39) + ',' + CHAR(13) + 'MOVE ' + 
char(39) + 'AdventureWorks_Log' + char(39) + ' TO ' + char(39) + 
@RestoreRootDirectory + @CurrentName + '\' + 'AdventureWorks_Log.ldf' + char(39)

-- 3 - Build the restore statement
SELECT @CMD1 = 'RESTORE DATABASE [' + @DatabaseName + '_' + @CurrentName + ']' + char(13) + 'FROM DISK =' + char(39) + @BackupLocation + char(39) + char(13) +  @RestoreParameters + char(13) 
EXEC(@CMD1) 

Rename Databases

Below are two scripts to rename the databases to minimize downtime. In both scripts spids connected to the database are killed prior to the rename process.

--Rename the current database to the previous date

-- 1 - Variable declarations
DECLARE @DatabaseName varchar(128)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8)
DECLARE @PreviousName varchar(8)
DECLARE @CMD1 varchar(8000) 
DECLARE @SQL1 VARCHAR(8000) 

-- 2 - Initialize variables
SET @DatabaseName = 'AdventureWorks'
SET @CurrentDate = GETDATE()
SET @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)
SET @PreviousName = CONVERT(varchar(8), @CurrentDate-1, 112)
SET @CMD1 = ''
SET @SQL1 = ''

-- 3 - Delete the spids for @DatabaseName i.e. 'BizTime2'
SELECT @SQL1=COALESCE(@SQL1,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; ' 
FROM master.dbo.sysprocesses 
WHERE DBID=DB_ID(@DatabaseName)
AND spid > 50
EXEC(@SQL1)

-- 4 - Rename the live database to previous database name
SELECT @CMD1 = 'EXEC sp_renamedb ' + @DatabaseName + ', ' + @DatabaseName + '_' + @PreviousName
EXEC(@CMD1)

--Rename the current database to the original name

-- 1 - Variable declarations
DECLARE @DatabaseName varchar(128)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8)
DECLARE @PreviousName varchar(8)
DECLARE @CMD1 varchar(8000) 
DECLARE @SQL1 VARCHAR(8000) 

-- 2 - Initialize variables
SET @DatabaseName = 'AdventureWorks'
SET @CurrentDate = GETDATE()
SET @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)
SET @PreviousName = CONVERT(varchar(8), @CurrentDate-1, 112)
SET @CMD1 = ''
SET @SQL1 = ''

-- 3 - Delete the spids for @DatabaseName i.e. 'BizTime2_TodaysDate' 
SELECT @SQL1=COALESCE(@SQL1,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; ' 
FROM master.dbo.sysprocesses 
WHERE DBID=DB_ID(@DatabaseName + '_' + @CurrentName) 
AND spid > 50
EXEC(@SQL1)

-- 4 - Rename current database to the live database
SELECT @CMD1 = 'EXEC sp_renamedb ' + @DatabaseName + '_' + @CurrentName + ', ' + @DatabaseName
EXEC(@CMD1)

Drop Database and Delete the Previous Directory

In the script below, the previous database is dropped and the associated directory is also deleted.

-- 1 - Variable declarations
DECLARE @DatabaseName varchar(128)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8)
DECLARE @PreviousName varchar(8)
DECLARE @CMD1 varchar(8000) 
DECLARE @SQL1 VARCHAR(8000) 
DECLARE @RestoreRootDirectory varchar(255) 

-- 2 - Initialize variables
SELECT @DatabaseName = 'AdventureWorks'
SET @CurrentDate = GETDATE()
SET @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)
SET @PreviousName = CONVERT(varchar(8), @CurrentDate-1, 112)
SET @CMD1 = ''
SET @SQL1 = ''
SET @RestoreRootDirectory = 'C:\Test\'

-- 3 - Delete the spids for @DatabaseName i.e. 'BizTime2_TodaysDate' 
SELECT @SQL1=COALESCE(@SQL1,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; ' 
FROM master.dbo.sysprocesses 
WHERE DBID=DB_ID(@DatabaseName + '_' + @PreviousName)
AND spid > 50 
EXEC(@SQL1)

-- 4 - Drop the previous database
SET @CMD1 = ''
SELECT @CMD1 = 'DROP DATABASE ' + @DatabaseName + '_' + @PreviousName
EXEC(@CMD1)

-- 5a - Error handling for the restore process
SELECT [Name] 
FROM master.dbo.sysdatabases 
WHERE [Name] = @DatabaseName + '_' + @PreviousName

-- 5b - Error handling for the restore process
IF @@ROWCOUNT = 1
BEGIN
RAISERROR ('7h1 - The previous database name was found in master.dbo.sysdatabases', 16, 1)
RETURN
END

-- 6 - Drop the previous directory
SET @CMD1 = ''
SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'rmdir ' + @RestoreRootDirectory + @PreviousName + '\ /q' + char(39) 
EXEC(@CMD1)

-- 7 - Test the error value
IF @@ERROR <> 0 
BEGIN
RAISERROR ('7i - Previous directory was not deleted', 16, 1)
RETURN
END
Next Steps


Last Updated: 2009-08-05


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools