sql-server - 恢复存储过程

Question

我在 SQL Server 2008 R2 中有一个数据库，我创建了这个用于恢复数据库的存储过程:

CREATE PROCEDURE [dbo].[usp_DBRestore]
@DBName nvarchar(60)
,@BackName nvarchar(120)
,@OutMessage nvarchar(4000) output
--,
--@DataName varchar(60),
--@DataFileName varchar(120),
--@LogName varchar(60),
--@LogFileName varchar(120)
AS

BEGIN TRY
    USE [master]

    ALTER DATABASE @DBName SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE @DBName FROM
        DISK = @BackName WITH
        FILE = 1,  NOUNLOAD, 
        REPLACE,
        PASSWORD = 'TEST'
    SET @OutMessage = 'OK';
    ALTER DATABASE @DBName SET  MULTI_USER WITH ROLLBACK IMMEDIATE
END TRY
BEGIN CATCH

    ALTER DATABASE @DBName SET  MULTI_USER WITH ROLLBACK IMMEDIATE

    INSERT [dbo].[ErrorLog]
  (
   [UserName], 
   [ErrorNumber], 
   [ErrorSeverity], 
   [ErrorState], 
   [ErrorProcedure], 
   [ErrorLine], 
   [ErrorMessage] 
   )
   VALUES(
          CONVERT(sysname, CURRENT_USER), 
          ERROR_NUMBER(),
          ERROR_SEVERITY(),
          ERROR_STATE(),
          ERROR_PROCEDURE(),
          ERROR_LINE(),
          ERROR_MESSAGE()

         )
END CATCH

当我执行代码时，我看到了这个错误:

a USE database statement is not allowed in a procedure, function or trigger.

我该如何解决这个错误？

Answer 1

你不能那样做——你基本上有两个选择:

坚持使用存储过程，但在这种情况下，您必须使用动态 SQL。您的存储过程创建了一个 SQL 语句字符串，它允许它使用 USE master它允许它动态设置数据库名称等，然后它使用 sp_executesql @sqlRestoreStatement 执行该 SQL 语句.如果你想看看这个，你必须 一定要阅读(并理解)Erland Sommarskog 的开创性文章 The Curse and Blessings of Dynamic SQL

您可以使用常规 SQL 脚本，可能带有 SQLCMD 占位符(如果您在 SQL Server Management Studio 中启用了 SQLCMD mode)并从常规脚本执行恢复(例如，您可以将其放入您自己的模板文件夹中)。在这种情况下，你会有类似的东西:

:setvar dbname YourDatabaseNameHere

DECLARE @FileName NVARCHAR(255)
SET @FileName = N'D:\YourBackupDirectory\SomeDatabase.bak' 

RESTORE DATABASE [$(dbname)]
FROM DISK = @FileName
WITH FILE = 1,  
MOVE N'YourDatabase_Data' TO N'D:\MSSQL\Data\$(dbname).mdf',  
MOVE N'YourDatbase_Log' TO N'D:\MSSQL\Data\$(dbname)_Log.ldf',  
NOUNLOAD, REPLACE,  
STATS = 2
GO

通过此设置，您可以轻松地将 SQL 脚本用作模板并使用它恢复任何类型的数据库。