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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Database Backup Question

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-20 : 02:46:33
see

http://www.codeproject.com/Articles/315538/Exclusive-access-could-not-be-obtained-because-the

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 offline

RESTORE 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-user

Jeff
Go to Top of Page
   

- Advertisement -