Restore All Databases Script

Question

I am migrating SQL Server DBs to a new instance.

I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.

Does anyone know of a sample script to do this?

Thanks!

Answer 1

I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure'

http://www.sqlservercentral.com/articles/Restore+database/95839/

Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) and give you a script to generate them. Also generates the DBCC CHECKDB commands to check the DBs after restoring.

I actually just used this script about an hour ago so I can do a test restore and verify my backups.

Answer 2

there is a very good script on the link below:

How to generate database restore script in SQL Server

Based on that script I have created my stored procedure below, which works very well, although it does not generate the DBCC CHECKDB:

it needs 3 parameters:

@Database sysname - the database name you want to generate the restore
@MovePathLog NVARCHAR(1008) - where to move the transaction log file to
@MovePathData NVARCHAR(1008) - where to move the data file(s) to

Here is the code:

use master
go
IF OBJECT_ID('sp_genRestoreScripts') IS NOT NULL
 DROP PROCEDURE sp_genRestoreScripts
GO
create procedure sp_genRestoreScripts
@Database sysname,
@MovePathLog NVARCHAR(1008),
@MovePathData NVARCHAR(1008)
as begin
DECLARE @FullMediaSetID NVARCHAR(20)
DECLARE @BackupSetID NVARCHAR(20)
DECLARE @FullPath NVARCHAR(400)
DECLARE @FullPosition NVARCHAR(20)
DECLARE @LogMediaSetID NVARCHAR(20)
DECLARE @LogPath NVARCHAR(400)
DECLARE @LogPosition NVARCHAR(20)
DECLARE @DiffMediaSetID NVARCHAR(20)
DECLARE @DiffPath NVARCHAR(400)
DECLARE @DiffPosition NVARCHAR(20)
DECLARE @SQLMove NVARCHAR(MAX)
SET @SQLMove =''
IF ( @MovePathData <> '' OR @MovePathLog <> '')
BEGIN
 DECLARE @LogicalName NVARCHAR(200)
 DECLARE @FileID NVARCHAR(10)
 DECLARE @FileType NVARCHAR(10)
 DECLARE @ExtName NVARCHAR(20)
 DECLARE MoveCur CURSOR FOR 
 SELECT m.name
 ,m.file_id
 ,m.type 
 FROM sys.master_files m 
 INNER JOIN sys.databases d 
 ON m.database_id = d.database_id
 WHERE d.name = @Database
 OPEN MoveCur
 FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @ExtName = CASE 
 WHEN @FileID = 1 THEN '.mdf'
 WHEN @FileID > 1 THEN CASE @FileType 
 WHEN 0 THEN '.ndf' 
 ELSE '.ldf' 
 END
 END
 SET @SQLMove = @SQLMove + CHAR(10) + ' , MOVE ' + '''' + @LogicalName + '''' 
 + ' TO ' + '''' + 
 CASE @FileType 
 WHEN 0 THEN @MovePathData 
 ELSE @MovePathLog
 END
 + @LogicalName + @ExtName + ''''
 FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType 
 END
 CLOSE MoveCur
 DEALLOCATE MoveCur
END
SELECT 
 @BackupSetID = backup_set_id,
 @FullMediaSetID = media_set_id, 
 @FullPosition = position
FROM msdb.dbo.backupset 
WHERE backup_set_id =
(
 SELECT MAX(backup_set_id) 
 FROM msdb.dbo.backupset 
 WHERE database_name = @Database 
 AND type='D'
 GROUP BY database_name
)
SELECT @FullPath = physical_device_name 
FROM msdb.dbo.backupmediafamily 
WHERE media_set_id = @FullMediaSetID
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.backupset 
 WHERE database_name = @Database AND type IN('L','I') AND backup_set_id > @BackupSetID)
BEGIN
 PRINT REPLICATE('-',200)
 PRINT '-----------//Full backup restore only'
 PRINT REPLICATE('-',200)
 PRINT 'RESTORE DATABASE ' + @Database + CHAR(10) 
 + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10) 
 + ' WITH FILE = ' + @FullPosition + ', REPLACE' + @SQLMove
END
IF EXISTS(SELECT 1 FROM msdb.dbo.backupset 
 WHERE database_name = @Database AND type='L' AND backup_set_id > @BackupSetID)
BEGIN
 PRINT REPLICATE('-',200)
 PRINT '-----------//Full backup and log backup restore '
 PRINT REPLICATE('-',200)
 PRINT 'RESTORE DATABASE ' + @Database + CHAR(10)
 + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10)
 + ' WITH FILE = ' + @FullPosition + ', NORECOVERY, REPLACE ' + @SQLMove
 DECLARE log_cursor CURSOR FOR 
 SELECT media_set_id
 ,position 
 FROM msdb.dbo.backupset 
 WHERE database_name = @Database 
 AND type='L' 
 AND backup_set_id > @BackupSetID 
 ORDER BY backup_set_id
 OPEN log_cursor
 FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SELECT @LogPath = physical_device_name 
 FROM msdb.dbo.backupmediafamily 
 WHERE media_set_id = @LogMediaSetID
 PRINT 'RESTORE Log ' + @Database + CHAR(10) 
 + ' FROM DISK = ''' + @LogPath
 + ''' WITH FILE = ' + @LogPosition + ', NORECOVERY'
 FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition
 END
 CLOSE log_cursor
 DEALLOCATE log_cursor
 PRINT 'RESTORE DATABASE ' + @Database + ' WITH RECOVERY '
END
IF EXISTS(SELECT 1 FROM msdb.dbo.backupset 
 WHERE database_name = @Database AND type='I' AND backup_set_id > @BackupSetID)
BEGIN
 PRINT REPLICATE('-',200)
 PRINT '-----------//Full backup and differential backup restore '
 PRINT REPLICATE('-',200)
 PRINT 'RESTORE DATABASE ' + @Database + CHAR(10) 
 + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10)
 + ' WITH FILE = ' + @FullPosition + ', NORECOVERY, REPLACE ' + @SQLMove
 DECLARE diff_cursor CURSOR FOR 
 SELECT media_set_id
 ,position 
 FROM msdb.dbo.backupset 
 WHERE database_name = @Database 
 AND type='I' 
 AND backup_set_id > @BackupSetID 
 ORDER BY backup_set_id
 OPEN diff_cursor
 FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SELECT @DiffPath = physical_device_name 
 FROM msdb.dbo.backupmediafamily 
 WHERE media_set_id = @DiffMediaSetID
 PRINT 'RESTORE DATABASE ' + @Database + CHAR(10)
 + ' FROM DISK = ''' + @DiffPath 
 + ''' WITH FILE = ' + @DiffPosition + ', NORECOVERY'
 FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition
 END
 CLOSE diff_cursor
 DEALLOCATE diff_cursor
 PRINT 'RESTORE DATABASE ' + @Database + ' WITH RECOVERY '
END
end 
GO

This is how you run the procedure for a single database:

use master
go
exec sp_genRestoreScripts
 @Database ='CAAltosextracts',
 @MovePathLog ='F:\logs\',
 @MovePathData ='e:\DATA\'

and for multi-databases I use the sp_foreachdb stored procedure

 declare @db_list NVARCHAR(MAX)
 SELECT @db_list = STUFF((
 SELECT ', ' + name FROM sys.databases
 WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB')
 FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
 --exec sp_foreachdb @database_list = @db_list
 -- ,@command='use ?; print db_name() + char(13)' 
 exec sp_foreachdb @database_list = @db_list
 ,@command='
 exec sp_genRestoreScripts
 @Database =[?],
 @MovePathLog =''F:\logs\'',
 @MovePathData =''E:\DATA\'' 
 ' 

• Feivel: It is throwing the below error I use your multi database script Msg 103, Level 15, State 4, Line 3 The identifier that starts with '[ABC], @MovePathLog ='F:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQ' is too long. Maximum length is 128.
• Marcello Miorelli: @Database sysname that is 128 chars. do you want database names greater than 128? No thank you.

Answer 3

I am migrating SQL Server DBs to a new instance.

You can use dbatools - Copy-DbaDatabase to do backup/restore from one source to multiple destinations.

Also, for restore dbatools - Restore-DbaDatabase - allows you to just point to a backup location or Trust your msdb backup history to build commands and allow you to do a point in time restore as well. It automatically figures out full, diff and tlogs.

You can even generate your restore scripts using Export-DbaScript to a file.

Note: I have used both dbatools and sp_restoreGene - both are really good. The plus point of dbatools is that you can use other cmdlets for your entire server management.

Answer 4

create TABLE #database(id INT identity ,
name sysname,
logicalfileName Varchar(50),
DataFileName Varchar(50),
logicalfilePath Varchar(150),
DatafilePath Varchar(150)
)
 
go
 
set nocount on
 
declare @dbname sysname, @LogicalDataFile sysname , @LogicalLogFile sysname, @PhysicalDataFile nvarchar(260) , @PhysicalLogFile nvarchar(260)
 
insert into #database(name)
 
select name
 
from sys.databases
 
where name not in ('master','msdb','model', 'tempdb','SF_SQL_Admin')
order by name
 
declare @id INT, @CNT INT, @alenzi NVARCHAR(max), @currentDb sysname;
 
select @id = 1, @CNT = max(id)
from #database
 
while @id <= @CNT
 
BEGIN
 
select @dbname=name from #database where id=@id
 
-- Data file
select @LogicalDataFile = name
, @PhysicalDataFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'ROWS'
 
-- Log file
select @LogicalLogFile = name
, @PhysicalLogFile = physical_name
from sys.master_files
where database_id = db_id(@DBName)
and type_desc = 'LOG'
 
update #database
set DataFileName= @LogicalDataFile
,logicalfileName= @LogicalLogFile
, Datafilepath =@PhysicalDataFile
, logicalfilePath = @PhysicalLogFile
where id=@id
set @id = @id + 1;
 
END
 
--Change the destination path in below syntax as per your envrionment
select 'RESTORE DATABASE ['+Name+'] FROM DISK = ''G:\MSSQL\BACKUP\G_MP04\BACKUP\'+name+'.bak'' WITH FILE = 1, MOVE N'''+logicalfilename+''' TO ''G:\MSSQL\LOGS\G_MP02\LOGS\'+name+'.ldf'' ,MOVE N'''+DataFileName+''' TO ''G:\MSSQL\DATA\G_MP01\DATA\'+name+'.mdf'''
from #database
 
drop table #database
go

• John K. N.: Welcome to the DBA.SE community. Could you possibly elaborate on your script? What does it do? Are there any requirements? Please click the edit button to add some explanation and/or details. Thanks.
• Yano_of_Queenscastle: This seems to rely on the database ids being ascending, with no gaps - but that isn't always the case, or is it?