CREATE PROCEDURE RestoreDatabase
(
@ToRestoreDatabaseName nvarchar(50), --要恢复的数据库名
@FromBackupDisk nvarchar(500), --从哪个备份文件恢复
@IsRestored bit OUTPUT --是否恢复
)
AS
BEGIN TRY
USE master;
GO RESTORE @ToRestoreDatabaseName
FROM DISK = @FromBackupDisk
GO
SET @IsRestored = 'true'
END TRY
BEGIN CATCH
SET @IsRestored = 'false'
END CATCH错误如下;
Msg 154, Level 15, State 1, Procedure RestoreDatabase, Line 9
a USE database statement is not allowed in a procedure, function or trigger.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@ToRestoreDatabaseName'.
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@IsRestored".
Msg 137, Level 15, State 1, Line 5
Must declare the scalar variable "@IsRestored".为什么有这些错误呢?谢谢!
(
@ToRestoreDatabaseName nvarchar(50), --要恢复的数据库名
@FromBackupDisk nvarchar(500), --从哪个备份文件恢复
@IsRestored bit OUTPUT --是否恢复
)
AS
BEGIN TRY
USE master;
GO RESTORE @ToRestoreDatabaseName
FROM DISK = @FromBackupDisk
GO
SET @IsRestored = 'true'
END TRY
BEGIN CATCH
SET @IsRestored = 'false'
END CATCH错误如下;
Msg 154, Level 15, State 1, Procedure RestoreDatabase, Line 9
a USE database statement is not allowed in a procedure, function or trigger.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@ToRestoreDatabaseName'.
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@IsRestored".
Msg 137, Level 15, State 1, Line 5
Must declare the scalar variable "@IsRestored".为什么有这些错误呢?谢谢!
FROM DISK = @FromBackupDisk---这种写法不支持,写成动态SQL
(
@ToRestoreDatabaseName nvarchar(50), --要恢复的数据库名
@FromBackupDisk nvarchar(500), --从哪个备份文件恢复
@IsRestored bit OUTPUT --是否恢复
)
AS
BEGIN TRY
--USE master;
--GO RESTORE DATABASE @ToRestoreDatabaseName
FROM DISK = @FromBackupDisk
--GO
SET @IsRestored = 'true'
END TRY
BEGIN CATCH
SET @IsRestored = 'false'
END CATCH
--看看行吗?
请问, 为什么在RESTORE 后面加上DATABASE后就好了呢?
[ FROM < backup_device > [ ,...n ] ] DATABASE指定从备份还原整个数据库。如果指定了文件和文件组列表,则只还原那些文件和文件组。
参考联机丛书
就像备份要写backup database xxxx to disk.....一样
数据库名为"ABC",已经备份为ABC_BACK.BAK,现在我把它恢复成别的文件的数据库名称"ABC123",可是却提示:
"TITLE: Microsoft SQL Server Management Studio
------------------------------Restore failed for Server 'a1'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476------------------------------
ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ABC.mdf' cannot be overwritten. It is being used by database 'ABC'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476------------------------------
BUTTONS:OK
------------------------------
"请问有没有办法可以让它恢复呢?谢谢!