Restore All Backups

--SCRIPT TO RESTORE BACKUP FOR ALL DATABASES, PLACED IN FOLDER CALLED
--ORIGIN_FOLDER, WITH THE INITIALS 'DBVF' AND EXTENSION '.BAK' 

SET NOCOUNT ON
---------------------------------------------------------------------
DECLARE @RESTORE_TYPE INT
--VARIÁVEL UTILIZADA PARA INDICAR SE O RESTORE POSSUI OU NÃO
--THIS VAR INDICATE THE EXIST ADITIONAL INCREMENTAL OR TRANSACTION LOGS
--IF =0 FULL BACKUP IS RESTORED
--IF =1 TRANSACTIONAL AND INCREMENTAL ADITIONAL BACKUPS ARE ALOWED
--BACKUPS INCREMENTAIS E TRANSACTION LOG A SER APLICADOS
--SE O VALOR DA VARIÁVEL @RESTORE_TYPE FOR IGUAL A 1, 
--É EFETUADO O RESTORE
--DO ULTIMO BACKUP FULL E NÃO SERÁ POSSÍVEL COLOCAR INCREMENTAIS OU 
--TRANSACION LOGS ADICIONAIS. COM O VALOR PARA @RESTORE_TYPE=0 A BASE 
--FICA ABERTA AOS RESTORES ADICIONAIS PORÉM INATIVA ATÉ A FINALIZAÇÃO
--DO RESTORE DE TODOS OS INCREMENTAIS OU LOGS
SET @RESTORE_TYPE = 0
-----------------------------------------------------------------------
DECLARE @START_FOLDER VARCHAR(130),
        @END_FOLDER VARCHAR(130),
        @STOPAT SMALLDATETIME
SET @END_FOLDER = 'E:\MSSQL\Data\'
SET @START_FOLDER = 'E:\Backups\' 
--THE VAR @STOPAT IS USED FOR STOP RESTORE AT SPECIFIED DATETIME
IF @RESTORE_TYPE=1
SET @STOPAT = ''
--'2010-02-18 18:30:00'

DECLARE @DEBUG INT,@CHECK INT
--ADDITIONAL VARS FOR DEBUG PRINT MESSAGES AND CHECK NUMBER OF FILES
--@DEBUG=0 - NO DEBUG
--@DEBUG=1 - DEBUG
SET @DEBUG=0

---------------------------------------------------------------------

DECLARE @DBID INT,
 @DATABASE VARCHAR(30),
 @COMANDO NVARCHAR(300),
 @COMANDO2 NVARCHAR(300),
 @SQL NVARCHAR(2000)

DECLARE @DATAFILE VARCHAR(60),
        @DATAFILE2 VARCHAR(60), --IF YOU HAVE MORE THEN 2 DATA FILES IN YOU DATABASES, PLEASE CREATE ADDITIONAL VARS
        @DATALOG VARCHAR(30) 


--PRINT @COMANDODOS
IF EXISTS ( SELECT *
 FROM TempDb.dbo.SysObjects
 WHERE NAME = '##db_restore' ) 
 DROP TABLE ##db_restore
IF EXISTS ( SELECT *
 FROM TempDb.dbo.SysObjects
 WHERE NAME = '##teste' ) 
 DROP TABLE ##teste
IF EXISTS ( SELECT *
 FROM TempDb.dbo.SysObjects
 WHERE NAME = '##HISTORY_BACKUP ' ) 
 DROP TABLE ##HISTORY_BACKUP 

DECLARE @DEVICE VARCHAR(100),
 @COMANDODOS VARCHAR(200)
SET @COMANDODOS = 'DIR /B ' + @START_FOLDER + '*.bak '
--FOLDER IS LOCATED IN YOU SQL SERVER, OFF COURSE
CREATE TABLE ##teste ( texto NVARCHAR(255) )
INSERT INTO ##teste
 EXEC MASTER..XP_CMDSHELL @COMANDODOS
PRINT @COMANDODOS

CREATE TABLE ##db_restore
 (
 DBID INT IDENTITY,
 BASE NVARCHAR(60),
 DEVICE NVARCHAR(100),
 COMANDO NVARCHAR(200),
 COMANDO2 NVARCHAR(200)
 )

DECLARE DEVICE CURSOR
 FOR SELECT texto
 FROM ##teste
 WHERE texto LIKE '%bdvf%'

OPEN DEVICE
FETCH NEXT FROM DEVICE INTO @DEVICE
WHILE @@FETCH_STATUS = 0
 BEGIN
 INSERT INTO ##db_restore
 (
 BASE,
 DEVICE,
 COMANDO,
 COMANDO2
     )
 VALUES (
 UPPER(REPLACE(REPLACE(@DEVICE, 'BDVF_', ''), '.bak', '')),
 @DEVICE,
 '''RESTORE FILELISTONLY FROM DISK =''''' + @START_FOLDER
 + @DEVICE + '''''''',
 '''RESTORE HEADERONLY FROM DISK =''''' + @START_FOLDER
 + @DEVICE + ''''''''
    
     )
 FETCH NEXT FROM DEVICE INTO @DEVICE
 END
CLOSE DEVICE
DEALLOCATE DEVICE



IF EXISTS ( SELECT *
 FROM TempDb.dbo.SysObjects
 WHERE NAME = '##HISTORY_BACKUP' ) 
 DROP TABLE ##HISTORY_BACKUP
EXEC SP_EXECUTESQL @SQL
--
---------------------------------------------------------------
CREATE TABLE ##HISTORY_BACKUP
 (
 BackupName NVARCHAR(300),
 BackupDescription NVARCHAR(2000),
 BackupType INT,
 ExpirationDate SMALLDATETIME,
 Compressed BIT,
 Position INT,
 DeviceType INT,
 UserName NVARCHAR(100),
 ServerName NVARCHAR(100),
 DatabaseName NVARCHAR(100),
 DatabaseVersion NVARCHAR(100),
 DatabaseCreationDate SMALLDATETIME,
 BackupSize NVARCHAR(30),
 FirstLsn NVARCHAR(100),
 LastLsn NVARCHAR(100),
 CheckpointLsn NVARCHAR(100),
 DifferentialBaseLsn NVARCHAR(100),
 BackupStartDate SMALLDATETIME,
 BackupFinishDate SMALLDATETIME,
 SortOrder INT,
 CodePage INT,
 UnicodeLocaleId INT,
 UnicodeComparisonStyle INT,
 CompatibilityLevel INT,
 SoftwareVendorId INT,
 SoftwareVersionMajor NVARCHAR(100),
 SoftwareVersionMinor NVARCHAR(100),
 SoftwareVersionBuild NVARCHAR(100),
 MachineName NVARCHAR(100),
 Flags BIT,
 BindingId NVARCHAR(100),
 RecoveryForkId NVARCHAR(100),
 Collation NVARCHAR(100)
 )
---------------------------------------------------------------


DECLARE DEVICE CURSOR
 FOR SELECT DBID,
 BASE,
 COMANDO,
 COMANDO2
 FROM ##db_restore
 ORDER BY DBID
OPEN DEVICE
FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO, @COMANDO2
WHILE @@FETCH_STATUS = 0

 BEGIN
 SET @SQL = 'IF EXISTS ( SELECT * FROM TempDb.dbo.SysObjects WHERE NAME = ''##'
 + @DATABASE + ''' ) 
        DROP TABLE ##' + @DATABASE + ''

 EXEC SP_EXECUTESQL @SQL
    
 SET @SQL = 'create table ##' + @DATABASE + ' (
                    LogicalName VARCHAR(200), PhysicalName VARCHAR(200),
                    Type VARCHAR(1),FilegroupName VARCHAR(200),
                    Size VARCHAR(200),MaxSize VARCHAR(200))'
 EXEC SP_EXECUTESQL @SQL
 SET @SQL = 'insert ##' + @DATABASE + ' exec (' + @COMANDO + ')'
 EXEC SP_EXECUTESQL @SQL
        
        IF @@ERROR <>0
        BEGIN
     SET @SQL = 'insert ##HISTORY_BACKUP exec (' + @COMANDO2 + ')'

            EXEC SP_EXECUTESQL @SQL
        END
 FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO, @COMANDO2
 END
CLOSE DEVICE
DEALLOCATE DEVICE

IF @DEBUG <>0
BEGIN
    SELECT * FROM ##HISTORY_BACKUP
END
ELSE
BEGIN


--RESTAURA
DECLARE @TOTAL_ARQUIVOS_BASE INT
DECLARE @VARLOOP INT
DECLARE @BACKUP_TYPE INT

DECLARE DEVICE CURSOR
 FOR SELECT DBID,
 BASE,
 COMANDO
 FROM ##db_restore --WHERE BASE='ARCMEDICOS' 
 ORDER BY DBID
OPEN DEVICE
FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO
WHILE @@FETCH_STATUS = 0
 BEGIN 
    
    --OBTEM OS NOMES DOS DATAFILES DAS BASES
     SET @SQL = N'SELECT @CHECK =COUNT (TYPE) FROM ##' + @DATABASE
 + ' WHERE TYPE=''D''' 
 EXEC SP_EXECUTESQL @QUERY = @SQL,
 @params = N'@CHECK INT OUTPUT',
 @CHECK = @CHECK OUTPUT 
 IF @DEBUG <>0
 --PRINT @CHECK
     IF @CHECK >1
     BEGIN
            SET @SQL = N'SELECT @DATAFILE =LOGICALNAME FROM ##' + @DATABASE
                + ' WHERE TYPE=''D'' AND FILEGROUPNAME = ''PRIMARY'''
            IF @DEBUG <>0    
            PRINT @SQL
            EXEC SP_EXECUTESQL @QUERY = @SQL,
                @params = N'@DATAFILE VARCHAR(60) OUTPUT',
                @DATAFILE = @DATAFILE OUTPUT 
            SET @SQL = N'SELECT @DATAFILE2 =LOGICALNAME FROM ##' + @DATABASE
                + ' WHERE TYPE=''D'' AND FILEGROUPNAME <> ''PRIMARY'''
            EXEC SP_EXECUTESQL @QUERY = @SQL,
                @params = N'@DATAFILE2 VARCHAR(60) OUTPUT',
                @DATAFILE2 = @DATAFILE2 OUTPUT 
                IF @DEBUG <>0
                PRINT @SQL
        END
        ELSE
        BEGIN
            SET @SQL = N'SELECT @DATAFILE =LOGICALNAME FROM ##' + @DATABASE
                + ' WHERE TYPE=''D'' AND FILEGROUPNAME = ''PRIMARY'''
            EXEC SP_EXECUTESQL @QUERY = @SQL,
                @params = N'@DATAFILE VARCHAR(30) OUTPUT',
                @DATAFILE = @DATAFILE OUTPUT 
        END                
        IF @DEBUG <>0
            PRINT @DATAFILE2
    --OBTAIN DATALOGS NAMES
 SET @SQL = 'SELECT @DATALOG =LOGICALNAME FROM ##' + @DATABASE
 + ' WHERE TYPE=''L'''
 EXEC SP_EXECUTESQL @QUERY = @SQL,
 @params = N'@DATALOG VARCHAR(30) OUTPUT',
 @DATALOG = @DATALOG OUTPUT 
 IF @DEBUG <>0
 BEGIN 
            PRINT @DATALOG 
             PRINT 'DATA FILE= ' + @DATAFILE + '---- LOG FILE= '+ @DATALOG
            PRINT @DATALOG
        END
 --CHECK EXISTING ANOTHER BACKUPS TO BE RESTORED
 SET @TOTAL_ARQUIVOS_BASE = ( SELECT COUNT(BACKUPTYPE)
 FROM ##HISTORY_BACKUP
 WHERE DATABASENAME = @DATABASE
 AND backuptype <> 1
 )
    
 IF @TOTAL_ARQUIVOS_BASE > 0    --IF EXISTING SET ADDITIONAL RESTORE ON
 SET @RESTORE_TYPE = 1
 ELSE 
 SET @RESTORE_TYPE = 0    --IF NOT EXIST, SET FULL RESTORE
            
    --START RESTORE, MOVING DATA AND LOG FILES TO DESTINATION FOLDERS
    --RENAME FILES TO FORMAT @DATABASE.MDF E @DATABASE.LDF
 IF @RESTORE_TYPE = 1 --NOT FULL
 BEGIN
                IF @DEBUG <>0
 PRINT 'NOT FULL'
 SET @SQL = 'RESTORE DATABASE ' + @DATABASE + '
                            FROM DISK = N''' + @START_FOLDER + 'BDVF_' + @DATABASE + '.BAK''
                            WITH MOVE ''' + @DATAFILE + ''' TO ''' + @END_FOLDER
                            + @DATABASE + '.mdf'', 
                            MOVE ''' + @DATALOG + ''' TO ''' + @END_FOLDER
                            + @DATABASE + '.ldf'',
                            NORECOVERY, NOUNLOAD, STATS = 10, FILE=1'
                IF @DEBUG <>0            
                BEGIN
                    PRINT @DATABASE +' - total de backups='+ CONVERT(VARCHAR,@TOTAL_ARQUIVOS_BASE)         
                    PRINT @SQL
                END

 EXEC SP_EXECUTESQL @SQL
        --loop FOR ALL FILES IN BACKUPSET
 SET @VARLOOP = 0
 WHILE @TOTAL_ARQUIVOS_BASE <> @VARLOOP - 1
 BEGIN
 SET @VARLOOP = @VARLOOP + 1
                --CHECK BACKUP TYPE IF=5 INCREMENTAL IF=2 BACKUP LOG
 SET @BACKUP_TYPE = ( SELECT BACKUPTYPE
 FROM ##HISTORY_BACKUP
 WHERE DATABASENAME = @DATABASE
 AND POSITION = @VARLOOP
 )
 IF @TOTAL_ARQUIVOS_BASE >= @VARLOOP 
 BEGIN
 IF @BACKUP_TYPE = 2 
 SET @SQL =
 'RESTORE LOG [' + @DATABASE
 + '] FROM DISK = N''' + @START_FOLDER
 + 'BDVF_' + @DATABASE
 + '.BAK'' WITH FILE = '
 + CONVERT(VARCHAR, @VARLOOP) + ', 
                                        NORECOVERY, NOUNLOAD, STATS = 10'
 IF @BACKUP_TYPE = 5 
 SET @SQL = 'RESTORE DATABASE [' + @DATABASE
 + '] FROM DISK = N''' + @START_FOLDER
 + 'BDVF_' + @DATABASE
 + '.BAK'' WITH FILE '
 + CONVERT(VARCHAR, @VARLOOP) + ', 
                                        NORECOVERY, NOUNLOAD, STATS = 10'
                IF @DEBUG <>0            
                BEGIN                                        
                    PRINT @BACKUP_TYPE
                    PRINT 'NOT CLOSE'--@SQL
                END
 EXEC SP_EXECUTESQL @SQL
 END
 ELSE 
 BEGIN
 IF @BACKUP_TYPE = 2 
 SET @SQL =--'RESTORE FILE' + '-- WITH FILE =' + CONVERT (VARCHAR,@VARLOOP)
 'RESTORE LOG [' + @DATABASE
 + '] FROM DISK = N''' + @START_FOLDER
 + 'BDVF_' + @DATABASE
 + '.BAK'' WITH FILE = '
 + CONVERT(VARCHAR, @VARLOOP) + ', 
                                        NOUNLOAD, STATS = 10'
                
 IF @BACKUP_TYPE = 5 
 SET @SQL = 'RESTORE DATABASE [' + @DATABASE
 + '] FROM DISK = N''' + @START_FOLDER
 + 'BDVF_' + @DATABASE
 + '.BAK'' WITH FILE '
 + CONVERT(VARCHAR, @VARLOOP) + ', 
                                        NOUNLOAD, STATS = 10'
 IF ISNULL(@STOPAT, '') <> '' 
 SET @SQL = @SQL + ' ,STOPAT=N'''
 + CONVERT(VARCHAR, @STOPAT) + ''''
                
                                IF @DEBUG <>0            
                                BEGIN
                                    PRINT @BACKUP_TYPE 
                                    PRINT @SQL
                                END
 EXEC SP_EXECUTESQL @SQL    
 END
            
 END
        
        
 END
 ELSE --FULL RESTORE, NO ADDITIONAL FILES
 BEGIN
                IF @DEBUG <>0    
 PRINT 'RESTORE FULL'
 SET @SQL = 'RESTORE DATABASE ' + @DATABASE + '
                    FROM DISK = N''' + @START_FOLDER + 'BDVF_' + @DATABASE + '.BAK''
                    WITH MOVE ''' + @DATAFILE + ''' TO ''' + @END_FOLDER + @DATABASE
 + '.mdf'','
 IF @CHECK > 1
                 SET @SQL=@SQL + 'MOVE ''' + @DATAFILE2 + ''' TO ''' + @END_FOLDER + @DATABASE
 + '_FILE2.mdf'',' 
 SET @SQL=@SQL +' 
                    MOVE ''' + @DATALOG + ''' TO ''' + @END_FOLDER
 + @DATABASE + '.ldf'''
                    IF @DEBUG <>0     
                    PRINT @SQL
                    EXEC SP_EXECUTESQL @SQL
        
 END
 IF @@ERROR <> 0 
 PRINT 'RESTORE ERROR - ' + CONVERT(VARCHAR,@@ERROR) + @DATABASE + ' ---> ' + @SQL
 FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO
 END
CLOSE DEVICE
DEALLOCATE DEVICE
--END RESTORE
END

--END OD PROCESS, DESTROY ALL TEMP TABLES

DECLARE DEVICE CURSOR
 FOR SELECT DBID,
 BASE,
 COMANDO
 FROM ##db_restore
 ORDER BY DBID
OPEN DEVICE
FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @SQL = 'DROP TABLE ##' + @DATABASE
 PRINT @sql    
 EXEC SP_EXECUTESQL @SQL

 FETCH NEXT FROM DEVICE INTO @DBID, @DATABASE, @COMANDO
 END
CLOSE DEVICE
DEALLOCATE DEVICE