Setting up a DR env. for Standard Ed.

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)

Setting up a DR env. for Standard Ed.

Author

Topic

cwalston
Starting Member

25 Posts

Posted - 2007-06-29 : 01:50:02

Hey Guys,

Probably real simple...

I am trying to setup a DR env. for a few Prod databases. I am on Standard edition.

I setup a simple maint plan to dump the prod database (no trans for simple example), and it dumps each night with a date/time stamp on the file and puts it in a network share.

Then from the DR server, I created a job to grab the file and copy it to the local backup directory.

Now I am trying to load the dump file, but the date/time stamp is throwing me off. The database name is in the filename, but I am having a problem loading the dump file because I cannot seem to use a wildcard to take care of the date/time stamp.

I have started to think of a couple ways to have the DR server know the file name, but they seem too complicated.

1. dir the filename into a file and bcp this 1 row file into a temp table and use dynamic sql to setup the restore command.
2. setup a linked server to query the msdb backup tables to identify the backup filename

But, as I start to go down those paths, I can't stop feeling that there is a probably a real simple way to do this.

So I thought I would throw it out and see if anyone had any ideas.

I really appreciate any help that you could offer!!

Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 01:58:36

use xp_cmdshell to get the filename. Hopefully your datetime is in ISO format, YYYYMMDDHHMMSS

-- Create a temp table to hold the result
CREATE TABLE #bkfile
(
	bk_filename	varchar(100)
)

-- execute cmdshell into the temp table
INSERT INTO #bkfile 
EXEC xp_cmdshell 'DIR C:\FOLDER\DBNAME.bak /b'

-- Your retrieval of filename here
SELECT * FROM #bkfile

DROP TABLE #bkfile

KH
[spoiler]Time is always against us[/spoiler]

cwalston
Starting Member

25 Posts

Posted - 2007-07-05 : 12:35:07

That works great!!!

Thanks so much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources