I'm calling this from another sql server...
I created a linked server... and want to restore database backups on the other box...
The restore script runs fine when ran locally but fails with the message below when calling it remotely
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
CREATE PROCEDURE usp_restore_database_backups AS
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'BesMgmt', 'single user', true
GO
I have set it to read only dbo only ... single user... still get the same message...
does anyone have any suggestions...try executing...
ALTER DATABASE <MyDB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE <MyDB> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
before the restore statement and drop the sp_dboption.|||thanks Thrasymachus
I'm still getting the same error:
Executed as user: NFCU\sqlsvc. RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101). The step failed.|||post the script you are currently using and try running sp_who when it fails to see what connections are in use. Also when you execute this script in the QA are you connected to the database you are trying to restore? The database selected from the dropdown should not be the database you are trying to restore. If everything is OK with your script I suspect this is the case.|||The error does not tally with this but just belt and braces.
If you run
EXEC sp_helpserver
do you see RPC & RPC out in the status field?|||When I set the database manually into single user mode I get a different error when trying to restore the database... this is the error that comes from when the database is in single user mode....
"Executed as user: NFCU\sqlsvc. Cannot open database requested in login 'BESMgmt'. Login fails. [SQLSTATE 42000] (Error 4060). The step failed."
CREATE PROCEDURE usp_restore_database_backups AS
/*
declare @.x varchar(255)
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('BesMgmt')
exec (@.x)
*/
ALTER DATABASE BesMgmt SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE BesMgmt
FROM DISK = 'D:\MSSQL\BACKUP\BesMgmt\BesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:\MSSQL\Data\BesMgmt\undo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:\MSSQL\Data\BesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:\MSSQL\Data\BesMgmt.ldf'
WAITFOR DELAY '00:00:05'
--EXEC sp_dboption 'BesMgmt', 'single user', true
GO|||The command is within a job...
the sql agent service is ran under a domain user acount...
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment