I am actually trying to make a script (in Sql Server 2008) to restore one database from one backup file. I made the following code and I am getting an error -
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because
the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
How do I fix this problem ?
IF DB_ID('AdventureWorksDW') IS NOT NULL
BEGIN
RESTORE DATABASE [AdventureWorksDW]
FILE = N'AdventureWorksDW_Data'
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\Backup\AdventureWorksDW.bak'
WITH FILE = 1,
MOVE N'AdventureWorksDW_Data'
TO N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW.mdf',
MOVE N'AdventureWorksDW_Log'
TO N'C:\Program Files\Microsoft SQL Server\
MSSQL10_50.SS2008\MSSQL\DATA\AdventureWorksDW_0.LDF',
NOUNLOAD, STATS = 10
END
This question is related to
sql
sql-server
sql-server-2008
I got this error when there was not enough disk space to restore Db. Cleaning some space solved it.
Here's a way I am doing database restore from production to development:
NOTE: I am doing it via SSAS job to push production database to development daily:
Step1: Delete previous day backup in development:
declare @sql varchar(1024);
set @sql = 'DEL C:\ProdAEandAEXdataBACKUP\AE11.bak'
exec master..xp_cmdshell @sql
Step2: Copy production database to development:
declare @cmdstring varchar(1000)
set @cmdstring = 'copy \\Share\SQLDBBackup\AE11.bak C:\ProdAEandAEXdataBACKUP'
exec master..xp_cmdshell @cmdstring
Step3: Restore by running .sql script
SQLCMD -E -S dev-erpdata1 -b -i "C:\ProdAEandAEXdataBACKUP\AE11_Restore.sql"
Code that is within AE11_Restore.sql file:
RESTORE DATABASE AE11
FROM DISK = N'C:\ProdAEandAEXdataBACKUP\AE11.bak'
WITH MOVE 'AE11' TO 'E:\SQL_DATA\AE11.mdf',
MOVE 'AE11_log' TO 'D:\SQL_LOGS\AE11.ldf',
RECOVERY;
execute this query before restoring database:
alter database [YourDBName]
set offline with rollback immediate
and this one after restoring:
alter database [YourDBName]
set online
Use the following script to find and kill all the opened connections to the database before restoring database.
declare @sql as varchar(20), @spid as int
select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>')
and spid != @@spid
while (@spid is not null)
begin
print 'Killing process ' + cast(@spid as varchar) + ' ...'
set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)
select
@spid = min(spid)
from
master..sysprocesses
where
dbid = db_id('<database_name>')
and spid != @@spid
end
print 'Process completed...'
Hope this will help...
Solution 1 : Re-start SQL services and try to restore DB Solution 2 : Re-start system / server and try to restore DB Solution 3 : Take back of current DB, Delete the current/destination DB and try to restore DB.
I just restarted the sqlexpress service and then the restore completed fine
I got this error when unbeknownst to me, someone else was connected to the database in another SSMS session. After I signed them out the restore completed successfully.
Setting the DB to single-user mode didn't work for me, but taking it offline, and then bringing it back online did work. It's in the right-click menu of the DB, under Tasks.
Be sure to check the 'Drop All Active Connections' option in the dialog.
Use Master
alter database databasename set offline with rollback immediate;
--Do Actual Restore
RESTORE DATABASE databasename
FROM DISK = 'path of bak file'
WITH MOVE 'datafile_data' TO 'D:\newDATA\data.mdf',
MOVE 'logfile_Log' TO 'D:\newDATA\DATA_log.ldf',replace
alter database databasename set online with rollback immediate;
GO
I experienced this issue when trying to restore a database on MS SQL Server 2012.
Here's what worked for me:
I had to first run the RESTORE FILELISTONLY
command below on the backup file to list the logical file names:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'
This displayed the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively:
LogicalName PhysicalName
com.my_db C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf
com.my_db_log C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf
All I had to do was to simply replace the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively in my database restore script:
USE master;
GO
ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE my_db
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_backup.bak'
WITH REPLACE,
MOVE 'com.my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db.mdf',
MOVE 'com.my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\com.my_db_log.ldf'
GO
ALTER DATABASE my_db SET MULTI_USER;
GO
And the Database Restore task ran successfully:
That's all.
I hope this helps
I think you just need to set the db to single user mode before attempting to restore, like below, just make sure you're using master
USE master
GO
ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
For me, the solution is:
Check Overwrite the existing database(WITH REPLACE) in optoins tab at left hand side.
Uncheck all other options.
Select source and destination database.
Click ok.
That's it.
Source: Stackoverflow.com