SQL Server Performance Forum – Threads Archive

Restore database error

Hi All I’m trying to create a ‘restore database’ job that will run once a week, this will be a copy of a database that is running on another server. Here is the code: RESTORE DATABASE [KNS-TEST1] FROM DISK = N’ E:AMDCProductionName Changed to Protect the GuiltyProduction DataDatabase BackupKNS-AMDC1-Backup' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE , MOVE N’kns_dat’ TO N’E:AMDCProductionSQL Server DatabasesKNS-TEST1_Data.mdf’, MOVE N’kns_log’ TO N’E:AMDCProductionSQL Server DatabasesKNS-TEST1_Log.ldf' Here is the error: 18204 : BackupDiskFile:<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />penMedia: Backup device ‘E:AMDCProductionName Changed to Protect the GuiltyProduction DataDatabase BackupKNS-AMDC1-Backup’ failed to open. Operating system error = 123(The filename, directory name, or volume label syntax is incorrect.). The directories DO exist. The backup file exists, and I can do a ‘manual’ restore with it. Thanks Bruce (clueless newbe)

What is the backup file name and its extension?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check whether path (backup) is correct and ensure SQLagent has got relevant priviliges on this share. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

MohammedU,
Name= ‘KNS-AMDC1-Backup’ ,no extension Satya SKJ,
The path/name has been copied directly from the windows explorer address block,
The E: drive is not a share, it’s the second drive on the Win2K server. Bruce

To resolve this behavior, make sure that the MSSQLSERVER account has been granted the Access this computer and that it has Full Control permissions to the folder where the backup file is to be created. If the backup file already exists on the remote share, make sure the MSSQLSERVER account has Full Control permissions to the existing file Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

As Satya mentioned… looks like it is access issue, run the following in Query Window and see what you get… master..xp-cmdshell ‘dir E:AMDCProductionName Changed to Protect the GuiltyProduction DataDatabase Backup’
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I double-checked the permissions…
The service SQLSERVERAGENT is running with an administrator account, the administrator account has full access to the database directory/files, and the backup directory/files. MohammedU,
I tried the master..xp-cmdshell ‘dir E:AMDCProductionName Changed to Protect the GuiltyProduction DataDatabase Backup’ in the SQL Query Analyzer and got…. Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘cmdshell’. Bruce

I can’t post exact command in xp-cmdshell replace ‘-‘ with ‘_’
and make sure it turns red…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MohammedU,
Thank you! The problem turned out to be the ‘spaces’ in the directory name. The dir command gave me the hint as to were the problem was. Thanks again to all who helped!! Bruce

Most of the when you have spaces in the folders name you have to use double quote ‘"’. Good to know issue resolved. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>