SQLCMD Restore works in Management Studio but not from DOS prompt

Question

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?

Answer 1

Can you try creating a test database, backing it up, deleting it, and restoring it in a SQLCMD session. i.e.

SQLCMD
Create database junk
go
Backup database junk to disk='c:\junk.bak'
go
Drop database junk 
go
Restore database junk from disk='c:\junk.bak'....

If that works, what's different? Is the other database backup large? Do you have anti-virus software that could be scanning it? If in doubt, run Sysinternals Filemon while running the SQLCMD restore.

Answer 2

Shot in the dark, but are you running the CMD prompt as Administrator? If not, can you try?

Answer 3

SQLAcid: This seemed to work:

C:\>SQLCMD -s local\SQL2008 -d master -Q "CREATE DATABASE [junk];"

C:\>SQLCMD -s local\SQL2008 -d master -Q "BACKUP DATABASE [junk] TO DISK='C:\junk.bak'"
Processed 152 pages for database 'junk', file 'junk' on file 1.
Processed 2 pages for database 'junk', file 'junk_log' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.182 seconds (6.892 MB/sec).

C:\>SQLCMD -s local\SQL2008 -d master -Q "RESTORE DATABASE [junk] FROM DISK='C:\junk.bak' WITH  FILE = 1,  MOVE N'junk' TO N'C:\Pr
ogram Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\junk.mdf',  MOVE N'junk_log' TO N'C:\Program Files\Microsoft SQL Serve
r\MSSQL10.SQL2008\MSSQL\DATA\junk_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 100"
100 percent processed.
Processed 152 pages for database 'junk', file 'junk' on file 1.
Processed 2 pages for database 'junk', file 'junk_log' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.070 seconds (17.920 MB/sec).

Mind you my database was originally from SQL2005 but I don't think that should be an issue. Something has to be up.

Answer 4

I found my problem. I was using the command line which is wrong:

 SQLCMD -s local\SQL2008 ...

It was the -s. This is not the switch for server name but rather the switch for colseparator

The correct way would be (note the capital S as well as localhost):

 SQLCMD -S localhost\SQL2008 ...

Problem solved guys, thanks for taking the time to look. :)