We had a requirement where we have to restore multiple databases from different locations of backup. I have already done restore multiple DB in one shot but there backup location and backup naming convention was unique. So for restoring backup from multiple locations and considering backups with different naming convention we need separate procedure.
So syntax will be
EXEC Restore DB ‘DBNAME1’ ,’backuplocation\backup1’
EXEC Restore DB ‘DBNAME2’ ,’backuplocation\backup2’
/***************************************************/
Create PROCEDURE RESTORE_DB (@SourcePath varchar (5000) ,@name varchar (8000))
as BEGIN
/*********Explanation*************
Created By Saurabh Sinha
Date 30/09/2015
Detail:To restore single database form specific db location where there is only 1 backup. Once restore done backup will be deleted
@SourcePath = from where backups to be picked
----> if path is T:\Adhoc\05PPBACKUP
then pass "T:\Adhoc\05PPBACKUP\"; dont forget "\"
******************************/
Create table #fileListTable
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32) -- remove this column if using SQL 2005
)
DECLARE @path varchar(1000)
DECLARE @SourceFile AS VARCHAR(5000);
Declare @Backupname as varchar (5000);
DECLARE @Cmd AS VARCHAR(5000);
Declare @backuplocation varchar (5000)
Declare @Lfile1 varchar (200) , @Lfile2 varchar (200)
Declare @Pfile1 varchar (2000) , @Pfile2 varchar (2000)
SET @SourceFile = @SourcePath
Set @backuplocation = @SourcePath
SET @Path = @SourceFile
CREATE Table #MyTable (Results varchar(500))
CREATE Table #MYFILES (is_directory int ,[Name] varchar (500))
CREATE Table #MYFILES_main (id int identity , name varchar (500))
SET @cmd = 'dir ' + @path + ' /A /OS /-C'
INSERT INTO #MyTAble
EXEC XP_CMDSHELL @cmd
---select * from #MyTAble
INSERT INTO #MYFILES --(is_directory int ,[Name] varchar (500))
SELECT CASE
LTRIM(RTRIM(SUBSTRING(Results, 21, 18)))
WHEN ''THEN 1
WHEN '<DIR> ..'THEN 1
WHEN '<DIR> .'THEN 1
ELSE 0 END as is_directory,
SUBSTRING(Results, 36, Len(Results)) AS [name]FROM #MyTable
WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0
--select * from #MYFILES
DELETE FROM #MYFILES
where name in ('..','.') or is_directory =1
--select * from #MYFILES
insert into #MYFILES_main select top 1 name from #MYFILES
select * from #MYFILES_main
---------------------------------------------
Select @Backupname = name from #MYFILES_main
Set @backuplocation = @backuplocation + rtrim(ltrim(@Backupname))
Declare @RestoreStatement varchar (8000)
SET @RestoreStatement = N'RESTORE FILELISTONLY FROM DISK=N''' + @backuplocation + ''''
/*********Extracting logical name of db files from backup ***********/
insert into #fileListTable
EXEC(@RestoreStatement)
Select @Lfile1 = LogicalName from #fileListTable where fileid =1
Select @Lfile2 = LogicalName from #fileListTable where fileid =2
Select @Pfile1 = filename from master..sysaltfiles where dbid =db_id(@name ) and fileid =1
select @Pfile2 = filename from master..sysaltfiles where dbid =db_id(@name ) and fileid = 2;
/*****Changing recovery model to simple ***********/
Declare @Recovery_change varchar (8000)
SET @Recovery_change = N'Alter database [' + @name +'] SET Recovery simple'
select @Recovery_change
EXEC(@Recovery_change);
/******Kill all db connections ***************/
DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@name)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@name) AND spid > @spid
END
/**********Restore Database **********/
Select @name , @backuplocation ,@lfile1 ,@lfile2 ,@pfile1 ,@pfile2
Restore database @name from disk = @backuplocation with stats =1, replace ,stats =1,
move @Lfile1 to @Pfile1,
move @Lfile2 to @Pfile2
Declare @delete varchar (8000)
SET @delete = 'xp_cmdshell ' + '''del '+@backuplocation +''''
Select @delete
Exec (@delete)
Drop Table #MYFILES
Drop table #MYFILES_main
DROP Table #MyTAble
DROP TABLE #fileListTable
END
/***************************************************/
No comments:
Write commentsPlease do not enter spam links