automate creation of staging database

any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:

> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the mo
st recent database
backup from the database. Save it in a variable, and use that to construct y
our RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.