July 10, 2006 at 12:41 pm
Hello,
We are hoping to be able to perform a DB restore remotely (backup file on remote server). This is to avoid the overhead incurred from having to copy large backup file over to local server then to restore it. Ideally, we are hoping to be able to point the restore to the backup file on the remote server and then restore it locally.
I've tried a few attempts but keep running into same error. I'm hoping for a second set of eyes and to benefit from the expertise on this site.
OK, here's my code example :
EXEC sp_addumpdevice 'disk','adaadhoc1_backup',
'\\Adaadhoc1\f$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MSCAR_DataWarehouse\MSCAR_DataWarehouse_db_200606031215.BAK'
EXEC sp_helpdevice
restore database MSCAR_Datawarehouse
from adaadhoc1_backup
with REPLACE
EXEC sp_dropdevice 'adaadhoc1_backup'
From the examples on the MS site this is what I was able to create however, I'm encountering following error :
Executed as user: NT AUTHORITY\SYSTEM. 'Disk' device added. [SQLSTATE 01000] (Message 15444) Cannot open backup device 'adaadhoc1_backup'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Thanks in advance and hope I'm posting my question to the appropriate board.
Don
July 10, 2006 at 12:54 pm
hello, that error has to do with the NT AUTHORITY\SYSTEM permissions. do you have that users in the server users? It is not SQL Server. Microsoft has that know issue. There is a workaround for that NT AUTHORITY user. Sorry, don't remember the location, but go to MSDN Knowledge base, there are quite a few samples of that.
So, your SQL is fine and it has to do with permissions. Good luck (that happenmed to me a couple of years back and it took quite a while to figured that out.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply