第三篇――第二部分――第五文配置SQLServer镜像――域环境SQLServer镜像日常维护(五)
odel.', 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName
AND recovery_model = 3 )
BEGIN
RAISERROR ('The specified database [%s] is using the simple
recovery model. Please check the name entered or
do not supply a database name if you want to back up
the log for all online databases using the full or
bulk-logged recovery model.', 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM sys.databases
WHERE name = @DBName
AND source_database_id IS NOT NULL )
BEGIN
RAISERROR ('The specified database [%s] is a database snapshot.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.', 16, 1, @DBName);
RETURN;
END
IF EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = @DBName )
BEGIN
RAISERROR ('The specified database [%s] is a log shipping
primary and cannot have its log file backed up.
Please check the name entered or do not supply
a database name if you want to back up the log
for all online databases using the full or
bulk-logged recovery model.', 16, 1, @DBName);
RETURN;
END
END
IF @LogBackupLocation IS NULL
BEGIN
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory', @BakDir OUTPUT, 'no_output';
IF @BakDir IS NOT NULL
BEGIN
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir;
END
END
END
IF @LogBackupLocation IS NULL
BEGIN
SELECT TOP 1
@BakDir = LEFT(MF.physical_device_name,
LEN(MF.physical_device_name)
- CHARINDEX('\',
REVERSE(MF.physical_device_name)))
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily MF ON MF.media_set_id = BS.media_set_id
WHERE NOT EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = BS.database_name )
-- log backups first, then differentials, then full backups
ORDER BY BS.type DESC ,
BS.backup_finish_date DESC; -- newest first
IF @BakDir IS NOT NULL
BEGIN
DELETE FROM @FileExists
INSERT INTO @FileExists
EXEC sys.xp_fileexist @BakDir;
SELECT @Exists = ParentDirectoryExists
FROM @FileExists
IF @Exists = 1
BEGIN
SET @LogBackupLocation = @BakDir;
END
END
END
IF @LogBackupLocation IS NOT NULL
BEGIN
IF RIGHT(@LogBackupLocation, 1) <>
'\'
SET @LogBackupLocation = @LogBackupLocation + '\';
END
ELSE
BEGIN
RAISERROR ('Backup location not specified or not found.', 16, 1);
RETURN;
END
INSERT INTO @DBs
( DBName
)
SELECT name
FROM sys.databases D
WHERE state = 0
AND --online
-- 1 = Full, 2 = Bulk-logged, 3 = Simple
-- (log backups not needed for simple recovery model)
recovery_model IN ( 1, 2 )
AND
-- No log backups for core system databases
name NOT IN ( 'master', 'tempdb', 'msdb', 'model' )
AND
-- If is not null, database is a database snapshot
-- and can not be backed up
source_database_id IS NULL
AND
-- Backing up the log of a log-shipped database will
-- break the log shipping log chain
NOT EXISTS ( SELECT 1
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database = D.name )
AND ( name = @DBName
OR @DBName IS NULL
);
SELECT @MaxID = MAX(DBID) ,
@CurrID = 1
FROM @DBs;
WHI