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 |
|
Sonu619
Posting Yak Master
202 Posts |
 Posted - 2013-04-18 : 21:04:13
|
| Quick Question what would be best approach. I am running job every day in the morning around 5:00 in morning to refresh my database. From last couple of days i am having problem (Job Fail) database in use.. I am wondering is this is beat approach 1 Task = Use Master alter database 'MyDatabase' set single_user 2 Task = Use Master alter database 'MyDatabase' set Multi_User 3 Task = My Restore Database Script Please guide me Thank You. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:23:11
|
| you didnt tell us error returned by job. can you post the error message returned?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-04-19 : 11:03:53
|
| Error is "Exclusive access could noto be obtained because the database is in use" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-04-20 : 02:52:58
|
| One option is to add a script to kill all processes before you try the restore, this WILL depend on the nature of the database. For example, you may not want to do this process if it's a OLTP database or some load process is occuring. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-04-20 : 21:51:12
|
| Thanks Guys, Could you please guide me whats the point after restore the database first SET the user Single Mod and then SET the user Multi Mode?Thank You. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-04-21 : 02:13:26
|
| Do you mean at what point to commit the RESTORE after you have placed it into Single User Mode?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-04-22 : 15:20:12
|
| There is no need to switch the database back to multi-user before the restore. Here is what I do...ALTER DATABASE {my database} SET OFFLINE WITH ROLLBACK IMMEDIATE; -- take current database offlineRESTORE DATABASE {my database} ... WITH NORECOVERY;RESTORE LOG {my database} ... WITH NORECOVERY;...Once everything has been restored, issue the following:RESTORE DATABASE {my database} WITH RECOVERY; -- brings database online - multi-userJeff |
|
|
|
|
|
|
Database Backup Question