BackuUp problem - SQL Server Forums

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)

BackuUp problem

Author

Topic

acko
Yak Posting Veteran

52 Posts

Posted - 2004-07-01 : 04:13:35

Hi everyone
I have this situation with backup and restore.
I have two instances of sql, sql1 and sql2.
Production server is sql1 and test server is sql2.

I created database maintenance plan that makes full backup every night on tape.

I would like to make some kind of job or to add steps to job that was created with DMplan which will make restore of backuped database to sql2 server.

How can i do this? How can i read which is the last file backuped on tape?

Thanks very much
Alex

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 04:43:43

Hi Acko,

If you write your own backup jobs, you'll have more control. Having said that, I doubt you'd be able to kick off a restore on another instance.

regards a way of doing this:
MSDB query

select bs.backup_start_date,bf.physical_device_name,bf.logical_device_name
from 	msdb..backupset bs inner join
	msdb..backupmediafamily bf
		on bs.media_set_id = bf.media_set_id
where bs.database_name = 'Redbaron'
and bs.type = 'D'
and bs.backup_start_date = (select max(backup_start_date) from msdb..backupset where database_name = 'REdbaron' and type = 'D')

From there you can build your restore..

a comment:Test and prod instances on same box? Is there no way you can get another box to run your test instance?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources