Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Automated Restore


    I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".

    Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.

    I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.

    Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.

    I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.

    I've only used restore via the enterprise manager and I was wondering if anyone here could help.

    kind regards,


  2. #2
    Join Date
    Jul 2005
    New Zealand
    You need to create a scheduled job that will execute something like this:

    restore database ManagementDBYesterday
    from disk = 'backup_location\ManagementDB.bak' with recovery,
    move 'ManagementDB_Data' to 'data_location\ManagementDBYesterday_Data.ndf',
    move 'ManagementDB_Primary' to 'data_location\ManagementDBYesterday_Primary.mdf',
    move 'ManagementDB_Log' to 'log_location\ManagementDBYesterday_Log.ldf'
    If you have different logical file names for each database, you may also want to do this for clarity:

    alter database ManagementDBYesterday
    modify file (name=ManagementDB_Primary, newname=ManagementDBYesterday_Primary)
    alter database ManagementDBYesterday
    modify file (name=ManagementDB_Data, newname=ManagementDBYesterday_Data)
    alter database ManagementDBYesterday
    modify file (name=ManagementDB_Log, newname=ManagementDBYesterday_Log)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts