USE master;
GO
/*********************************
**** 要设置的参数开始
**********************************/
DECLARE @IsExecSQL BIT = 1, /*是否运行 SQL */
@DBName NVARCHAR(200) = 'JWen_UPB', /*数据库名称*/
@RestoreDBFileName NVARCHAR(1000) = 'D:\JWen_UPB_20190105_140300.BAK', /*要恢复数据库的路径和文件名称*/
@RestoreDBPath NVARCHAR(1000) = 'E:\DataBase\2008\'; /* 要恢复的文件路径(如果为空时,默认为要恢复的数据库路径,否则为 master 的路径)*/
/*********************************
**** 要设置的参数结束
**********************************/
DECLARE @SQL NVARCHAR(MAX),
@bkfName NVARCHAR(1000);
-- 文件路径
IF (LEN(RTRIM(LTRIM(@RestoreDBPath))) = 0)
BEGIN
IF (@DBName IS NULL OR DB_ID(@DBName) IS NULL)
SELECT @RestoreDBPath = RTRIM(REVERSE(filename))
FROM master..sysdatabases
WHERE name = 'master';
ELSE
SELECT @RestoreDBPath = RTRIM(REVERSE(filename))
FROM master..sysdatabases
WHERE name = @DBName;
IF @DBName IS NULL
SET @RestoreDBPath = REVERSE(SUBSTRING(@RestoreDBPath, CHARINDEX('\', @RestoreDBPath) + 5, 260)) + 'BACKUP';
ELSE
SET @RestoreDBPath = REVERSE(SUBSTRING(@RestoreDBPath, CHARINDEX('\', @RestoreDBPath), 260));
END;
-- 1. 备份数据库
IF NOT (
@DBName IS NULL
OR DB_ID(@DBName) IS NULL
)
BEGIN
SET @bkfName = '\DBNAME\_\DATE\_\TIME\.BAK';
SET @bkfName
= REPLACE(
REPLACE(REPLACE(@bkfName, '\DBNAME\', @DBName), '\DATE\', CONVERT(VARCHAR, GETDATE(), 112)),
'\TIME\',
REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
);
SET @SQL
= CHAR(13) + '--/* 1. 备份数据库 */' + CHAR(13) + 'BACKUP DATABASE ' + @DBName + CHAR(13) + ' to disk='''
+ @RestoreDBPath + @bkfName + '''' + CHAR(13) + ' WITH NOFORMAT, INIT, ' + CHAR(13) + ' NAME = '''
+ @DBName + '-Full Database Backup'',' + CHAR(13) + ' SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS=10';
PRINT @SQL;
IF @IsExecSQL = 1
BEGIN
EXEC (@SQL);
PRINT N'
************************************
成功备份数据库到
' + @RestoreDBPath + @bkfName + '
************************************'
END
END;
-- 2. 杀死进程
IF NOT (
@DBName IS NULL
OR DB_ID(@DBName) IS NULL
)
BEGIN
SET @SQL = '';
SELECT @SQL = @SQL + ' kill ' + CAST(spid AS VARCHAR) + CHAR(13)
FROM master.sys.sysprocesses
WHERE dbid = DB_ID(@DBName);
SET @SQL = CHAR(13) + '--/* 2. 杀死进程 */' + CHAR(13) + @SQL;
PRINT @SQL;
IF @IsExecSQL = 1
EXEC (@SQL);
END;
-- 3. 删除数据库
IF NOT (
@DBName IS NULL
OR DB_ID(@DBName) IS NULL
)
BEGIN
SET @SQL = '--/* 3. 删除数据库 */' + CHAR(13) + 'DROP DATABASE [' + @DBName + '];';
PRINT @SQL;
IF @IsExecSQL = 1
EXEC (@SQL);
END;
-- 4. 恢复数据库
IF (LEN(RTRIM(LTRIM(@RestoreDBFileName))) = 0)
BEGIN
PRINT CHAR(13) + '要恢复的数据库备份文件不可以为空!' + CHAR(13);
RETURN;
END;
--restore database myTest from disk='D:\JWen_UPB_20190105_140300.BAK' with file=1,replace,RECOVERY
--生成数据库恢复语句
SET @SQL
= CHAR(13) + '--/* 4. 恢复数据库 */' + CHAR(13) + 'RESTORE DATABASE ' + @DBName + CHAR(13) + +' FROM DISK='''
+ @RestoreDBFileName + '''' + CHAR(13) + +' WITH FILE=1, REPLACE ,RECOVERY ';
--从备份文件中获取逻辑文件名
DECLARE @lfn NVARCHAR(128),
@tp CHAR(1),
@i INT;
--创建临时表,保存获取的信息
DECLARE @T TABLE
(
LogicalName NVARCHAR(128), --文件的逻辑名称
PhysicalName NVARCHAR(260), --文件的物理名称或操作系统名称。
[Type] CHAR(1), -- 文件的类型,其中包括:L = Microsoft SQL Server 日志文件 D = SQL Server 数据文件 F = 全文目录
FileGroupName NVARCHAR(128), --包含文件的文件组的名称
Size NUMERIC(20, 0), --当前大小(以字节为单位)。
MaxSize NUMERIC(20, 0), --允许的最大大小(以字节为单位)。
FileId BIGINT, -- 文件标识符,在数据库中唯一。
CreateLSN NUMERIC(25, 0), --创建文件时的日志序列号。
DropLSN NUMERIC(25, 0) NULL, --文件创建时的日志序列号。如果文件尚未删除,该值为 NULL。
UniqueID UNIQUEIDENTIFIER, --文件的全局唯一标识符。
ReadOnlyLSN NUMERIC(25, 0) NULL, --包含该文件的文件组从读写属性更改为只读属性(最新更改)时的日志序列号。
ReadWriteLSN NUMERIC(25, 0) NULL, --包含该文件的文件组从只读属性更改为读写属性(最新更改)时的日志序列号。
BackupSizeInBytes BIGINT, --此文件的备份的大小(字节)。
SourceBlockSize INT, --包含文件的物理设备(并非备份设备)的块大小(以字节为单位)。
FileGroupID INT, -- 文件组的 ID。
LogGroupGUID UNIQUEIDENTIFIER NULL, --NULL。
DifferentialBaseLSN NUMERIC(25, 0) NULL, --用于差异备份,日志序列号大于或等于 DifferentialBaseLSN 的更改都包含在差异中。对于其他备份类型,该值为 NULL。有关日志序列号 (LSN) 的信息,请参阅日志序列号简介的介绍。
DifferentialBaseGUID UNIQUEIDENTIFIER, --用于差异备份,是差异基准的唯一标识符。对于其他备份类型,该值为 NULL。
IsReadOnly BIT, -- 1 = 该文件为只读文件。
IsPresent BIT,
TDEThumbPrint BIT
);
--从备份文件中获取信息
INSERT INTO @T
EXEC ('restore filelistonly from disk=''' + @RestoreDBFileName + '''');
DECLARE #f CURSOR FOR SELECT LogicalName, [Type] FROM @T;
OPEN #f;
FETCH NEXT FROM #f
INTO @lfn,
@tp;
SET @i = 0;
WHILE @@fetch_status = 0
BEGIN
SELECT @SQL
= @SQL + CHAR(13) + ',MOVE ''' + @lfn + ''' TO ''' + @RestoreDBPath + @DBName + CAST(@i AS VARCHAR)
+ CASE @tp
WHEN 'D' THEN
'.mdf'''
ELSE
'.ldf'''
END,
@i = @i + 1;
FETCH NEXT FROM #f
INTO @lfn,
@tp;
END;
CLOSE #f;
DEALLOCATE #f;
PRINT @SQL;
IF @IsExecSQL = 1
EXEC (@SQL);