{"id":252,"date":"2014-10-18T10:10:37","date_gmt":"2014-10-18T09:10:37","guid":{"rendered":"http:\/\/www.codereview.co\/?p=252"},"modified":"2014-10-15T14:55:36","modified_gmt":"2014-10-15T13:55:36","slug":"automatic-database-restore-using-datetime-functions-in-sql-server","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/automatic-database-restore-using-datetime-functions-in-sql-server\/","title":{"rendered":"Automatic database restore using DateTime functions in SQL Server"},"content":{"rendered":"

Scenario<\/strong><\/span><\/p>\n

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?\u00a0 Check out this tip to learn more.<\/span><\/p>\n

\u00a0<\/strong><\/span><\/p>\n

Solution<\/strong><\/span><\/p>\n

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”.\u00a0 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.<\/span><\/p>\n

Let’s say we have a folder full of backups like this:<\/span><\/p>\n

\"1\"<\/p>\n

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.<\/span><\/p>\n

The below script will restore from the backup created on the first day of the current week.<\/strong>\u00a0 I’ve added comments to explain the code.<\/span><\/p>\n

— Declare variables
\nDECLARE @backup nvarchar(200)
\nDECLARE @datebegin datetime
\nDECLARE @dateend datetime<\/em><\/span><\/p>\n

— Initalize variables
\n— Set @datebegin equal to the first day of the current week
\nSELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
\n— Set @dateend equal to the second day of the current week
\nSELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1)<\/p>\n

— Set @backup equal to query dependent on datebegin and dateend
\nSELECT TOP 1 @backup = name + ‘.bak’
\nFROM msdb..backupset
\nWHERE database_name = ‘Alabama’
\nAND backup_start_date BETWEEN @datebegin AND @dateend
\nAND type = ‘D’ — D is for full backups
\nORDER BY backup_start_date ASC<\/p>\n

USE [master]<\/p>\n

— Put DB in Single_User Mode
\nALTER DATABASE [Tide] SET SINGLE_USER WITH ROLLBACK IMMEDIATE<\/p>\n

— Restore DB using query from @backup variable
\nRESTORE DATABASE [Tide] FROM\u00a0 DISK = @backup WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
\nGO<\/p>\n

Below is a table of useful DateTime functions that you can use for the @datebegin and @dateend variables.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Day<\/strong><\/span><\/td>\nSQL<\/strong><\/span><\/td>\n<\/tr>\n
Today<\/span><\/td>\nSELECT GETDATE()<\/span><\/td>\n<\/tr>\n
Yesterday<\/span><\/td>\nSELECT DATEADD(d, -1, GETDATE())<\/span><\/td>\n<\/tr>\n
First Day of Current Week<\/span><\/td>\nSELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)<\/span><\/td>\n<\/tr>\n
Last Day of the Current Week<\/span><\/td>\nSELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)<\/span><\/td>\n<\/tr>\n
First Day of the Current Month<\/span><\/td>\nSELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)<\/span><\/td>\n<\/tr>\n
Last Day of the Current Month<\/span><\/td>\nSELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))<\/span><\/td>\n<\/tr>\n
First Day of the Current Year<\/span><\/td>\nSELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)<\/span><\/td>\n<\/tr>\n
Last Day of the Current Year<\/span><\/td>\nSELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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:<\/span><\/p>\n

–Set @datebegin equal to the first day of the current month<\/em><\/span>
\nSELECT @datebegin = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) <\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Set @dateend equal to the second day of the current month<\/em><\/span>
\nSELECT @dateend = SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1)<\/em><\/span><\/p>\n

A quick and easy way to see if your DateTime functions are correct is to use the following:<\/span><\/p>\n

–Declare variables<\/em><\/span>
\nDECLARE @backup nvarchar(200) <\/em><\/span>
\nDECLARE @datebegin datetime<\/em><\/span>
\nDECLARE @dateend datetime<\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Set @datebegin equal to the first day of the current week<\/em><\/span>
\nSELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) <\/em><\/span>
\n\u00a0<\/em><\/span>
\n–Set @dateend equal to the second day of the current week<\/em><\/span>
\nSELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) <\/em><\/span>
\nSELECT TOP 1 @backup = name + ‘.bak’ <\/em><\/span>
\nFROM msdb..backupset <\/em><\/span>
\nWHERE database_name = ‘Alabama’ <\/em><\/span>
\nAND backup_start_date BETWEEN @datebegin AND @dateend <\/em><\/span>
\nORDER BY backup_start_date ASC<\/em><\/span>
\n\u00a0<\/em><\/span>
\nPRINT @datebegin<\/em><\/span>
\nPRINT @dateend<\/em><\/span>
\nPRINT @backup<\/em><\/span>
\nAfter running this query you should return the dates and the backup name that will be used in the RESTORE statement:<\/span><\/p>\n

\"2\"<\/p>\n

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.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

Thanks for reading this article,<\/strong><\/span><\/p>\n

Next steps :<\/strong><\/span><\/p>\n

    \n
  1. Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/span><\/li>\n
  2. Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Scenario 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?\u00a0 Check out this tip to learn more. \u00a0 Solution If you take full backups […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[8,117,118,9,13,12],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/252"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=252"}],"version-history":[{"count":3,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/252\/revisions"}],"predecessor-version":[{"id":373,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/252\/revisions\/373"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=252"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}