Point in time restore issue - urgent
-
A user updated about 25,000+ records in our prod db, I'm trying to restore copy of prod db to test server so the specific lost records can be exported from test to prod. I'm keep getting database in use errors even though I’m the only one who logged on test server - tried sp_who2 - I just see myself. Thanks
USE PIT_TEST
RESTORE DATABASE PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_db_200707222213.BAK'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200723070011.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707230413.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707230821.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707231215.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707231615.TRN'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 3:21 PM'
====================================================================
Error:
Server: Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
-
Before you restore a database that is already being used you need to check if the database is being used by users if so kill those process so that the database can be restored else it will fire an error as given by you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You say you are the only one using the database - I am assuming you mean the database itself not the server.
Even if you sp_who2 shows up with a connection of your own to that database you can't restore until that connection no longer exists.
If you are restoring using QA, make sure the database in the drop down box is set to another database (like master) before running your RESTORE command.
-
Check whether ghost cleanup process is going on the database..
-
Use this script to kill alll user connection to database and then do the restore
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=24
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
best is to start with putting your db read_only and single_user.
USE PIT_TEST
go
ALTER DATABASE PIT_TEST Set single_USER , READ_only WITH ROLLBACK IMMEDIATE
-- don't put a go here, because others may takeover it you do !
RESTORE DATABASE PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_db_200707222213.BAK'
WITH NORECOVERY
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200723070011.TRN'
WITH NORECOVERY
....
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
1. do you have jobs running? If so, those are connections (users). Stop the SQL Server Agent service while you need it in single user mode.
2. did you open Query Analyzer from Enterprise Manager? If so, that is two connections (users). Close Enterprise Manager and just use Query Analyzer.
3. You can also try logging in via QA using the SA account instead of your account.
-SQLBill
-
Tried according to ALZDBA, still getting same error. Please see the attachment, as you can see just the background processors are running, closed – EM, stopped SQL agent..
-
Everyone is making this too hard. Do it this way, it will make it impossible for anyone to be in the database becase it will be offline.
use master
go
alter database PIT_TEST set offline with rollback immediate
-
Simply do this.
use master select 'kill',spid from master..sysprocesses where db_name(dbid) = 'PIT_TEST'
Take the output of this and do the following
--Make sure that you run all these commands in one go
use master
Go
Paste the outut of the above command here
Go
RESTORE DATABASE PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_db_200707222213.BAK'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200723070011.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707230413.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707230821.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707231215.TRN'
WITH NORECOVERY
GO
RESTORE LOG PIT_TEST
FROM DISK = 'D:\MSSQL\Backup for Month\C_PIT\C_PROD_tlog_200707231615.TRN'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 3:21 PM'
-
I guess your rollback operation may be taking to long.
If that is the case (long runnig transaction or a transaction with many logrecords), maybe your're better off with restarting your sqlserver instance. That way the rollback is being performed faster because it is done at startup time of the database, before any other users can get to it.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Following command:
use master
select 'kill',56 from master..sysprocesses where db_name(dbid) = 'PIT_TEST'
Prints nothing: (0 row(s) affected)
I was able to recover the data, simply updating the changed records, but still trying to figure out why point in time script didn’t work….
-
Jimmpy,
This has happened to me in the past as well. Since we can run sp_who2 from any database, it's possible it was run while connected to the database that you are trying to restore. To resolve this, all you have to do is select another database, like master, or add a "use master" about sp_who2.
If you still have the info from sp_who2, you can check to see which database you were connected to.
Also, if you want to kick everyone off -- this should do the trick...
-- restrict access
use master
alter database <database name> set single_user with rollback immediate;
go
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply