Restore Database Procedure From Multiple Backup Location



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
 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:

Post a Comment