Migrate Backup Files

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 6 Jun 2020

Description

Generates XCOPY Scripts to copy all database backups from the source server to the destination server

GENERATE script on: SOURCE SERVER

EXECUTE script on: SOURCE SERVER (from command prompt)

Code


--*******************************************--**** RUN THIS ON THE SOURCE SERVER ***--*******************************************

-- use this script to generate backup file copy scripts for every non-system DB on a server.-- save each DB script to a separate file using the file name at the top of the section
SET NOCOUNT ON

--*******************************************--**** Define and set parameter values ***--*******************************************
DECLARE @DatabaseName SYSNAME ,@DatabaseID INT ,@FilePath SYSNAME ,@DestinationServerName SYSNAME
DECLARE @CopyBackupList TABLE( DatabaseID INT ,DatabaseName SYSNAME)
DECLARE @CopyBackupResults TABLE( ResultID INT IDENTITY (1,1) ,ResultText NVARCHAR(MAX))

SELECT @FilePath = '\D$\DBA\Temp\' --supply a path name to usre for the backup command ,@DatabaseName = NULL --optionally, supply a DB name for generaton of command for a single database ,@DestinationServerName = 'XXXXXX' --

INSERT @CopyBackupList (DatabaseID, DatabaseName)SELECT dbid, nameFROM master..sysdatabasesWHERE name NOT IN ('master','model','tempdb','msdb','reportserver','reportservertempdb', 'distribution')AND name = ISNULL(@DatabaseName, name)AND status & 512 = 0 -- not offlineAND status & 4096 = 0 -- not single user mode

SELECT @DatabaseID = MIN (DatabaseID)FROM @CopyBackupList
WHILE @DatabaseID IS NOT NULLBEGIN SELECT @DatabaseName = DatabaseName FROM @CopyBackupList WHERE DatabaseID = @DatabaseID

 INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ START SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT '::**********************************************************************************' INSERT @CopyBackupResults (ResultText) SELECT '::***** Purpose: Transfer backup of [' + @DatabaseName + '] ready for restore' INSERT @CopyBackupResults (ResultText) SELECT '::***** Generated on: ' + CONVERT(VARCHAR(12), GETDATE(), 112) INSERT @CopyBackupResults (ResultText) SELECT '::***** Generated by: ' + SUSER_SNAME() INSERT @CopyBackupResults (ResultText) SELECT '::**********************************************************************************' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT 'XCOPY "\\' + @@SERVERNAME + @FilePath + @DatabaseName + '_For_Migration.bak" "\\' + @DestinationServerName + @FilePath + '" /Y' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END SCRIPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT '::' INSERT @CopyBackupResults (ResultText) SELECT '::'

 SELECT @DatabaseID = MIN (DatabaseID) FROM @CopyBackupList WHERE DatabaseID > @DatabaseID
END
SELECT ResultTextFROM @CopyBackupResultsORDER BY ResultID
SET NOCOUNT OFF

040. Migrate backup files.sqlOpen withDisplaying 040. Migrate backup files.sql.