Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2006-03-28 : 11:36:47
|
| Hi,One very elementary question.After my log restores I want my db to be on recovery mode.How do I do this?ALTER DATABASE Test1 set Multi_user?it is not working? |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-03-28 : 11:40:56
|
| If the database is still in a loading state, use Restore database test1 with recovery |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-04 : 11:31:12
|
| Is there any way to take it back into 'norecovery' option so that logs can be applied to it again? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-04 : 11:36:01
|
| STANDBY option maybe? That will leave the database READONLY.Kristen |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-04 : 11:41:25
|
| Yes, but it does not allow logs to be applied and have you disconnect all users. This can be a pain.The only way I know is to resote a full backup again with 'norecovery' which we cannot afford to do as it is very time consuming for multiple 60G size dbs. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-04 : 11:50:08
|
| "but it does not allow logs to be applied"I though that was the point of STANDBY - i.e. for Log Shipping"and have you disconnect all users"Well, you aren't going to be able to restore whilst users are connected I'm afraid!Is Replication an option?Kristen |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-04 : 12:09:48
|
| It has its own problems..But there will be substantial locking unless you use the concurrent snapshot option. Recommended is either restore a copy of the backup on your subscriber(s), or you find a quiet time to generate the snapshot and use the option to 'use the snapshot files from the following folder' option when creating your pull subscription. You can then copy the snapshot to the subscriber and apply it there.The concurrent option does not generate the locks that the default option does, but it takes much longer to generate and apply.Because of the sizes of my dbs, Restore from a backup of the publication database on the subscriber and fix constraints, identity columns, and triggers there for not for replication. If you can't do this during a quiet period on the publisher there will be sync issues which you will have to resolve, i.e. data in the publisher which is not in the subscriber between the time you did the backup and created the subscription.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replimpl_86wj.asp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-04 : 13:21:44
|
quote:
Originally posted by SQLCode
Is there any way to take it back into 'norecovery' option so that logs can be applied to it again?
If you've already used the WITH RECOVERY option, then no. You would have to start over again.Tara Kizeraka tduggan |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2006-04-04 : 13:39:58
|
| Thanks Tara, also looks like replication snapshot does generate locks for initial backup and the dbs I have, it can freeze..Thanks for all your wonderful support. |
|
|
|
|
|
|
|
|
Recover option