By: Jeremy Kadlec | Comments | Related: > 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
- Depending on your needs be sure to properly evaluate the options outlined in this tip. Although the focus of this tip was on one option, two others may exist that could meet your needs.
- The restore portion of the logic intentionally used hard coded parameters, if you intend to use this code in production, be sure to update the logic to use variables.
- Check out these related backup and recovery tips:
- For more information about killing spids or gaining exclusive access to a database, reference the following tips:
- Have some challenging backup and recovery needs? Please post them in the forums below and we may feature your post as an upcoming tip.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
