Restoring a DB - What do you think? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restoring a DB - What do you think?

Author

Topic

braddoro
Starting Member

7 Posts

Posted - 2002-07-30 : 18:42:52

Here is my solution to restoring a DB from a production box to a warm box. This sp will be scheduled to run everyday. Let me know what you think. I hope the formatting didn't get too munched up.

create procedure usp_Process_Restore_CommonTables
as

-----------------------------------------------------------------------------------------
-- Setup
-----------------------------------------------------------------------------------------
set nocount on
create table #BackupFile (BackupFile varchar(200))
declare @str_BackupFile varchar(100)
declare @str_SQL nvarchar(2000)
declare @str_CMD varchar(100)
declare @str_DBPath varchar(100)
declare @str_NewPath varchar(100)
declare @str_DBName varchar(100)
declare @str_Quote char(1)

set @str_Quote = char(39)
set @str_NewPath = 'd:\MSSQL\Data\'
set @str_DBPath = '\\sqlsvr2\BACKUP\CommonTables\'
set @str_DBName = 'CommonTables'
set @str_CMD = 'dir ' + @str_DBPath + @str_DBName + '*.bak /o-d /b'

-----------------------------------------------------------------------------------------
-- Get the list of database backups and put them into a table.
-----------------------------------------------------------------------------------------
INSERT INTO #BackupFile (BackupFile)
exec master..xp_cmdshell @str_CMD

-----------------------------------------------------------------------------------------
-- Get the most recent database backup and put it into a variable.
-----------------------------------------------------------------------------------------
select top 1 @str_BackupFile = rtrim(BackupFile) from #BackupFile
drop table #BackupFile

-----------------------------------------------------------------------------------------
-- Build our restore string for this database.
-----------------------------------------------------------------------------------------
set @str_SQL = 'RESTORE DATABASE ' + @str_DBName + ' FROM DISK = ' + @str_Quote +
@str_DBPath+@str_BackupFile + @str_Quote + ' WITH ' +
' MOVE ' + @str_Quote +
@str_DBName + '_Data' + @str_Quote + ' TO ' + @str_Quote + @str_NewPath +
@str_DBName + '_Data.MDF' + @str_Quote + ',' +
' MOVE ' + @str_Quote +
@str_DBName + '_Log' + @str_Quote + ' TO ' + @str_Quote + @str_NewPath +
@str_DBName + '_Data.ldf' + @str_Quote

-----------------------------------------------------------------------------------------
-- Run the restore job.
-----------------------------------------------------------------------------------------
EXECUTE sp_executesql @str_SQL

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-07-31 : 07:35:28

no authorization issues ? running linkedserver's with SA ?

Have you considered making it more parametre driven - i.e. giving it options as to target server instead of hardcoding ?

braddoro
Starting Member

7 Posts

Posted - 2002-07-31 : 08:33:42

We have 3 servers and our data is split over the 3 servers... not because we have lots of data, but due to bad design by someone before me. (these are some of the issues I am fixing) So as a result we have a trust between all 3 servers.

And yes I probably should make some of the variables parms so I can just pass them in. :)

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources