Friday, April 10, 2009

[Backups] Restoring Litespeed archival backups to secondary server

After a year or so, databases on my primary server get moved to a secondary server. I wrote the below script to look in a particular for 1 backup, then it determines where to restore it based off the database name. Hopefully someone else can use this.

DECLARE @backup_directory VARCHAR(500), @Verified VARCHAR(100), @full_backup_name VARCHAR(600),
@database_name sysname, @restore_directory VARCHAR(500),
@logical_name_log VARCHAR(50), @physical_name_log VARCHAR(50),
@logical_name_data VARCHAR(50), @physical_name_data VARCHAR(50)
,@with_data VARCHAR(200), @with_log VARCHAR(200)

--put the name of the folder with the backup you want restored here--
SET @backup_directory = '\\servername\share\databasefolderbackup'

IF RIGHT(@backup_directory, 1) <> '\'
SET @backup_directory = @backup_directory + '\'

SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb.[dbo].[#listing]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
create table #Listing
(resultant nvarchar (255))

--Find the backups in the given folder
declare @dirlist varchar(500)
select @dirlist = 'exec master..xp_cmdshell ''dir /b "'+ @backup_directory + '*"'''
insert #Listing exec (@dirlist)

--get the most recent
select @Verified = MAX(resultant) from #listing WHERE resultant LIKE '%.bak'

--get a list of all logical files within the backup so we can restore with the right data
SELECT @full_backup_name = @backup_directory + @Verified

if object_id('tempdb..#database_details') is not null
DROP TABLE #database_details

CREATE TABLE #database_details
LogicalName sysname,
PhysicalName varchar(500),
FileGroupName varchar(50),
[Size] VARCHAR(50),
[MaxSize] VARCHAR(50))
INSERT INTO #database_details
EXEC MASTER..xp_restore_filelistonly @filename = @full_backup_name

--get the names of everything.
SELECT @logical_name_data = LogicalName, @physical_name_data = PhysicalName
FROM #database_details WHERE [TYPE] = 'D'
SELECT @logical_name_log = LogicalName, @physical_name_log = PhysicalName
FROM #database_details WHERE [TYPE] = 'L'

SELECT @database_name = @logical_name_data

--set up the folders where the restore will automatically go.
SELECT @physical_name_data = CASE
WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009\'
WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008\'
ELSE 'not known'
+ right(@physical_name_data, CHARINDEX('\', reverse(@physical_name_data))-1)

SELECT @physical_name_log = CASE
WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009_Logs\'
WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008_Logs\'
ELSE 'not known'
+ right(@physical_name_log, CHARINDEX('\', reverse(@physical_name_log))-1)

--need to set these separately since you can't call within the SP
SELECT @with_data = 'MOVE "' + @logical_name_data + '" TO "' + @physical_name_data + '"'
SELECT @with_log = 'MOVE "' + @logical_name_log + '" TO "' + @physical_name_log + '"'

--Now do a restore with MOVE.
exec master..xp_restore_database @database=@database_name
, @filename= @full_backup_name
, @with = @with_data
, @with = @with_log

No comments: