Thursday, February 20, 2014

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

1 comment:

David Hutton said...

Great Script. Thank you very much.

David