Best way to restore MSDB

  • I have to restore msdb from prod backup to a test box so that i can get all the jobs in QA as prod. No one is using that box and there are no users. while i am trying to restore using LiteSpeed i get a message

    VDI open failed due to requested abort.

    RESTORE DATABASE is terminating abnormally.

    Exclusive access could not be obtained because the database is in use.

    There are no users at all in that box and no one is connected?

    Can i drop MSDB and then start the restore?

    If i drop MSDB will it have any troubles?

    Please advise

  • Are the SQL Server Builds and Versions same at Prod and QA?

    Also, have you checked if the SQL Server Agent is stopped?

    I have not tried dropping MSDB, so I can't guess if it would allow you, though I can try one at home and update here.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Yeah the builds are different.

    I have done some troubleshooting and found out that Prod version is 8.00.2282

    whereas in the QA box it is 8.00.2039

    That is the reason i was having an error cannot be restored.

    Can you please let me know by installing security bulletin(MS09-004) my build will change to same as prod?

  • I have installed the Hotfix and now builds are same

    I have even restored MSDB successfully.

    I have not dropped MSDB. Not sure it allows that.

    Thanks for the quick reply

  • You are welcome !

    Though your problem was solved, I tried to test and see if MSDB can be dropped. It did not allow to drop. I was 99.9% sure it would not allow dropping MSDB, now I am 100% sure.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • You are welcome !

    Though your problem was solved, I tried to test and see if MSDB can be dropped. It did not allow to drop. I was 99.9% sure it would not allow dropping MSDB, now I am 100% sure.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Deleted - didn't realise issue was resolved. :Whistling:

  • Nice Marmot (12/10/2009)


    Hi,

    The version of SQL Server you are restoring on to has to be the same or higher than the source version. Install any updates first.

    I haven't done this for a while, but from what I remember:

    Back up [msdb] on the restore server first, just in case.

    Shut down all SQL services and clients on the restore server. Open a DOS window and start the instance in single user mode:

    <path_to_executables>\Binn\sqlservr.exe -c -m

    Open another DOS window, run:

    osql -E

    In OSQL, run:

    restore database msdb from disk = '<whatever>.BAK'

    go

    Exit OSQL. The single user instance in the other window should have terminated. Close both DOS windows, and start all SQL Server services normally. You should have the restored [msdb].

    If you are restoring on to a named instance you will need additional switches on the executables. HTH

    Just to keep you updated, the OP's issue was solved and he may not need that exercise any more, nice try any way..


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Hi Friend,

    Hope your doing well!

    i have small query i need to install MSDB using Litespeed.

    i am getting some problems while installing MSDB using litespeed.

    Could you please provide me the steps to restore MSDB using litespeed?

    thanks

    satheesh

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

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