Generate a restore script for all databases

This time I will write little about database migration. Not so fancy, often just backup/restore. For the moment I participate to a large migration project and for one SQL instance the customer had nearly 700 databases used for the same system (different kind of tests).
Anyway, they wanted to move them at same time. Not the most exciting thing to do if you do it manually. I checked some existing script to generate a restore script for this but to my surprise I did not found any that worked. After a while I did create one for this purpose. First I made a stored procedure with a inputparameter for the databasename. Then I use a coursor to run in against all databases. This will make a restore script. The only thing I needed to do afterward was to change the path to backup files and datafile destinations. This is simply done with Notepad.

First the stored procedure:

CREATE PROCEDURE GenerateRestoreScript @DatabaseName VARCHAR(100)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MoveOption AS TABLE (Id INT IDENTITY(1,1), MoveOption VARCHAR(MAX))

PRINT 'RESTORE DATABASE ' + @DatabaseName

PRINT 'FROM DISK = ''' + @DatabaseName + '.bak''' 

PRINT 'WITH'

INSERT INTO @MoveOption ([MoveOption])

SELECT

'MOVE ''' + b.name + ''' TO ''' + b.physical_name + '''' AS [MOVE OPTION]

FROM sys.databases a

INNER JOIN sys.master_files b

ON a.database_id = b.database_id

WHERE a.name = @DatabaseName

DECLARE @LastId INT = 0, @MoveOptionText VARCHAR(MAX)

WHILE EXISTS (SELECT TOP 1 1 FROM @MoveOption WHERE Id > @LastId)

BEGIN

SELECT TOP 1 @MoveOptionText = MoveOption, @LastId = Id

FROM @MoveOption

WHERE Id > @LastId

ORDER BY Id ASC

PRINT CASE WHEN @LastId = 1 THEN '' ELSE ',' END + @MoveOptionText

END

PRINT ', RECOVERY'

PRINT 'GO'

END

GO

Then the coursor to run:

DECLARE @DatabaseName VARCHAR(256)

DECLARE @sql NVARCHAR(256)

DECLARE C1 CURSOR FOR

SELECT name

FROM sys.databases

ORDER BY name

OPEN C1

FETCH NEXT FROM C1 INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

   BEGIN

SELECT @sql = 'exec dbo.GenerateRestoreScript [' + @DatabaseName + ']'

            EXEC sp_executesql @sql

            FETCH NEXT FROM C1 INTO @DatabaseName

   END

CLOSE C1

DEALLOCATE C1

This will generate a quite nice looking restore script. And it also take all the filegroups in the database.

RESTORE DATABASE Test_Db

FROM DISK = 'Test_Db.bak'

WITH

MOVE 'Test_Db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db.mdf'

,MOVE 'Test_Db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_log.ldf'

,MOVE 'Test_Db_SecondaryFG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\Test_Db_FG2.ndf'

,MOVE 'Test_Db_third' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_third.ndf'

,MOVE 'Test_Db_SecondaryFG2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_SecondaryFG2.ndf'

, RECOVERY

GO