“恢复数据库”在动态 SQL 中

发布于 2025-01-11 10:17:32 字数 991 浏览 2 评论 0原文

SQL Server 2019。我有一个存储过程，它使用动态 SQL 进行许多操作。除了我的“恢复数据库”部分之外，一切都有效。

这是代码...

SET @CREATE_TEMPLATE = '
                        RESTORE DATABASE __DB_NAME__
                        FROM DISK = ''C:\SQL_MASTER_DB\MASTER_DB_BACKUP.BAK''
                        WITH MOVE ''MASTER_DB'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\__DB_NAME__.mdf'',
                        MOVE ''MASTER_DB_log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\__DB_NAME___log.mdf'' ,  NOUNLOAD,  REPLACE,  STATS = 5
                        ';

                    SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '__DB_NAME__', @newDB);
                    
                    EXECUTE (@SQL_SCRIPT);

如果我单独运行上面的代码（在存储过程之外），它运行良好。数据库按预期恢复。但是当它在我的 SP 内运行时，数据库似乎陷入“恢复”模式。下面的屏幕截图...

原文

SQL Server 2019. I have a stored procedure that uses dynamic SQL for a number of operations. That all works except for my "restore database" part.

Here is the code...

SET @CREATE_TEMPLATE = '
                        RESTORE DATABASE __DB_NAME__
                        FROM DISK = ''C:\SQL_MASTER_DB\MASTER_DB_BACKUP.BAK''
                        WITH MOVE ''MASTER_DB'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\__DB_NAME__.mdf'',
                        MOVE ''MASTER_DB_log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\__DB_NAME___log.mdf'' ,  NOUNLOAD,  REPLACE,  STATS = 5
                        ';

                    SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '__DB_NAME__', @newDB);
                    
                    EXECUTE (@SQL_SCRIPT);

If I run the above code by itself (outside of the stored procedure) it runs fine. DB restores as expected. But when it's run inside my SP the database appears to be stuck in a "restoring" mode. Screen shot below...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦幻之岛 2025-01-18 10:17:32

我不确定为什么它会卡在 Restoring 中，但这里实际上并不需要动态 SQL。 RESTORE 可以完全参数化。

DECLARE @newDB sysname = 'SomeDbName';
DECLARE @mdfPath nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @newDB + '.mdf';
DECLARE @ldfPath nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @newDB + '_log.mdf';

RESTORE DATABASE @newDB
FROM DISK = 'C:\SQL_MASTER_DB\MASTER_DB_BACKUP.BAK'
WITH
  MOVE 'MASTER_DB' TO @mdfPath,
  MOVE 'MASTER_DB_log' TO @ldfPath,
  REPLACE,
  STATS = 5;

I'm not sure exactly why it's stuck in Restoring, but you don't actually need dynamic SQL here. RESTORE can be fully parameterized.

DECLARE @newDB sysname = 'SomeDbName';
DECLARE @mdfPath nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @newDB + '.mdf';
DECLARE @ldfPath nvarchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @newDB + '_log.mdf';

RESTORE DATABASE @newDB
FROM DISK = 'C:\SQL_MASTER_DB\MASTER_DB_BACKUP.BAK'
WITH
  MOVE 'MASTER_DB' TO @mdfPath,
  MOVE 'MASTER_DB_log' TO @ldfPath,
  REPLACE,
  STATS = 5;

回复收藏 0 原文

~没有更多了~