locked
migration of sql server 2005 RRS feed

  • Question

  • Hi all

    I hav a windows server 2003 standard edition where i have sql server 2005 data base and  i have  a windows server 2008 standard edition with sql server 2008 . i want  to migrate sql server 2005 database(server 2003) to sql server 2008(server 2008).

    When i  try to restore, it shows that an error "there is in use".

    i need help

    thanks 

    Tuesday, April 13, 2010 1:03 PM

Answers

  • When you restore a database on an existing database, you should make sure that there are no other users connected to the existing database. Restore always requires exclusive lock for itself , so go to activity monitor or run the sp_who2 stored proc and see the spids that use the database and kill them in first place and then start your restore and now you wont get any error with ‘database is already in use’


    Thanks, Leks
    Tuesday, April 13, 2010 9:40 PM
    Answerer
  •  

    1. Its better you can change the database mode from Multi user to restricted mode.
    2. ALTER DATABASE dbname SET RESTRICTED_USER
    3. Before executing this command inform all your users.
    4. Now you'll be able to restore the database .
    5. But don't forget to change the database back to MULTI_USER
    6. ALTER DATABASE dbname SET MULTI_USER
    7. Now we are back to production.

    I think this will help you .

     

    have a nice day.


    Friday, April 16, 2010 1:02 PM

All replies

  • Hi,

    Pls elaborate the error. Your explanation is not clear enough.


    Apps Dev
    Tuesday, April 13, 2010 3:28 PM
  • When you restore a database on an existing database, you should make sure that there are no other users connected to the existing database. Restore always requires exclusive lock for itself , so go to activity monitor or run the sp_who2 stored proc and see the spids that use the database and kill them in first place and then start your restore and now you wont get any error with ‘database is already in use’


    Thanks, Leks
    Tuesday, April 13, 2010 9:40 PM
    Answerer
  • Hi,

    Thanks for your post!

    Could you please post the restore statement here?

    Also, take a look at another thread in the forum, see:http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/d315d8f4-d7e2-4197-9cbb-4cbe086af33e.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, April 15, 2010 3:43 AM
  •  

    1. Its better you can change the database mode from Multi user to restricted mode.
    2. ALTER DATABASE dbname SET RESTRICTED_USER
    3. Before executing this command inform all your users.
    4. Now you'll be able to restore the database .
    5. But don't forget to change the database back to MULTI_USER
    6. ALTER DATABASE dbname SET MULTI_USER
    7. Now we are back to production.

    I think this will help you .

     

    have a nice day.


    Friday, April 16, 2010 1:02 PM