How to Auto Backup and Restore Databases with TSQL during Server Migration

During migration, backup and restoring databases might be a hectic task. However with this Automated Backup and Restoration script you do not have to worry about it anymore.

SQL Database MigrationDuring migration, taking a backup and restore for databases might be a hectic task. Now is it easy with this Automated Backup and Restoration script which enables the user to have a stress free migration activity.

This script has to be executed on the server from which you are taking the database backup. The script will automatically connect to the Linked Server and restore databases.

--The linked server has to be created with RPC OUT option set to TRUE
-- RPC OUT = True allows execution of script on linked server 
--example for query execution on linked server exec ('Select * from [LINKED SERVER NAME].master.dbo.sysdatabases')
DECLARE @dbname VARCHAR(50) -- database name  
DECLARE @backuppath VARCHAR(256) -- please use UNC path for backup files
DECLARE @backupfileName VARCHAR(256) -- filename for backup  
DECLARE @backupfileDate VARCHAR(20) -- used for the format of backup file name
DECLARE @mdfpath varchar(2000) -- path for storing MDF files
DECLARE @ldfpath varchar(2000) -- path for storing MDF files
Create table #script
(c1 varchar(8000)) 
-- specify database backup directory
SET @backuppath = '\<BACKUP WILL BE TAKEN TO THIS FOLDER>' --please make sure the path ends with \
-- specify MDF directory
SET @mdfpath = <DURING RESTORATION MDF FILES WILL BE PLACED HERE>'  
-- specify LDF directory
SET @ldfpath = '<DURING RESTORATION LDF FILES WILL BE PLACED HERE>'  
 
-- specify filename format
SELECT @backupfileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE cursor_toholddbnames CURSOR FOR  
select name from sys.databases where state_desc = 'ONLINE' and 
name = 'TEST_DB_TEST'
--name NOT IN ('master','model','msdb','tempdb')  -- include database names here to exclude them from Backup and restoration task 
OPEN cursor_toholddbnames   
FETCH NEXT FROM cursor_toholddbnames INTO @dbname   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @backupfileName = @backuppath + @dbname + '_' + @backupfileDate + '.BAK'  
       --print @backupfileName
       BACKUP DATABASE @dbname TO DISK = @backupfileName  with copy_only
       
       DECLARE cursor_toholddbnames2 CURSOR FOR  
       Select name,filename from master.dbo.sysaltfiles where db_name(dbid) = @dbname
       declare @string varchar(8000), @dbname2 varchar(8000), @mdfldf varchar(8000), @ldfcount integer, @ndfcount integer
       set @ldfcount = 0
       set @ndfcount = 0
       set @string = 'Restore database [' + @dbname + ']' + cast(char(10) as varchar) + 'from disk = ' + cast(char(39) as varchar) + @backupfileName + cast(char(39) as varchar) + cast(char(10) as varchar) + 'WITH ' 
       open cursor_toholddbnames2
       fetch next from cursor_toholddbnames2 into @dbname2, @mdfldf
       while @@fetch_status = 0
       begin
       if charindex('.mdf',@mdfldf,1) <> 0 begin
       set @string = @string + 'move ' + cast(char(39) as varchar) + @dbname2 + cast(char(39) as varchar) + ' to ' + cast(char(39) as varchar) + @mdfpath + @dbname + '.MDF' + cast(char(39) as varchar) + cast(char(10) as varchar) + ','
       end
       if charindex('.ndf',@mdfldf,1) <> 0 begin
       set @ndfcount = @ndfcount + 1
       if @ndfcount = 1 begin
       set @string = @string + 'move ' + cast(char(39) as varchar) + @dbname2 + cast(char(39) as varchar) + ' to ' + cast(char(39) as varchar) + @mdfpath + @dbname + '.NDF' + cast(char(39) as varchar) + cast(char(10) as varchar) + ','
       end 
       if @ndfcount > 1 begin
       set @string = @string + 'move ' + cast(char(39) as varchar) + @dbname2 + cast(char(39) as varchar) + ' to ' + cast(char(39) as varchar) + @mdfpath + @dbname + cast(@ndfcount as varchar) + '.NDF' + cast(char(39) as varchar) + cast(char(10) as varchar) + ','
       end 
       end
       if charindex('.ldf',@mdfldf,1) <> 0 begin
       set @ldfcount = @ldfcount + 1
       if @ldfcount = 1 begin
       set @string = @string + 'move ' + cast(char(39) as varchar) + @dbname2 + cast(char(39) as varchar) + ' to ' + cast(char(39) as varchar) + @ldfpath + @dbname + '_LOG.LDF' + cast(char(39) as varchar) + cast(char(10) as varchar) + ','
       end 
       if @ldfcount > 1 begin
       set @string = @string + 'move ' + cast(char(39) as varchar) + @dbname2 + cast(char(39) as varchar) + ' to ' + cast(char(39) as varchar) + @ldfpath + @dbname + '_LOG' + cast(@ldfcount as varchar) +  '.LDF' + cast(char(39) as varchar) + cast(char(10) as varchar) + ','
       end
       end
       fetch next from cursor_toholddbnames2 into @dbname2, @mdfldf
       end
            CLOSE cursor_toholddbnames2   
            DEALLOCATE cursor_toholddbnames2
            set @string = @string + 'stats = 1' --+ cast(char(10) as varchar) + 'GO' + cast(char(10) as varchar)
            print @string 
            insert into #script values(@string)
            
            
            exec (@string) at [<LINKED SERVER NAME>]
            
       FETCH NEXT FROM cursor_toholddbnames INTO @dbname   
END   
CLOSE cursor_toholddbnames   
DEALLOCATE cursor_toholddbnames
--Select * from #script
drop table #script

Restore a Corrupt Database

Recovery SQL DatabasesIf you backup your database manually in the past, and you can restore the backup because it is corrupt. You need to find an effective SQL db recovery tool to fix the corruption and help you restore everything back to normal status.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook error and excel recovery software products. For more information visit www.datanumen.com

4 responses to “How to Auto Backup and Restore Databases with TSQL during Server Migration”