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'') begin backup database [?] to disk = ''F:\MyBackupFolder\?.bak'' with format, checksum; end' -- 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
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds