SQLCMD Restore works in Management Studio but not from DOS prompt
- by Gautam
Any idea why my Restore command works fine when run in Management Studio 2008 but not when run from the dos prompt?
Shown below is the error when running from the dos prompt.
C:\>SQLCMD -s local\SQL2008 -d master -Q "RESTORE DATABASE [Sample.Db] FROM DISK = N'C:\Sample.Db.bak' WITH FILE = 1, MOVE N'Sample.Db' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db.mdf', MOVE N'Sample.Db_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db_log.ldf', NOUNLOAD, REPLACE, STATS = 10"
Msg 3634, Level 16, State 1, Server GAUTAM, Line 1
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db.mdf'.
Msg 3156, Level 16, State 8, Server GAUTAM, Line 1
File 'Sample.Db' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Server GAUTAM, Line 1
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db_log.ldf'.
Msg 3156, Level 16, State 8, Server GAUTAM, Line 1
File 'Sample.Db_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server GAUTAM, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server GAUTAM, Line 1
RESTORE DATABASE is terminating abnormally.
However if I execute this directly in Management Studio 2008, it works fine:
RESTORE DATABASE [Sample.Db] FROM DISK = N'C:\Sample.Db.bak' WITH FILE = 1, MOVE N'Sample.Db' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db.mdf', MOVE N'Sample.Db_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Sample.Db_log.ldf', NOUNLOAD, REPLACE, STATS = 10
There is no lock or security issues, the data base doesn't exist on the server.
I can't figure it out.
Any ideas?
