SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Database Backup Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 04/18/2013 :  21:04:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/19/2013 :  02:23:11  Show Profile  Reply with Quote
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

195 Posts

Posted - 04/19/2013 :  11:03:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/20/2013 :  02:46:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 04/20/2013 :  02:52:58  Show Profile  Visit jackv's Homepage  Reply with Quote
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

195 Posts

Posted - 04/20/2013 :  21:51:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 04/21/2013 :  02:13:26  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Edited by - jackv on 04/21/2013 02:13:53
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 04/22/2013 :  15:20:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000