Agent Jobs

Create Database Restore Template Syntax

Here’s some quick SQL logic that will backup all databases to a folder, then create database restore template syntax for each backup file that was created.

This is the process..
1. Backup all databases to a particular folder.
2. Create a list of all backup files found in that folder.
3. Produce restore logic based on each file that is found.

The results are outputted as a single XML string, and once clicked it will show you all the syntax for restores across every file that is found.

The logic is dependent on xp_dirtree, and a simple variable table.

use master;
set nocount on

-- backup all databases
exec master..sp_msforeachdb
'if (''?'') not in (''tempdb'')
                        backup database [?] to disk = ''F:\MyBackupFolder\?.bak'' with format, checksum;

-- use backup path and create variable table to store list of backup files
declare @path               varchar(255) = 'F:\MyBackupFolder\'
declare @restoreall       varchar(max)
declare @filetable         table
            subdirectory      varchar(255)
,           depth                int
,           [file]               int

insert    into @filetable
exec master..xp_dirtree @path, 1, 1

-- create logic for xml output
set        @restoreall      = ''
select    @restoreall       = @restoreall + 
            'restore database [' + upper(replace(subdirectory, '.bak', '')) + '] 
                        from disk = ''F:\MyBackupFolder\' + subdirectory + '''' + char(10) + 
            'with replace, recovery,' + char(10) +
            ',           move '''' to '''''    + char(10) +
            ',           move '''' to '''''    + char(10) +
            ',           stats = 10'''        + char(10)
from     @filetable
where   subdirectory not in ('master.bak', 'msdb.bak', 'model.bak')  order by subdirectory asc

-- produce complete restore syntax for all backup files
select    @restoreall for xml path (''), type



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s