asked
Restoring fails in SQL SERVER 2008 R2
Hi, Recently I have installed sql server 2008 R2 version. I have an external tool which connects to sql server and restores the database. I can restore any number of times and also delete the database using my external tool. This worked well with sql server 2000, sql server 2005, sql server 2008 but not with sql server 2008 R2. When I restore for the first time using my external tool it goes well. When I restore it again it says " you have connected users". Here is the query (used in the application tool) and the result. select * from master.dbo.sysprocesses a, master.dbo.sysdatabases b, master.dbo.sysusers c where b.dbid=a.dbid and c.uid=a.uid and b.name='TEST_34' dbname :PDM_TEST_34 username:dbo loginame:sa hostname: program_name login_time: 2010-11-19 10:23:54.370 lastwaittype: CHECKPOINT_QUEUE That means sessions are still existing even after the first restore has been done. Please let me know if you still need some more information. It will be great to have a solution or workaround. Thanks in advance, Santosh Challa.
santosh challa - you are using the old join syntax which has been listed for deprecation. Please use the new join syntax so that you don't have problems in future SQL Server releases. You should change your joins to use LEFT JOIN or INNER JOIN instead of using commas and join predicates in the WHERE clause.
Make sure that the no users are connected to the Database that you are trying to restore. You can use sp_who2 or sys.sysprocesses to find the users who are connected to the database that you are restoring.
You will need to close all connections to the database you want to drop before dropping. I suggest you query the server to find the users connected to the database in question then, if it is ok to do so, kill these connections: DECLARE @dbname sysname = 'DatabaseName' SELECT KillCommand = 'KILL ' + CAST(session_id AS varchar(100)) FROM sys.dm_exec_requests DER WHERE database_id = DB_ID(@dbname)
As everyone has already noted, the error is caused by existing connections. There's nothing special about 2008R2 that would lead to this error. If you had existing connections on 2000 or 2005 this same error should occur. Assuming your users are not 'sa' or 'dbo', you can try this script to isolate the database: ALTER DATABASE AdventureWorks SET RESTRICTED_USER Then to turn it back off: ALTER DATABASE AdventureWorks SET MULTI_USER But, since you're likely dealing with open connections, you need to clear them: ALTER DATABASE AdventureWorks SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE This will clear the open transactions and kill the connections that are not sa or dbo. I'd be very, very careful about using this on a production system.
Hi, I did some R & D and find out that if sql server is logged in with user 'sa' then I don't get error. That means the sql server 2008 R2 (microsoft sql server management studio) is always opened with 'sa' whch don't cause error while restoring with my tool. But opening my sql server 2008 R2 might be risk. So still waiting for the solution. Kind regards, Santosh Challa.
