Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Restore SQL Server Databases using DateTime functions

MSSQLTips author Brady Upton By:   |   Read Comments (3)   |   Related Tips: More > Dates
Problem

I have to restore a production database to a development database on a weekly basis in order to refresh the environment. Currently, I'm doing this process manually. Is there any way to script this out so it can be automated?  Check out this tip to learn more.

Solution

If you take full backups using SQL Server maintenance plans and let SQL Server use the default naming convention, you have probably noticed that usually you'll have file name in the format of "database name + backup + date + time + .bak". For example, a backup from the master database may look like this: "master_backup_2012_10_02_220558_8601773.bak".  It can be a challenge to script out automatic restores because the numbers on the end of the backup name constantly change. In this tip I will explain how to script out RESTORE DATABASE statements using DateTime functions.

Let's say we have a folder full of backups like this:

A folder full of SQL Server database backups

Let's say our boss wants us to restore Monday's production backup (Alabama) every Friday afternoon to our development database (Tide). To accomplish this task, we can use the built-in SQL Server DateTime functions.

The below script will restore from the backup created on the first day of the current week.  I've added comments to explain the code.

-- Declare variables
DECLARE @backup nvarchar(200) 
DECLARE @datebegin datetime
DECLARE @dateend datetime

-- Initalize variables
-- Set @datebegin equal to the first day of the current week
SELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
-- Set @dateend equal to the second day of the current week 
SELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) 

-- Set @backup equal to query dependent on datebegin and dateend 
SELECT TOP 1 @backup = name + '.bak' 
FROM msdb..backupset 
WHERE database_name = 'Alabama' 
AND backup_start_date BETWEEN @datebegin AND @dateend 
AND type = 'D' -- D is for full backups
ORDER BY backup_start_date ASC 


USE [master]

-- Put DB in Single_User Mode
ALTER DATABASE [Tide] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

-- Restore DB using query from @backup variable
RESTORE DATABASE [Tide] FROM  DISK = @backup WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 
GO

Below is a table of useful DateTime functions that you can use for the @datebegin and @dateend variables.

Day SQL
Today SELECT GETDATE()
Yesterday SELECT DATEADD(d, -1, GETDATE())
First Day of Current Week SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Last Day of the Current Week SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)
First Day of the Current Month SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
Last Day of the Current Month SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
First Day of the Current Year SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Last Day of the Current Year SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

Another example may include where you need to take a backup from the first of the month of the production database and restore it weekly to the development database. In this situation you can edit the @datebegin and @dateend variables:

--Set @datebegin equal to the first day of the current month
SELECT @datebegin = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 

--Set @dateend equal to the second day of the current month
SELECT @dateend = SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1)

A quick and easy way to see if your DateTime functions are correct is to use the following:

--Declare variables
DECLARE @backup nvarchar(200) 
DECLARE @datebegin datetime
DECLARE @dateend datetime

--Set @datebegin equal to the first day of the current week
SELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 

--Set @dateend equal to the second day of the current week
SELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) 
SELECT TOP 1 @backup = name + '.bak' 
FROM msdb..backupset 
WHERE database_name = 'Alabama' 
AND backup_start_date BETWEEN @datebegin AND @dateend 
ORDER BY backup_start_date ASC

PRINT @datebegin
PRINT @dateend
PRINT @backup

After running this query you should return the dates and the backup name that will be used in the RESTORE statement:

After running this query you should return the dates and the backup name that will be used in the RESTORE statement

From looking at the results returned I can determine that the @datebegin variable equals Monday, Oct 1, the @dateend variable equals Tuesday, October 2, and the backup that will be used for the restore is the backup created on Monday, Oct 1 at 10:15 PM.

Next Steps
  • To learn more about Date and Time functions click here and check out these tips.
  • This script only applies to full backups, but by changing the type in the script you can add differential and log backups as well.


Last Update: 12/21/2012


About the author
MSSQLTips author Brady Upton
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, April 03, 2013 - 1:35:36 AM - HAMZA BOHARI Read The Tip

Thanks Man,  Great helpful stuff.....


Sunday, December 23, 2012 - 11:22:47 PM - Shailendra Read The Tip

Hi,

I want to know that is there any way to decrypt the encrypted storted procedure?


Friday, December 21, 2012 - 10:54:12 PM - Srinath Read The Tip

Thats really good piece of code. Thank you Brady for such a great post!!




 
Sponsor Information