April 9, 2010 at 7:23 am
Hi, I have log shipping databases primary and secondary; and I want to make secondary online for a test, I am planning to use tail log backups for switch and switch back;
I take tail log backup from primary as:
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [Test] TO DISK = N'D:\Test_tail.trn' WITH NORECOVERY
I can bring secondary as:
RESTORE LOG [Test] FROM DISK = N'D:\Test1_tail.trn' WITH RECOVERY
ALTER DATABASE Test SET MULTI_USER WITH ROLLBACK IMMEDIATE
....Some action in DB....
Tail log from secondary:
ALTER DATABASE Test1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP LOG [Test] TO DISK = N'D:\Test_tail2.trn' WITH NORECOVERY
Restore to primary fails with error:
RESTORE LOG [Test] FROM DISK = N'D:\Test_tail2.trn' WITH RECOVERY, STATS = 10
Exclusive access could not be obtained because the database is in use.
Primary db is in restoring state and I cannot even drop it or recover it, I think because I have put it in single user mode before taking tail backup it can not be recovered,
Am I missing something?
Or mustn't I take db to single user before tail log, how can I take tail backup with a db with connected users?
Thanks,
April 9, 2010 at 7:36 am
If you put database in single user, and took the tail log backup, you might want to use the same window to execute further statements.
However you can use sysprocesses to see the connection to the db is sleeping and then kill it.
April 9, 2010 at 7:44 am
Thanks jeroshan, I quitted all windows and it worked! But all the connections were with master in sysprocesses not Test db..
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply