时间戳

首页 > 解决方案 > 无法从同一服务器还原 SQL 数据库并将其重命名

sql-server - 无法从同一服务器还原 SQL 数据库并将其重命名

问题描述

我想从同一台服务器恢复数据库并提供新名称。下面是工作代码。怎么做？

USE [master]
RESTORE DATABASE [Mallesh4000] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\MALLESH' WITH  FILE = 1,
MOVE N'RSSQ001' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Mallesh4000.mdf', 
MOVE N'RSSQ001_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Mallesh4000_log.ldf',  NOUNLOAD,  STATS = 5
GO

下面的代码无法使其动态化。像新的数据库名称，MDFpath，LDFpath

Declare @dbname Varchar(50)
Declare @DBpath VarChar(500)
Declare @mdfpath varchar(500)
Declare @ldfpath varchar(500)

Set @DBNAME = 'Mallesh6000'

Set  @DBname  = [@DBNAME]
Set  @DBpath  = N 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\MALLESH'
Set  @mdfPath = N 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\'+@dbname+'.mdf'
Set  @ldfpath = N 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\'+@dbname+'_log.ldf'

print @DBname
print @DBpath
print @mdfPath
print @ldfpath


USE [master]
RESTORE DATABASE [@dbname] FROM 
DISK = @DBpath WITH  FILE = 1
MOVE N'RSSQ001' TO @mdfPath, 
MOVE N'RSSQ001_log' TO @ldfpath,
NOUNLOAD,  STATS = 5
GO

谢谢。

标签： sql-servertsql

解决方案

关。作为定界标识符[@dbname]将恢复名称为“@dbname”的数据库，并且您缺少几个逗号。

这个：

USE [master]
RESTORE DATABASE [@dbname] FROM 
DISK = @DBpath WITH  FILE = 1
MOVE N'RSSQ001' TO @mdfPath, 
MOVE N'RSSQ001_log' TO @ldfpath,
NOUNLOAD,  STATS = 5

应该

USE [master]
RESTORE DATABASE @DBname
FROM DISK = @DBpath
WITH  FILE = 1,
MOVE N'RSSQ001' TO @mdfPath,
MOVE N'RSSQ001_log' TO @ldfpath,
NOUNLOAD,  
STATS = 5

推荐阅读