Backup & Restore Script with a Move

Published On: 2018-02-16By:

Ok, I’ll admit it. I like scripts that are handy and do things.  Especially if the scripts make my life easier.

Now, not every environment is the same.  Instances get configured differently or things change just due to the nature of the business.  In a previous life I would routinely have to backup a database and restore it to another server.  However, the server I was using to restore to had a different drive configuration.  It happens.  Anyway, I wanted a script that would give me

  • A backup statement
  • A restore statement with the appropriate MOVE switch

This would allow me to easily execute the backup statement and then on the target server execute the restore statement.  Since the restore statement already had the appropriate MOVE switch provided, I didn’t have to manually compose the statement.

First, we will declare some variables just to make things a little cleaner and easier

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

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

Next, we’ll use a CTE to get the database name(s) and the file paths.  The CTE is what does the work to create the MOVE switch.

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''D:\SQLData\'
                                            ELSE ' TO ''E:\SQLTLogs\'
                                        + REVERSE(LEFT(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

The CTE is now populated with the statement that has the MOVE switch.  We can now do another SELECT from sys.databases with an INNER JOIN to the CTE.

'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 = movecmdcte.databasename
WHERE LIKE '%DatabaseName%'

You can adjust the WHERE clause for just a single database or multiple.  Of course, you would want to adjust all of the UNC path statements for your particular environment.

You can download the entire script here.


© 2018, John Morehouse. All rights reserved.

Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link