Restore of database

,

This script takes path and dbname, prints the command (default 0, 1 executes the command). It loops through to get number of files of the actual database. Then it builds the restore script based on number of files. It has some limitations. It can only be one backup of a database in the directory. Since it doesn’t check for the date on the files. But it can be several files of the backup.

How to use
Use your_config_db
go
EXEC Restore_Database
@backupPath = N'Put_your_backup_path_her',
@DBName = N'Put_your_dbname_her',
@Print_or_Run 
Default in @Print_or_Run is to print the command. If you put in 1 then it runs the restore command
USE your_config_db
GO

CREATE TABLE [dbo].[TempFile](
	[backupFile] [nvarchar](255) NULL,
	[depth] [smallint] NULL,
	[file] [smallint] NULL
) ON [PRIMARY]

GO

USE your_config_db
GO

Create PROC Restore_Database @backupPath nvarchar(500), @DBName sysname, @Print_or_Run smallint = 0

AS

/*

Script made by: Christian Møller
Email: Christian@eintveit.com
Date Created: 15.08.2016

*/

Truncate table TempFile

DECLARE @cmd nvarchar(1000)
DECLARE @Fil varchar(255)
DECLARE @Restore nvarchar(2000)
DECLARE @FileID smallint
DECLARE @Mdfpath varchar(500)
DECLARE @Ldfpath varchar(500)
DECLARE @mdfname varchar(150)
DECLARE @ldfname varchar(150)


-- 2 - Initialize variables 
SET @dbName = @DBName
SET @backupPath = @backupPath

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT into TempFile
EXEC master.sys.xp_dirtree @backupPath, 0, 1

-- 4 - check for serveral backup files
DECLARE backupFiles CURSOR FOR  
   SELECT backupfile, [file]  
   FROM TempFile


Declare @i int, @count int   -- variables for counting and looping files
Set @i = 0
Set @count = 0
Select @count = COUNT(1)+1 from TempFile

OPEN backupFiles  -- Cursor

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @Fil, @fileid  

WHILE @@FETCH_STATUS = 0  
BEGIN 
		
	If @i = 0 and @i < @count
	   Begin
			--Print '@i lik null, først loop'
			Set @Restore = 'RESTORE DATABASE ' + @dbName + ' FROM DISK =' + 'N' + '''' +  @backupPath + @Fil + '' +  '''' + ',' 
			Set @i = @i + 1
		 END
			Else
				IF @i >= 1 and @i < @count
				Begin
				-- IF @i >1 and @i < @Count
					Set @Restore =  @Restore + ' DISK =' + 'N' + '''' + @backupPath + @Fil + '' +  '''' + ',' 
					Set @count = @count + 1 -- Increase count until finished
					
				END-- Else
	
   FETCH NEXT FROM backupFiles INTO @Fil , @fileid
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  


SET @Restore = LEFT(@Restore, LEN(@Restore) - 1)

select  @Mdfpath =  physical_name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 0
select  @mdfname=  mf.name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 0
Select @Ldfpath = physical_name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 1
select  @ldfname=  mf.name from sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id and db.name = @dbName and mf.type = 1
Set @Restore = @Restore  + ' WITH  FILE = 1, ' + 'Move ' + 'N' + '''' + @mdfname + '''' + ' To ' + 'N' + '''' + @Mdfpath + '''' + ', '
Set @Restore = @Restore + 'Move ' + 'N' + '''' + @ldfname + '''' + 'TO ' + 'N' + '''' + @Ldfpath + '''' + ', NOUNLOAD,  REPLACE,  MAXTRANSFERSIZE = 4194304,
BUFFERCOUNT = 250'
Set @Restore = 'Use Master ALTER DATABASE ' + @dbName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + @Restore
Set @Restore = @Restore + ' ALTER DATABASE ' + @dbName + ' SET MULTI_USER'
 
If @Print_or_Run = 0 -- Default value
 
     PRINT @Restore -- Print of command
Else
 EXECUTE sp_executesql @Restore

Rate

2.5 (2)

Share

Share

Rate

2.5 (2)