SQL Server - Script to restore all databases automatically on a given instance

The always progressive IT industry is moving at faster pace today than it ever was and this trend will going to continue in future too. This progressive industry has made everyone very familiar with words like migration, upgrade, DR (Disaster Recovery) or BCP (Business Continuity Plan) etc. as these are the activities which are being performed more often now all thanks to cheaper hardware and new versions of software releasing every year.

In database world one activity which is common to all the above stated activities and is done even more often than them is database refresh. Activities like migration and upgrade are big projects and not performed as often as activities like DR drills or pre-production database refreshes or may be setting up Always-on, which are more likely to require to restore all databases from PROD.

Implementation

Keeping this high requirement of such database refresh activity in mind, I’ve created below script which can read backups from a given location and restore them on a target SQL instance provided you’ve a functional master database with database information already available and you have your DB backups segregated in individual folder corresponding to each database. In case your master database doesn’t have the info available, even then it can be used as by default it generates the restore commands hence you can run it on your existing PROD server and can use the generated output on the destination server once backups are available there.

Note – Please watch out for comments in the scripts as they may seek some action or info to give output.

-- ==============================================================

-- Author:           Siddharth Chauhan

-- Description:      Script to restore DB or generate restore commands

-- ==============================================================

set nocount on

declare @dbn nvarchar(255), @cmd nvarchar(max), @bk_path1 nvarchar(max), @bk_path2 nvarchar(max);

set @bk_path1 = N'D:\Restore'; --provide parent backup folder here

declare cur_dbn cursor

fast_forward

for

select name from sys.databases where name not in ('master','msdb','model','tempdb');

open cur_dbn;

fetch next from cur_dbn into @dbn;

while @@FETCH_STATUS = 0

begin

       declare @dbfname nvarchar(max), @dbfpath nvarchar(max);

       declare @tmpath table(dbpath nvarchar(max))

       delete @tmpath;

      

       print '';

      

       set @bk_path2 = @bk_path1 + '\' + @dbn;

       --set @bk_path2 = @bk_path2 + '\Full';  --Uncomment in case there is sub folder as well for e.g. “Full” after database named

       set @cmd = 'EXEC MASTER..XP_CMDSHELL ''dir "' + @bk_path2 + '\*.bak" /B'';';

      

       --print @cmd

      

       insert into @tmpath

       exec (@cmd);

      

       --select * from @tmpath;

       set @cmd = 'RESTORE DATABASE [' + @dbn + '] from ';

       select @bk_path2 = dbpath from @tmpath where dbpath is not null;

       if @bk_path2 like '%The system cannot find the path specified%'

       begin

              select @cmd = '--No backup file available for database : ' + @dbn;

              goto skip;

       end

       --attaching backup files

       --assuming folding is having only one set of database backup.

       declare cur_bkp_files cursor

       fast_forward

       for select dbpath from @tmpath where dbpath is not null;

       open cur_bkp_files

       fetch next from cur_bkp_files into @bk_path2

       while @@FETCH_STATUS = 0

       begin

              if PATINDEX(@bk_path1 + '%', @bk_path2) < 1                   --Use this "if clause" in case there is no sub-directory

                     set @bk_path2 = @bk_path1 + '\' + @dbn + '\' + @bk_path2;

              --if PATINDEX(@bk_path1 + '%', @bk_path2) < 1                        --Use this "if clause" in case you’ve mentioned sub-directory above

              --     set @bk_path2 = @bk_path1 + '\' + @dbn + '\Full\' + @bk_path2;

              set @cmd = @cmd + 'disk='''+ @bk_path2 + '''' + char(10) + ', ';

              fetch next from cur_bkp_files into @bk_path2;

       end

       close cur_bkp_files;

       deallocate cur_bkp_files;

       set @cmd = SUBSTRING(@cmd, 1, len(@cmd)-1) + 'with stats=5, ';

       --moving/allocating database files

       declare cur_db_files cursor fast_forward

       for

       select name, filename from sys.sysaltfiles where dbid = DB_ID(@dbn);

      

       open cur_db_files;

      

       fetch next from cur_db_files into @dbfname, @dbfpath;

      

       while @@FETCH_STATUS = 0

       begin

      

              set @cmd = @cmd + 'move ''' + @dbfname + ''' to ''' + @dbfpath + ''', ' + char(10);

             

              fetch next from cur_db_files into @dbfname, @dbfpath;

                    

       end

      

       close cur_db_files;

       deallocate cur_db_files;

      

       set @cmd = @cmd + 'replace'; --add ",norecovery" here for always-on setups

      

       skip:

      

       print @cmd;

       print 'GO'

       print '';

      

       --exec sp_executesql @cmd; --uncomment to perform restore too

      

      

       fetch next from cur_dbn into @dbn;

      

end

close cur_dbn;

deallocate cur_dbn;