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’

QUICK DOWNLOAD

/***************************************************/

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

/***************************************************/