1

I am attempting a Log Shipping failover test and the step which is intended to put the database into restoring mode is failing with the error "Exclusive access could not be obtained because the database is in use".

The action was carried out through SSMS selecting "Transaction Log" and also selecting "backup the tail of the log" under Media Options which should leave the database in "restoring mode".

After the failure I attempted to put the database into single user mode first and also take it offline but both commands didn't work (or fail).

I have repeated the action against a test database and that worked with no problem. The T-SQL is below:

BACKUP LOG [TESTDB] TO  DISK = N'U:\MSSQL\Backup\TESTDB.bak' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'TESTDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TESTDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TESTDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TESTDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'U:\MSSQL\Backup\TESTDB.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

I also checked for any blocking or running transactions but nothing showed up.

Any ideas anyone?

Marathon_Nick
  • 11
  • 1

1 Answers1

0

A log backup that leaves the target database in RESTORING is called a "Tail Log Backup". And taking the database offline requires terminating all other connections to the database. Like this:

use testdb
go
alter database testdb set single_user with rollback immediate
go
use master
BACKUP LOG [TESTDB] TO  DISK = N'c:\temp\TESTDB.bak' WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'TESTDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10, CHECKSUM
GO
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67