SQLServer T-SQL 备份还原数据库

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);