Solved

SQL 2008, scheduled database restore fails

Posted on 2014-03-12
1
1,159 Views
Last Modified: 2014-03-13
I have a database which is backed up and restored to another machine over night.
The problem is, whenever SQL management studio is left open, it fails. I know I have to close it when I 'm done working with it, but sometimes I forget and it affects everyone next day.


I don't know how setting single user will bring down the database, even the process which had opened management studio. I think I tried this before, it didn't work either.

Next I

Error message on restoring database:

Executed as user: NT AUTHORITY\SYSTEM. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The scheduled database restore does following;


--Do Actual Restore
RESTORE DATABASE db1
FROM DISK = '\\db-srv\ScheduledBackups\db1.bak'
WITH MOVE 'db1_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.mdf',
MOVE 'db1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.ldf',
REPLACE

use db1
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'username', 'username'
GO


I tried to use SQL statements to  kill all process, but it didn't work. I thought SQL management studio doesn't show as active process in process list.

Options what I found online was;

Solution 1:

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

ref. http://blog.sqlauthority.com/2009/02/06/sql-server-quickest-way-to-kill-all-threads-kill-all-user-session-kill-all-processes/

Solution 2
The other solution I found online is, it specifically set the database offline and it seems to be more promising to close all connections, but still does it close process management studio uses?

ALTER DATABASE <Put Your Database Here> SET OFFLINE WITH ROLLBACK IMMEDIATE

-- And to bring the database online again
ALTER DATABASE <Put Your Database Here> SET ONLINE

http://www.dbatodba.com/sql-server/how-tos/how-to-kill-all-users-in-specific-database-on-sql-server/


I need to test each of solution, but I want to get any additional information before I do it because I won't have much time to do the experiment when I actually do this.
0
Comment
Question by:crcsupport
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39923697
I would go with a SET OFFLINE, and then directly do the restore, which should bring it back online implicitly. you can try that on a test db restored to even another name, connecting to it, and trying to run the restore there ...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question