Generate Database Restore Scripts

Moving a set of databases to new storage? This script walks every online user database, reads its file layout, and prints a ready-to-run RESTORE DATABASE statement for each one - with the backup location templated as @BackupLocation and MOVE clauses pointing data files at @NewDatLocation and log files at @NewLogLocation. The , and placeholders in the path templates are substituted automatically.

SET NOCOUNT ON;



DECLARE @BackupLocation varchar(256) SET @BackupLocation = 'X:\SQLBackups\<databasename>\<databasename>_FULL.bak'
DECLARE @NewDatLocation varchar(256) SET @NewDatLocation = 'U:\SQLData\<physicalfile>'
DECLARE @NewLogLocation varchar(256) SET @NewLogLocation = 'L:\SQLData\<physicalfile>'

DECLARE @tSQL VarChar(MAX)
DECLARE @DatabaseName sysname
DECLARE @FileName sysname
DECLARE @FileType sysname
DECLARE @PhysicalFile nVarChar(256)

CREATE TABLE #DatabaseFiles
(
	[FileName] sysname,
	[FileType] sysname,
	[PhysicalFile] nvarchar(256)
)

DECLARE DatabaseList CURSOR FAST_FORWARD FOR
	SELECT
		name
	FROM
		sys.databases
	WHERE
		state_desc = 'ONLINE'
		AND name NOT IN ('master','tempdb','model','msdb')
OPEN DatabaseList
FETCH FROM DatabaseList INTO @DatabaseName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	----------------------------------------------------------------------------------------------------------------------------------
	TRUNCATE TABLE #DatabaseFiles

	SET @tSQL =
		'INSERT INTO #DatabaseFiles' + CHAR(13) + CHAR(10) +
		'SELECT' + CHAR(13) + CHAR(10) +
			'name as [FileName],' + CHAR(13) + CHAR(10) +
			'type_desc AS [FileType],' + CHAR(13) + CHAR(10) +
			'SUBSTRING([physical_name], LEN([physical_name]) - CHARINDEX(''\'', Reverse([physical_name])) + 2, 256) AS [PhysicalFile]' + CHAR(13) + CHAR(10) +
		'FROM' + CHAR(13) + CHAR(10) +
			'[' + @DatabaseName + '].sys.database_files' + CHAR(13) + CHAR(10)
	EXEC(@tSQL)
	
	DECLARE DatabaseFiles CURSOR FAST_FORWARD FOR
		SELECT
			[FileName],
			[FileType],
			[PhysicalFile]
		FROM
			#DatabaseFiles
	OPEN DatabaseFiles
	FETCH FROM DatabaseFiles INTO @FileName, @FileType, @PhysicalFile
	
	SET @tSQL = 'RESTORE DATABASE [' + @DatabaseName + ']' + CHAR(13) + CHAR(10) +
		'FROM DISK = ''' + @BackupLocation + '''' + CHAR(13) + CHAR(10) +
		'WITH ' + CHAR(13) + CHAR(10)
		
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		----------------------------------------------------------------------------------------------------------------------------------

		IF @FileType = 'ROWS'
		BEGIN--IF
			SET @tSQL = @tSQL + 'MOVE ''' + @FileName + ''' TO ''' + @NewDatLocation + ''','
		END ELSE IF @FileType = 'LOG' BEGIN
			SET @tSQL = @tSQL + 'MOVE ''' + @FileName + ''' TO ''' + @NewLogLocation + ''','
		END--IF

		SET @tSQL = @tSQL + CHAR(13) + CHAR(10)

		SET @tSQL = Replace(@tSQL, '<databasename>', @DatabaseName)
		SET @tSQL = Replace(@tSQL, '<filename>', @FileName)
		SET @tSQL = Replace(@tSQL, '<physicalfile>', @PhysicalFile)

		----------------------------------------------------------------------------------------------------------------------------------
		FETCH FROM DatabaseFiles INTO @FileName, @FileType, @PhysicalFile
	END--WHILE

	SET @tSQL = @tSQL + 'RECOVERY, STATS=10' + CHAR(13) + CHAR(10) +
		'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

	PRINT @tSQL

	CLOSE DatabaseFiles DEALLOCATE DatabaseFiles
	
	----------------------------------------------------------------------------------------------------------------------------------
	FETCH FROM DatabaseList INTO @DatabaseName
END--WHILE

CLOSE DatabaseList DEALLOCATE DatabaseList

DROP TABLE #DatabaseFiles