May 20, 2011 at 10:17 am
Today I tried to restore a database, I happened to have a couple of questions about this to clarify.
1. For both sql 2005, or/and sql 2008, does it still need no other user connection to database in order to restore?
2. Today I tried first to restore a database, I first setup it to single user mode in ssms, then I did a restore from a backup file that comes from production server, then it restores successfully and it changed itself back to multi user mode, I thought I should either change it to multi user mode in ssms or using sql: alter database db-name set MULTI_USER
But I didn't do that, it changed by itself, why is that?
Thank you
May 20, 2011 at 10:23 am
sqlfriends (5/20/2011)
Today I tried to restore a database, I happened to have a couple of questions about this to clarify.1. For both sql 2005, or/and sql 2008, does it still need no other user connection to database in order to restore?
2. Today I tried first to restore a database, I first setup it to single user mode in ssms, then I did a restore from a backup file that comes from production server, then it restores successfully and it changed itself back to multi user mode, I thought I should either change it to multi user mode in ssms or using sql: alter database db-name set MULTI_USER
But I didn't do that, it changed by itself, why is that?
Thank you
The database can have no user connections when you restore it.
A easy way to do that is to set it offline to kick everyone out and prevent them from re-connecting.
alter database [MyDatabase] set offline with rollback immediate
When you restore a database, it goes to the mode it was in at the time of the last backup you applied.
May 20, 2011 at 10:38 am
Thanks.
when you say, When you restore a database, it goes to the mode it was in at the time of the last backup you applied.
Does this mean: if the backup copy of the database is in multi -user mode, the restored db from it will be in multi_user mode too.
May 20, 2011 at 12:51 pm
Yes. The restored database is exactly the same as the database was at time of backup. That's structure, data, settings, everything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 3:08 pm
Thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy