SQLCMD Restore works in Management Studio, but not from the DOS prompt
Any idea why my Restore command works fine when launched in Management Studio 2008 but not when launched from a dos prompt?
Below is the error when running from 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 great:
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
No locking or security issues, the database does not exist on the server.
I can not understand.
Any ideas?
a source to share
I found my problem. I used the wrong command line:
SQLCMD -s local\SQL2008 ...
It was -s. This is not a switch for the server name, but a switch for the colseparator
Correct way (note the capital S as well as localhost):
SQLCMD -S localhost\SQL2008 ...
Problem solved guys, thanks for taking the time to look. :)
a source to share
Can you try creating a test database, backing it up, deleting it and restoring it in an 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 it works, what else? Large database backup? Do you have antivirus software that can be scanned? When in doubt, start Sysinternals Filemon while you perform a SQLCMD restore.
a source to share
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).
Remember my database was originally from SQL2005, but I don't think this should be a problem. There must be something.
a source to share