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