Restore Database(s) from backupfiles

The script restore the database backup (single or multiple) from the backup-file(s) stored at a location.
The script only take backupfile path as a parameter , the database are restored with the same name as stored in the backupfiles

EXEC [Usp_dba_RestoreDatabase] @BackupFilePath='drive\foldername'

The database files will be restored at the same location as in the backup file , so the source and destination file location has to be the same.

USE DatabaseName
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Usp_dba_RestoreDatabase] (@BackupFilePath varchar(1000))
AS
BEGIN
 
SET NOCOUNT ON
DECLARE @command	      nvarchar(500)
DECLARE @DBName               varchar(255)
DECLARE @vBackupFileName      varchar(255) 
DECLARE @vNewPath             varchar(1000) 
DECLARE @vFileName            varchar(100) 
--DECLARE @BackupFilePath       varchar(1000) 
DECLARE @SQL                  varchar(max)
DECLARE @dated                varchar(50) 
DECLARE @vCount               int 
DECLARE @vNumDBs              int 
 
DECLARE @LogDFileName         varchar(max)
DECLARE @LogDFilePath         nvarchar(max)
 
DECLARE @LogLFileName         varchar(max)
DECLARE @LogLFilePath         nvarchar(max)
 
DECLARE @vErrorString         varchar(1000) 
--***********--
--Error log variables
DECLARE @cmd varchar(500), @var varchar(500),@ServerName varchar(50),
            @ErrorMessage varchar(100),@filename varchar(100)
---------------
 
DECLARE @vDBDaFileList Table (DBDaFile Varchar(256) ,DataFilePath varchar(max)) 
DECLARE @vDBDLList Table (DBLgFile Varchar(256) , LogFilePath varchar(max)) 
DECLARE @vDBList Table (DBID INT NOT NULL IDENTITY(1, 1),DBakName Varchar(256))
CREATE TABLE #vDBHList
(
DBID INT NOT NULL       IDENTITY(1, 1),	
BackupName              nvarchar(128),		
BackupDescription       nvarchar(255),
BackupType              smallint,		
ExpirationDate          datetime,			
Compressed              tinyint,
Position                smallint,		
DeviceType              tinyint,			
UserName                nvarchar(128),
ServerName              nvarchar(128),	
DatabaseName            nvarchar(128),		
DatabaseVersion         int,
DatabaseCreationDate    datetime,		
BackupSize              numeric(20,0),		
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),	
CheckpointLSN           numeric(25,0),		
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,		
BackupFinishDate        datetime,			
SortOrder               smallint,
CodePage                smallint,		
UnicodeLocaleId         int,				
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,		
SoftwareVendorId        int,				
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,			
SoftwareVersionBuild    int,				
MachineName             nvarchar(128),
Flags                   int,			
BindingID               uniqueidentifier,	
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),	
FamilyGUID              uniqueidentifier,	
HasBulkLoggedData       bit,
IsSnapshot              bit,			
IsReadOnly              bit,				
IsSingleUser            bit,
HasBackupChecksums      bit,			
IsDamaged               bit,				
BeginsLogChain          bit,
HasIncompleteMetaData   bit,			
IsForceOffline          bit,				
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0) NULL,
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0) NULL,
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier NULL
)


Declare @version varchar(2)
  select @version = substring(convert(nvarchar(100),serverproperty('ProductVersion')),0, charindex('.',convert(nvarchar(100),serverproperty('ProductVersion'))))
  --print @version
  if (convert (int,@version ) <12) 
   begin 
      ALTER TABLE #vDBHList ADD CompressedBackupSize int 
      ALTER TABLE #vDBHList ADD Containment int 
   end 
  else if (convert (int,@version ) >=12)
    begin
     ALTER TABLE #vDBHList  ADD CompressedBackupSize int
     ALTER TABLE  #vDBHList  ADD Containment int	 
     ALTER TABLE  #vDBHList  ADD KeyAlgorithm nvarchar(32)	 
     ALTER TABLE  #vDBHList  ADD EncryptorThumbprint varbinary(20)	 
     ALTER TABLE  #vDBHList  ADD EncryptorType nvarchar(32)	 
    end
 
-----Insert backupfile name from the backup file location-----
SET @command='dir '+@BackupFilePath+'\*.bak /B /O'
Insert Into @vDBList 
exec xp_cmdshell @command
--------------------------------------------------------------
delete from @vDBList where DBakName IS NULL

 
SELECT  @vNumDBs = count(*) from @vDBList
Set @vCount = 1 
 
While @vCount <= @vNumDBs 
 Begin 
      DELETE FROM @vDBList where DBakName is NULL
      SELECT @vBackupFileName = DBakName From @vDBList Where DBID = @vCount  and DBakName is not NULL
      SET @BackupFilePath=''''+@BackupFilePath+'\'+@vBackupFileName+''''
 
      SET @SQL='RESTORE HEADERONLY FROM DISK= '+ @BackupFilePath
      --Print @SQL
      --SELECT * FROM sys.master_files
      INSERT INTO #vDBHList
      exec (@SQL)
      DELETE FROM #vDBHList WHERE DatabaseName is NULL or DatabaseName=''
      DELETE FROM #vDBHList WHERE DatabaseName in ('master','model','msdb')
      DELETE FROM #vDBHList WHERE DatabaseName not in (Select name from sys.databases)
     
      --SELECT DBID,databasename FROM #vDBHList --where databasename is not null or databasename=''
      SET @DBName=''
      SELECT @DBName=databasename from #vDBHList where DBID=@vCount ---and databasename is not null
       IF    @DBName <>''
            BEGIN
             SET @SQL='SELECT name,physical_name FROM sys.master_files where 
                       database_id=db_id('''+@DBName+''') and type_desc=''ROWS'''
             INSERT into @vDBDaFileList exec (@SQL)
                  select @LogDFileName=DBDaFile ,@LogDFilePath=DataFilePath from @vDBDaFileList
                                   
                  SET @SQL='SELECT name,physical_name FROM sys.master_files 
                            where database_id=db_id('''+@DBName+''') and type_desc=''LOG'''
                INSERT into @vDBDLList exec (@SQL)
                  select @LogLFileName=DBLgFile , @LogLFilePath=LogFilePath from @vDBDLList
           
      SET @SQL='RESTORE DATABASE ['+@DBName +
            '] FROM  DISK ='+ @BackupFilePath+
            ' WITH  FILE = 1,  '+
      'MOVE '+''''+@LogDFileName+''''+ 'TO '+ '''' + @LogDFilePath +''''+',  '+
      'MOVE '+''''+@LogLFileName+''''+ 'TO '+ '''' + @LogLFilePath +''''+',  '+
      'NOUNLOAD,  REPLACE,  STATS = 10'
      Print '----------Restore started for Database['+@DBName+']---------------'
      --Print  @SQL
      exec (@SQL)
      Print '----------Restore Completed for Database['+@DBName+']--------------'
      Print '                                               '
      Print'-----------SET DATABASE ['+@DBName+'] RECOVERY MODEL TO SIMPLE---------------'
      SET @SQL='ALTER DATABASE '+@DBNAME+ ' SET RECOVERY SIMPLE '
      exec (@SQL)
     
      SET @SQL='USE ['+ @DBName +'];' +' DBCC SHRINKFILE(2,1) WITH NO_INFOMSGS'   
    Print @SQL   
    Print '----------DATABASE ['+@DBName+'] RECOVERY MODEL IS SET TO SIMPLE--------------'
      Print '     '
      END
 
      SET @vCount = @vCount + 1    
    END
 DROP TABLE #vDBHList
SET NOCOUNT OFF
END

Download Script :DatabaseRestore.sql

Leave a Reply

Your email address will not be published. Required fields are marked *