代码之家  ›  专栏  ›  技术社区  ›  Gautam

sqlcmd restore可以在management studio中工作,但不能在dos提示符下工作

  •  3
  • Gautam  · 技术社区  · 16 年前

    知道为什么我的restore命令在management studio 2008中运行时可以正常工作,但在dos提示符下运行时不行吗?

    从DOS提示符运行时出现的错误如下所示。

    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.
    

    但是,如果我在Management Studio 2008中直接执行此操作,则它可以正常工作:

    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
    

    没有锁定或安全问题,服务器上不存在数据库。

    我想不出来。

    有什么想法吗?

    4 回复  |  直到 16 年前
        1
  •  2
  •   Gautam    16 年前

    我发现了我的问题。我使用的命令行是错误的:

     SQLCMD -s local\SQL2008 ...
    

    是-s。这不是服务器名的开关,而是colseparator的开关

    正确的方法是(注意大写字母s和localhost):

     SQLCMD -S localhost\SQL2008 ...
    

    问题解决了,伙计们,谢谢你们花时间看。:)

        2
  •  1
  •   SqlACID    16 年前

    可以尝试在sqlcmd会话中创建测试数据库、备份、删除并还原它吗?即

    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'....
    

    如果成功了,有什么不同?另一个数据库备份大吗?你有可以扫描它的防病毒软件吗?如果有疑问,请在运行sqlcmd还原时运行sysinternals filemon。

        3
  •  1
  •   Gautam    16 年前

    sqlacid:这似乎有效:

    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).
    

    请注意,我的数据库最初来自sql2005,但我不认为这是一个问题。一定有什么事。

        4
  •  0
  •   JRL    16 年前

    在黑暗中拍摄,但你是作为管理员运行命令提示符吗?如果没有,你能试试吗?