Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    2

    Unanswered: restore backup from one database to another database

    Here's the situation : I have a production database - PRODDB
    I have a report and a test database - REPTDB and TESTDB respectively
    After the backup maintenance plan for PRODDB runs - I want to take the backup and apply it to the test and report databases. I use the following script.

    ***start of script:***

    declare @openfile as char(84)
    -- pre-initialize production backup file
    set @openfile = 'd:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_yyyymmddhhmm. bak'

    -- OK here it is in a nutshell
    -- the object is to replace the yyyymmddhhmm string in the openfile variable with
    -- the correct info so that restore will use the correct backup file for the run date.
    --
    -- The select will return the correct message field which contains the file path for
    -- the last run production database backup - it's contends are :
    -- 'Backup Destination: [d:\program files\microsoft sql server\mssql\backup\proddb\proddb_db_200208150202. bk]'
    --
    -- Once you have above you have to replace the yyymmddhhmm with the 200208150202 (this changes daily)
    --
    -- Then you can do the restores once you build the openfile variable
    --
    use msdb
    set @openfile = replace(@openfile, 'yyyymmddhhmm',right((select message
    from sysdbmaintplan_history
    where convert(char(11),start_time) = convert(char(11), getdate())
    and cast(message as char(18)) = 'Backup Destination') ,17))

    -- restore test database with production backup
    restore database testdb
    from disk=@openfile
    with norecovery

    -- Restore report database with production backup
    restore database reptdb
    from disk=@openfile
    with norecovery

    *** End script***

    ****the messages/logs I get is this:****
    Processed 236888 pages for database 'testdb', file 'Base' on file 1.
    Processed 2 pages for database 'testdb', file 'Base_log' on file 1.
    RESTORE DATABASE successfully processed 236890 pages in 270.579 seconds (7.172 MB/sec).
    Processed 236888 pages for database 'reptdb', file 'Base' on file 1.
    Processed 2 pages for database 'reptdb', file 'Base_log' on file 1.
    RESTORE DATABASE successfully processed 236890 pages in 276.522 seconds (7.017 MB/sec).
    **** end message****

    The problem I have is that the REPTDB and TESTDB are left in a (loading...) state when you look in Enterprise Manager. It say's that they are in a restore process.

    Can anyone tell me what's going on and how to correct this?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    restore database reptdb
    from disk=@openfile
    with norecovery

    BOL:
    When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.

    It has to be this way:

    restore database reptdb
    from disk=@openfile
    with recovery

  3. #3
    Join Date
    Aug 2002
    Posts
    2
    Originally posted by snail
    restore database reptdb
    from disk=@openfile
    with norecovery

    BOL:
    When specifying the NORECOVERY option, the database is not usable in this intermediate, nonrecovered state.

    It has to be this way:

    restore database reptdb
    from disk=@openfile
    with recovery


    Snail it worked thanks again.

Posting Permissions

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