Quick Backup/Restore Script

By bradyupton in Backup/Restore, Upgrades/Migrations 0 Comment

Throughout the years I’ve probably performed hundreds if not thousands of migrations or “refreshes” to lower environments. It’s pretty simple to run a quick maintenance plan or SQL job to backup all databases, but the more complex piece is creating a restore script to point to that backup location and the backup name. It can be done, but it takes a little longer, IMO. A lot of backup file names contain numeric characters such as date or time of backup.

John Morehouse created a dynamic script that simplifies everything and it can be easily modified to fit particular needs. I’ve had this script bookmarked for years and use it at least once a week. Kudos to John for sharing this with the SQL community. I’ll link the script below, but here’s a quick snippet.

DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
          AS ( SELECT DISTINCT
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''D:\SQLData\'
                                            ELSE ' TO ''E:\SQLTLogs\'
                                          END
                                        + REVERSE(LEFT(REVERSE(physical_name),
                                                       CHARINDEX('\',
                                                              REVERSE(physical_name),
                                                              1) - 1)) + ''''
                               FROM     sys.master_files sm1
                               WHERE    sm1.database_id = sm2.database_ID
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
               FROM     sys.master_files sm2
  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + movecmdCTE.MoveCmd
FROM sys.databases d
	INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

To change the backup/restore path you can modify this section of code to fit your needs:

SET @path = '\\UNCPath\Folder\'

Another modification I perform initially is changing the where clause, for example, if I don’t want system databases I can change:

WHERE d.name LIKE '%DatabaseName%'

to

WHERE d.database_id > 4

A lot of times I don’t need to move the data and log files to a new location because I’m overwriting other databases so I just comment all of the MoveCmdCTE CTE code out:

DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

--;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
--          AS ( SELECT DISTINCT
--                        DB_NAME(database_id) ,
--                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
--                                        + CASE Type
--                                            WHEN 0 THEN ' TO ''D:\SQLData\'
--                                            ELSE ' TO ''E:\SQLTLogs\'
--                                          END
--                                        + REVERSE(LEFT(REVERSE(physical_name),
--                                                       CHARINDEX('\',
--                                                              REVERSE(physical_name),
--                                                              1) - 1)) + ''''
--                               FROM     sys.master_files sm1
--                               WHERE    sm1.database_id = sm2.database_ID
--                        FOR   XML PATH('') ,
--                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
--               FROM     sys.master_files sm2
--  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' --+ movecmdCTE.MoveCmd
FROM sys.databases d
	--INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

There’s a lot of option here and this script is quick and easy.

Link to script: https://gist.github.com/airtank20/a826c6f37439482edd5070e8aaeb1ee1

Creating a Backup plan on SQL Express using Ola Hallengren’s scripts

By bradyupton in Backup/Restore, Maintenance 0 Comment

SQL Express doesn’t have the SQL Server Agent so we can’t schedule jobs like normal. Follow this post to create a backup plan that will back up all the databases using Windows Task Scheduler.

  • Download CommandExecute – https://ola.hallengren.com/scripts/CommandExecute.sql
  • Download DatabaseBackup – https://ola.hallengren.com/scripts/DatabaseBackup.sql
  • Execute both of these stored procedures against the target server

Backups with Ola 1

  • Open Notepad on the target server and copy the following code changing the path to where the backups will be stored and the cleanup time. The cleanup time is specified in hours :
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backups',
@BackupType = 'FULL',
@Compress = 'Y', 
@CleanupTime = 48
  • Save the file with a .sql extension

Backups with Ola 2

  • Start the Task Scheduler by clicking on StartMenu/All Programs/Accessories/System Tools/Task Schedule
  • Click Create Basic Task to start the Scheduled Task Wizard
  • Type a name for the Task

Backups with Ola 3

  • Choose Daily from the scheduling options

Backups with Ola 4

  • Click Next, specify the information about the time to run the task. Set Start Time to an appropriate value when the load on the server is low. Set the recur every option to 1 day and click Next

Backups with Ola 5

  • Click Browse. Browse to SQLCMD.exe (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE) and then click Open
  • Type the following content to the Add arguments text box and then click Next
    • –S ServerName –i “c:\SQLScripts\SQLBackups.sql”

Backups with Ola 7

  • Select the checkbox to Open the Advanced Properties for this task and click Finish

Backups with Ola 8

  • Check Run whether user is logged in or not and Run with highest privileges under Security Options then click OK.

Backups with Ola 9

**Note**

You may have to run this in the command prompt until it succeeds and copy exactly from CMD into the arguments section

Restore SQL Server Databases using DateTime functions

By bradyupton in Backup/Restore 0 Comment

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:

SQL Freelancer SQL Server Backup Restore Datetime Functions

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)

Click here to view the rest of this post.