数据库附加、分离、还原、备份（sqlserver2014）

数据库附加

USE [master]
GO
--附件加后的数据库名称
CREATE DATABASE [test1] ON 
--数据库存放地址
( FILENAME = N'E:\WorkCode\deployment\database\test1.mdf' ),
( FILENAME = N'E:\WorkCode\deployment\database\test1_log.ldf' )
 FOR ATTACH
GO

数据库还原（未完待续）

DECLARE @DBName NVARCHAR(50);
DECLARE @sql NVARCHAR(MAX);
DECLARE @FileSavePath NVARCHAR(1024);
DECLARE @BackupFilePath NVARCHAR(1024);

SET @DBName = 'test3333';--还原后的数据库名称
SET @FileSavePath = 'd:\test333';--还原后的数据库保存路径
SET @BackupFilePath = 'E:\WorkCode\backup\test.bak'; --数据库备份地址
SET @sql = 'RESTORE database ' + @DBName + ' from disk= ''' + @BackupFilePath + ''' with file=1,RECOVERY,';

CREATE TABLE #temp1
(
    LogicalName NVARCHAR(255),
    PhysicalName NVARCHAR(1024),
    Type NVARCHAR(10),
    FileGroupName VARCHAR(20),
    SIZE BIGINT,
    MaxSize BIGINT,
    FileId NVARCHAR(40),
    CreateLSN BIGINT,
    DropLSN BIGINT,
    UniqueId VARCHAR(40),
    ReadOnlyLSN VARCHAR(40),
    ReadWriteLSN VARCHAR(40),
    BackupSizeInBytes VARCHAR(40),
    SourceBlockSize VARCHAR(40),
    FileGroupId VARCHAR(40),
    LogGroupGUID VARCHAR(40),
    DifferentialBaseLSN VARCHAR(40),
    DifferentialBaseGUID VARCHAR(40),
    IsReadOnly VARCHAR(40),
    IsPresent VARCHAR(40),
    TDEThumbprint VARCHAR(40)
);

--从备份文件中获取信息
INSERT INTO #temp1
EXEC ('RESTORE filelistonly from disk=''' + @BackupFilePath + '''');
SELECT *
FROM #temp1;
SELECT @sql = @sql + CASE
                         WHEN Type = 'D' THEN
                             'MOVE ''' + LogicalName + ''' TO ''' + @FileSavePath + '.mdf'','
                         WHEN Type = 'L' THEN
                             'MOVE ''' + LogicalName + ''' TO ''' + @FileSavePath + '_log.ldf'''
                     END
FROM #temp1;


EXEC (@sql);
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
BEGIN

    DROP TABLE #temp1;
END;

数据库备份

完全备份

BACKUP DATABASE [GOODJOBFRAME]
TO  DISK = N'E:\backup\database\test.bak'
WITH NOFORMAT,
     NOINIT,
     NAME = N'test',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10,
     CHECKSUM;
GO
DECLARE @backupSetId AS INT;
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'test'
      AND backup_set_id =
      (
          SELECT MAX(backup_set_id)
          FROM msdb..backupset
          WHERE database_name = N'test'
      );
IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'验证失败。找不到数据库“test”的备份信息。', 16, 1);
END;
RESTORE VERIFYONLY
FROM DISK = N'E:\backup\database\test.bak'
WITH FILE = @backupSetId,
     NOUNLOAD,
     NOREWIND;
GO

差异备份

BACKUP DATABASE [test]
TO  DISK = N'E:\backup\database\test.bak'
WITH DIFFERENTIAL,
     NOFORMAT,
     NOINIT,
     NAME = N'',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10;
GO

查看备份介质中的备份信息

restore headeronly from disk='E:\backup\test.bak';

列出备份集中包含的文件信息

restore filelistonly from disk='E:\backup\test.bak';

参考文章

• SQLserver完全、差异、日志备份与恢复命令