Restoring Using t-sql 2012 database

Question

Restoring Using t-sql 2012 database

plamannkathy

SSCrazy

Points: 2190
More actions
November 21, 2014 at 8:07 am

#313281

Hi All
I am practicing restores. I would like to create a set of t-sql scripts I can keep on my servers for restores so I am testing these scripts. The first step I took was to backup the tail-log which left my database set to single_user and in norecovery. Next I attempted a restore of the last full backup and I received the error message that 'Exclusive access could not be obtained because the database is in use.' Can you tell me what I have done wrong?
Thanks
Kathy
--First step for full recovery model restore is to attempt to do a tail-log backup(this code was scripted from the GUI)
USE [master]
ALTER DATABASE [TESTFULL] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
BACKUP LOG [TESTFULL] TO DISK = N'TESTFULL_TailLOG.trn' WITH /*NO_TRUNCATE,*/ NOFORMAT, NOINIT, NAME = N'TESTFULL-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TESTFULL' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TESTFULL' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TESTFULL'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'_TESTFULL_TailLOG.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
-------------------------------------------------------------------
--Step 2 in the full recovery model restore is to restore the last full backup
USE [master]
RESTORE DATABASE [TESTFULL] FROM DISK = N'TESTFULL_FULL_20141120_190322.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
-------------------------------------------------------------------
Error Message:
Exclusive access could not be obtained because the database is in use.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233821 More actions · Answer 1

plamannkathy (11/21/2014)
--Step 2 in the full recovery model restore is to restore the last full backup
USE [master]
RESTORE DATABASE [TESTFULL] FROM DISK = N'TESTFULL_FULL_20141120_190322.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
-------------------------------------------------------------------
Error Message:
Exclusive access could not be obtained because the database is in use.

Use the REPLACE directive like so

USE [master]

RESTORE DATABASE [TESTFULL] FROM DISK = N'TESTFULL_FULL_20141120_190322.bak'

WITH FILE = 1, NORECOVERY, REPLACE, NOUNLOAD, STATS = 5

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

plamannkathy SSCrazy Points: 2190 More actions · Answer 2

Thanks for the input. I tried your suggestion as seen below and I still get the same error message:

USE [master]

RESTORE DATABASE [TESTFULL] FROM DISK = N'TESTFULL_FULL_20141120_190322.bak'

WITH FILE = 1, NORECOVERY, REPLACE, NOUNLOAD, STATS = 5

GO

Exclusive access could not be obtained because the database is in use.

Any ideas?

Thanks

Kathy

Maddave SSCertifiable Points: 6828 More actions · Answer 3

There is still one or more connections to the database. Exec sp_who2 and look to see what connections are connected to your database.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 4

Maddave (11/21/2014)
There is still one or more connections to the database. Exec sp_who2 and look to see what connections are connected to your database.

Not if this completed successfully

USE [master]

ALTER DATABASE [TESTFULL] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

So, burning question is did it complete successfully?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

plamannkathy SSCrazy Points: 2190 More actions · Answer 5

I ran the exec sp_who2 and my TESTFULL database isn't listed. I do have a database that stores statistics from databases, servers, etc. I disabled the jobs for this server. The TESTFULL database remains in the restoring mode and I still get the error message of Exclusive access could not be obtained because the database is in use. Does the restoring mode have the one connection to the database because the database is set to single_user--just brainstorming possibilities

Thanks

Kathy

plamannkathy SSCrazy Points: 2190 More actions · Answer 6

Yes the Alter command to set to single_user did complete successfully. To ensure this I ran this code

USE [master]

ALTER DATABASE [TESTFULL] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

and got this message

Msg 5064, Level 16, State 1, Line 2

Changes to the state or options of database 'TESTFULL' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

Thanks

Kathy

Perry Whittle SSC Guru Points: 233821 More actions · Answer 7

are you running the restore within the same window as the prvious commands?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

plamannkathy SSCrazy Points: 2190 More actions · Answer 8

No. The Alter and Tail-log backup were done in one query window and then the attempt at the full restore was done in another query window.

Thanks

Kathy

Perry Whittle SSC Guru Points: 233821 More actions · Answer 9

run them in same query window as that has the single connection

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

plamannkathy SSCrazy Points: 2190 More actions · Answer 10

plamannkathy

SSCrazy

Points: 2190

November 24, 2014 at 9:13 am

#1760561

Thanks I'll give it a try.

Kathy

ricky_lacayo SSC Enthusiast Points: 115 More actions · Answer 11

Hi!!

If the database is currently in use you won't be able to restore. If no one is supposed to be connected but you, then you should be able to kill every other session.

Try this script:

use master

DECLARE @nameBD VARCHAR (100)

SET @nameBD= 'YourDatabase'

DECLARE @sql VARCHAR (500)

SET @sql = ''

/* This will build a kill command for every session found connected to the database */

select @sql = @sql + ' KILL ' + CAST( spid AS VARCHAR(4 )) + '' FROM DBO.sysprocesses WHERE DB_NAME (dbid) = @nameBD AND spid > 50

select @sql

EXEC(@sql ) -- This EXEC will kill all the connections and let you restore.

/* Credits go to Pinal Dave! */

If you set it to single user mode and lost the connection to someone else, then reconnect to the instance using MASTER as your default database and set YourDatabase back to multi user to recover your connection.

Hope it helps!

regards