How to restore 100 databases from backups in MSSQL

Moving databases between servers means creating a backup copy on the old server and deploying that backup on the new server. With one or two databases there is not much hassle, but what do you do if you have more than a hundred of them? Your fingers will fall off from pressing the buttons that many times.

I already wrote about how to back up a big number of MSSQL databases. Below the talk goes about automating the deployment of the databases.

Create a StoredProcedure. For that you have to run the following query in the NewQuery window

IF OBJECT_ID ('restoreDB') IS NOT NULL
 DROP PROCEDURE restoreDB
GO
CREATE PROC [dbo].[restoreDB]
   @p_strDBNameTo SYSNAME,
   @p_strFQPathTo VARCHAR(255),
   @p_strFQNRestoreFileName VARCHAR(255)
AS
   DECLARE
		@p_strDBNameFrom SYSNAME,
       @v_strDBFilename VARCHAR(100),
       @v_strDBLogFilename VARCHAR(100),
       @v_strDBDataFile VARCHAR(100),
       @v_strDBLogFile VARCHAR(100),
       @v_strExecSQL NVARCHAR(1000),
       @v_strExecSQL1 NVARCHAR(1000),
       @v_strMoveSQL NVARCHAR(4000),
       @v_strREPLACE NVARCHAR(50),
       @v_strTEMP NVARCHAR(1000),
       @v_strListSQL NVARCHAR(4000),
       @v_strServerVersion NVARCHAR(20)

	SET @p_strDBNameFrom  = @p_strDBNameTo	-- 'DB name From' = 'DB Name To'
   IF exists (select name from sys.databases where name = @p_strDBNameTo)
       SET @v_strREPLACE = ', REPLACE'
--    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##FILE_LIST'))
--    BEGIN
     DROP TABLE ##FILE_LIST
--    END

   SET @v_strListSQL = ''
    SET @v_strListSQL = @v_strListSQL + 'IF (EXISTS (SELECT * FROM [tempdb].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''##FILE_LIST''))'
    SET @v_strListSQL = @v_strListSQL + 'BEGIN'
    SET @v_strListSQL = @v_strListSQL + '   DROP TABLE [tempdb].##FILE_LIST '
    SET @v_strListSQL = @v_strListSQL + 'END '
   SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
   SET @v_strListSQL = @v_strListSQL + '   LogicalName VARCHAR(64),'
   SET @v_strListSQL = @v_strListSQL + '   PhysicalName VARCHAR(130),'
   SET @v_strListSQL = @v_strListSQL + '   [Type] VARCHAR(1),'
   SET @v_strListSQL = @v_strListSQL + '   FileGroupName VARCHAR(64),'
   SET @v_strListSQL = @v_strListSQL + '   Size DECIMAL(20, 0),'
   SET @v_strListSQL = @v_strListSQL + '   MaxSize DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   FileID bigint,'
   SET @v_strListSQL = @v_strListSQL + '   CreateLSN DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   DropLSN DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   UniqueID UNIQUEIDENTIFIER,'
   SET @v_strListSQL = @v_strListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   ReadWriteLSN DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   SourceBlockSize INT,'
   SET @v_strListSQL = @v_strListSQL + '   filegroupid INT,'
   SET @v_strListSQL = @v_strListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
   SET @v_strListSQL = @v_strListSQL + '   differentialbaseLSN DECIMAL(25,0),'
   SET @v_strListSQL = @v_strListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
   SET @v_strListSQL = @v_strListSQL + '   isreadonly BIT,'
   SET @v_strListSQL = @v_strListSQL + '   ispresent BIT'

   SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

   IF @v_strServerVersion LIKE '10.0%' or @v_strServerVersion LIKE '11.0%'
       BEGIN
           SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
           --PRINT 'Verson’ + @v_strServerVersion
       END

   SET @v_strListSQL = @v_strListSQL + ')'
--	print 'EXECUTING QUERY:' + @v_strListSQL	C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data
   EXEC (@v_strListSQL)


   INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')

   DECLARE curFileLIst CURSOR FOR
--        SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(PhysicalName, @p_strDBNameFrom, @p_strDBNameTo) + ''''
       SELECT 'MOVE N''' + LogicalName + ''' TO N''' + @p_strFQPathTo + substring(PhysicalName, (len(PhysicalName)-charindex('\', reverse(PhysicalName))+1), 200) + ''''
         FROM ##FILE_LIST

   SET @v_strMoveSQL = ''

   OPEN curFileList
   FETCH NEXT FROM curFileList into @v_strTEMP
   WHILE @@Fetch_Status = 0
   BEGIN
       SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
       FETCH NEXT FROM curFileList into @v_strTEMP
   END

   CLOSE curFileList
   DEALLOCATE curFileList

   PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'

   -- Create the sql to kill the active database connections
   SET @v_strExecSQL = ''
   SELECT   @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
   FROM     master.dbo.sysprocesses
   WHERE    DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid

   EXEC (@v_strExecSQL)

   PRINT 'Restoring "' + @p_strDBNameTo + '" database'
   PRINT 'from dbname @p_strDBNameFrom "' + @p_strDBNameFrom + '" database'
	PRINT ' from (p_strFQNRestoreFileName) "' + @p_strFQNRestoreFileName + '" with '
   PRINT ' data file "' + @v_strDBDataFile  + '"'
	PRINT ' located at (v_strDBFilename)"' + @v_strDBFilename + '"'
   PRINT ' log file (v_strDBLogFile)"' + @v_strDBLogFile + '"'
	PRINT ' located at (v_strDBLogFilename)"' + @v_strDBLogFilename + '"'
	PRINT ' @v_strMoveSQL = ' + @v_strMoveSQL

   SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
   SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
   SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
   SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
   SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
   SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
   SET @v_strExecSQL = @v_strExecSQL + ', REPLACE '
--    SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE


   PRINT '---------------------------'
   PRINT @v_strExecSQL
   PRINT '---------------------------'

   EXEC sp_executesql @v_strExecSQL

After you see the Command(s) completed successfully. message you can restore the databases with the following command:

exec restoreDB 'database_name', 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data', 'c:\folder_name\database_name.BAK'

In the case of a 2012 server you have to change the path to the DATA folder:

exec restoreDB 'database_name', 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA', 'c:\folder_name\database_name.BAK'

Now the biggest pain - making a batch in the unfriendly windows command line to restore a hundred databases:
Go to the folder where the database backups are (c:\folder_name) and run the following command:

for /r %f in (*.bak); do echo exec restoreDB '%~nf', 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA', 'c:\folder_name\%~nf.BAK' >> c:\\db_restore.txt

As a result the c:\\db_restore.txt file will contain a set of commands. You can edit it all if needed. It is still easier than typing the restore of different databases 100 times.

Go back to SQL Management Studio and run the huge query from the text file.