Backup--备份相关的信息查看及小技巧

--查看指定数据库当前最小 LSN

DECLARE @database_name NVARCHAR( 200)

SET @database_name ='DBName'

SELECT MIN (redo_start_lsn)

FROM master .sys. master_files

WHERE database_id = DB_ID ( @database_name)

AND TYPE = 0

AND STATE IN (0, 1)

AND redo_start_lsn IS NOT NULL

--判断某日志备份是否能应用到当前正在还原的 DB上

USE master ;

DECLARE @MyBitVar BIT;

EXEC sp_can_tlog_be_applied

@backup_file_name = N'C:\AdventureWorks2012.bak' ,

@database_name = N'AdventureWorks2012',

@result = @MyBitVar OUTPUT;

SELECT @MyBitVar

--使用RESTORE Headonly 来查看备份信息

DECLARE @command NVARCHAR( 1000)

DECLARE @backup_header TABLE

(

BackupName NVARCHAR( 128) NULL,

BackupDescription NVARCHAR (256) NULL,

BackupType INT,

ExpirationDate DATETIME NULL,

Compressed INT,

POSITION INT,

DeviceType INT,

UserName NVARCHAR( 128) NULL,

SERVERNAME NVARCHAR( 128),

DatabaseName NVARCHAR( 128),

DatabaseVersion INT,

DatabaseCreationDate DATETIME,

BackupSize NUMERIC( 20,0 ) NULL,

FirstLsn NUMERIC( 25,0 ) NULL,

LastLsn NUMERIC(25 ,0) NULL,

CheckpointLsn NUMERIC( 25,0 ) NULL,

DatabaseBackupLsn NUMERIC (25, 0) NULL,

BackupStartDate DATETIME,

BackupFinishDate DATETIME,

SortOrder INT,

CodePage INT,

UnicodeLocaleId INT,

UnicodeComparisonStyle INT,

CompatibilityLevel INT ,

SoftwareVendorId INT,

SoftwareVersionMajor INT,

SoftwareVersionMinor INT,

SoftwareVersionBuild INT,

MachineName NVARCHAR( 128),

Flags INT NULL,

BindingId UNIQUEIDENTIFIER NULL,

RecoveryForkId UNIQUEIDENTIFIER NULL,

COLLATION NVARCHAR( 128) NULL,

FamilyGUID UNIQUEIDENTIFIER NULL,

HasBulkLoggedData BIT NULL,

IsSnapshot BIT NULL,

IsReadOnly BIT NULL,

IsSingleUser BIT NULL,

HasBackupChecksums BIT NULL,

IsDamaged BIT NULL,

BeginsLogChain BIT NULL,

HasIncompleteMetadata BIT NULL,

IsForceOffline BIT NULL,

IsCopyOnly BIT NULL,

FirstRecoveryForkID UNIQUEIDENTIFIER NULL,

ForkPointLSN NUMERIC (25, 0) NULL,

RecoveryModel NVARCHAR (60) NULL,

DifferentialBaseLSN NUMERIC( 25,0 ) NULL,

DifferentialBaseGUID UNIQUEIDENTIFIER NULL,

BackupTypeDescription NVARCHAR( 60) NULL,

BackupSetGUID UNIQUEIDENTIFIER NULL,

CompressedBackupSize NUMERIC( 20,0 ) NULL

)

DECLARE @backup_file_name NVARCHAR( 500)

SET @backup_file_name =''

SELECT @command = N'restore headeronly from disk = N'''

+ REPLACE (@backup_file_name, N'''', N'''''') + N''''

INSERT INTO @backup_header

EXECUTE(@command )

SELECT *

FROM @backup_file_name

--=======================================================
使用TF3226来阻止将备份记录写入ERRORLOG
TF 3226被开启后，备份完成后，不会向ERRORLOG里写记录
TF 3326 对数据库备份和日志备份都起效
http://msdn.microsoft.com/zh-cn/library/ms188396.aspx

--=======================================================
使用DBCC来查看备份过程
DBCC traceon (3004, 3605, -1)
--参考：http://www.cnblogs.com/lyhabc/p/3452894.html