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