Results 1 to 3 of 3

Thread: Refresh the MS SQL Server 2000 Server database every hour from Production DB

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Refresh the MS SQL Server 2000 Server database every hour from Production DB

    I am currently refreshing the Reporting Server Database from Production database(size 200mb) every night by using backup and restore t-sql as follows:

    Restore Database ProjectDB from disk = 'D:\MSSQL\BACKUP\RptBak\ProjectDB.bak'
    with move 'ProjectDB_log' to 'd:\Mssql\Data\ProjectDB.ldf',
    move 'ProjectDB_data' to 'd:\Mssql\Data\ProjectDB.mdf'

    I usually take Transaction Log Backup from Production every hour everyday.

    I am planning to apply the transaction log every hour on Distination Server Database.

    I am confused whether to use WITH RECOVERY OR WITH NORECOVER while restoring a database and log.

    I am getting errors(Msg 4306) when applying the log.

    Can anyone help me as to any changes to the above script for restoring full database(every night) and how to restore applying incremental transaction log to the restored database(step by step)?

    Appreciat your help.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    For all restores except the last transaction log restore you have to use WITH NORECOVERY and use WITH RECOVERY in the last one

  3. #3
    Join Date
    Jun 2006
    Posts
    1

    Use STANDBY instead

    If you restore your database using STANDBY you'll be able to apply log files using the RESTORE LOG command.

    If you restore your database using NORECOVERY, your database will be in a "Recovery" mode and you won't be able to connect to it until you do a RESTORE LOG RECOVERY.

    If restore your database using RECOVERY, you won't be able to apply any log files. This is used if you want to restore a database and not apply any log files.

    See this link for more details:

    http://searchsqlserver.techtarget.co...324&bucket=ETA
    Last edited by kschlegelmilch; 06-01-2006 at 03:46 PM.

Posting Permissions

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