Restore SQL 2005 db and sys dbs from prod server to test

  • Hello,

    I am trying to refresh test server, which has old data.

    I have a backup of user and sys dbs on prod server. I copied those files on E: of test server. All the dbs names and sys dbs are same. Now when I am trying to restore user db on test server from its E: (where I copied backups from prod server) It is gives me following errors:

    1. Exclusive access could not be obtained.

    2. Couldn't restore as db is in use.

    Please advice. Thanks

  • That means you're trying to overwrite an existing database, while it's in use.

    You'll need to take it offline, or set it to single-user and use that credential to run the restore.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • well it is a test server and not in use. So didn't take it offline nor put it in single user mode.

    My Q is that the weekly backup used for restoring is of prod server. weekly backup from Sunday. Is it in use? I didn't think so, Am I rt? Please advice. It is the backup and not the actual prod db

  • tracmonali (7/28/2011)


    well it is a test server and not in use. So didn't take it offline nor put it in single user mode.

    My Q is that the weekly backup used for restoring is of prod server. weekly backup from Sunday. Is it in use? I didn't think so, Am I rt? Please advice. It is the backup and not the actual prod db

    "in use" meaning there is a connection open to the live database on the test server, and you cannot overwrite a database that it "in use".

    Put the database on the test server into single_user mode with rollback immediate to boot everyone out, then run your restore command with replace.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Use Master

    Alter Database [insert db name here]

    SET SINGLE_USER With ROLLBACK IMMEDIATE

    RESTORE DATABASE [insert db name here] FROM DISK = 'E:\backup\insert db backup name here.bak'

    WITH REPLACE

    GO

    Tweak as needed.

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

  • Thank you very much for replies. It helped.

    My question is after running the queries, how do I bring back to multi user mode? Does it stay in Single user mode?

  • Nope, you can flip the mode back and forth:

    ALTER DATABASE [insert db name here] SET MULTI_USER ;

    More info...see example J:

    ALTER DATABASE

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks for all the replies. My issue is resolved. It helped

  • You're welcome, HTH 🙂

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply