???? Dual-Machine Hot standby database backup script v2.1

text/*this4.0 database backup script [author] rulition qq:7355157[version] v2.1[modified] May 21, 2010 15:35:13 finish May 24, 2010 11:35:13 Details modified       May 25, 2010 11:31:13 Increase consistency check before [full backup] May 27, 2010 10:31:13 Check the size of the differential backup to avoid large files affecting differential backups.       May 28, 2010 11:14:15 make a backup check to see if a full backup has been done recently.       October 17, 2010 21:58:32 Toggle @ Backup Path [If this is the first run, you can Select @ backup path = @ Backup Path 1] March 17, 2011 15:00:00 Increase single-month backup, increase full backup frequency, repair (30) Delete outdated backup files       2012-10-26 14:53 Modify Month backup to \\202.202.202.26\Y$\This_BackUp 2012-10-31 10:00 increase the intermediate calculation results that show ' insufficient free disk space ' during normal operation. March 31, 2015 10:00:00 Modify the description and change the DBCC to execute at night.----2015-04-03 v2.0 Modify the process, put the externally referenced program in the front, handle the parameter check, add more instructions, the differential backup file is too large to prompt the hand Work. Environment SQL2000 and SQL2005 test through [source] http://blog.csdn.net/rulition/article/details/47999561[description] Using a dual-machine hot spare disk space, will THIS4.0 The backup schedule for the database is set to the following ways: Differential backup-executed every n hours, stored to # and C:, reserved for n days.        "Full Backup"-Press free space, specify at 12:25 21:25 03:25, store to cluster space, keep x days.              For example: The current setting of the foot is: 1\ to the SQL scheduled task requirements, every 3 hours of 25 ticks run once, at 12:25, 21:25, 03:25 of these three points of time for a full backup, other points of time differential backup. 2\ month 1th monthly the backup suffix name is no longer. bak, but. YBF。 (You can also put a backup in a different place by a single month.) The consistency check of the 3\ database is placed on the after midnight. [Features] 1) The frequency of backup is adjusted by the SQL Scheduled Task 2) The operation log is collected by the SQL scheduled task by appending (see Advanced options for Scheduled Tasks) 3) Delete the expired backup file before performing the backup operation, as opposed to the SQL operating mode (the pre-backup preparation work) section, as in SQL operating mode. 4) automatically determine and establish a backup folder, delete expired backup files by retention time 5) automatically checks for sufficient space before performing a backup operation, automatically avoids [differential backup] [full backup] at the same time, and performs a consistency check. 6) Check the size of the differential backup to avoid large files affecting the differential backup. and automatically do a [full backup] [note] 1) The script is heavily used in MS-DOS commands, if not very familiar, please do not modify!   2) The FORFILES.exe command is used to delete outdated backup files, otherwise it cannot be executed. This can be used in the Win2000 Environment command, is Forfiles v 1.1-[email protected]-4/98 and Win7 system comes with the Chinese version, in the use of parameters is a little different, please pay attention to use!         3) in order to reduce the operation log size, in the (consistency check) This section called Isql.exe, this file is not in the SQL2005, can be copied from the SQL2000 system, the use of attention to the path.        4) All externally referenced programs (including paths) must be stored in the same path as the # machine and the * * machine, and the program is not missing the corresponding supporting files, otherwise, the program will not work properly. 5) required FORFILES.exe and Isql.exe can be obtained from the following address: http://download.csdn.net/detail/rulition/9048595[reference] 1) Clean up the system backup log, delete the history of the backup Job 2) About SQL2005 Remote backup * See the last part of the script exec usp_job_backup_this4 0*/create Proc usp_job_backup_this4 (@Is_Run int = 0)----0- The test runs and displays the action record, and when @Is_Run = 1 o'clock, all actions are performed automatically. As--------SET NOCOUNT on----Select GetDate () as ' Start time '---test--Declare @Is_Run int--Select @Is_Run = 0----IF @Is_Run = 0 Print ' "Test Status" '--------/* Set external reference program (with path) */decl is @ISQL varchar (DECLARE) @FORFILES varchar () Select @ISQL = ' C:\THIS_BAT\isql.exe ' select @FORFILES = ' C:\THIS_BAT\ Forfiles.exe '----open xp_cmdshell support-exec sp_configure ' show advanced options ', reconfigure with override--Exec Sp_co Nfigure ' xp_cmdshell ', 1--Reconfigure with override--Exec sp_configure ' show advanced options ', 0--reconfigure with OV Erride----parameter Check declare @SQL varchar (8000) Select @SQL = ' dir ' [email protected]declare @result int Exec @result = Xp_c        Mdshell @SQL, no_outputif (@result = 1) Begin Print ' ERROR: No external reference program found: ' [email protected] Return        Endselect @SQL =replace (@SQL, @ISQL, @FORFILES) Exec @result = xp_cmdshell @SQL, no_outputif (@result = 1) Begin Print ' ERROR: No external reference program found: ' [email protected] Return End--------/* Set run parameter */declare @ database name varchar (+) De Clare @ Backup path varchar (DECLA)Re @ backup file varchar (+) Declare @ Backup method varchar (Declare @ retention time varchar () Declare @ BACKUP statement varchar (----Select @ database name = ' THI S4 ' Select @ backup file = Convert (char (8), GETDATE (),) + replace (Convert (char (6), GETDATE (), 108), ': ', ')--------/* Full backup-parameter settings */if Convert (Varchar,getdate (), between ' 03:00:00 ' and ' 04:00:00 ' Beginselect @ Backup method = ' Full backup ' Select @ backup path = ' \ \ \ 202.202.202.26\y$\this_backup ' SELECT @ backup file = @ backup file + '. bak ' Select @ retention time = ' 2 '----monthly backup of 1th # The prefix is changed to. YBF----IF (Day (getdate () ) =1 and Month (GETDATE ())%2 = 1)----Single month----if (Day (getdate ()) =1 and Month (GETDATE ())%2 = 0)----Two-month if Day (GETDATE ()  ) =1begin Select @ Backup mode = @ Backup mode + ' month backup ' ' SELECT @ Backup path = ' \\202.202.202.26\Y$\This_BackUp ' SELECT @ Backup file = Replace (@ backup file, '. Bak ', '. YBF ') Select @ retention time = ' 9600 ' endend----ELSEIF Convert (varchar,getdate (), between ' 12:00:00 ') and ' 13:00:00 ' beginselect @ Backup mode = ' Full backup ' SELECT @ Backup path = ' \\202.202.202.46\X$\This_BackUp ' SELECT @ backup file = @ backup file + '. Bak ' Select @ retention time = ' 2 ' End----ELSEIF Convert (VARCHAR,GETDATe (), between ' 21:00:00 ' and ' 22:00:00 ' beginselect @ Backup mode = ' full backup ' Select @ Backup path = ' \\202.202.202.26\Z$\This_ Backup ' SELECT @ back up file = @ backup file + '. bak ' Select @ retention time = ' 2 ' endelse--------/* Differential backup-parameter setting (if time limit is required, refer to the above method.) ) */begin SELECT @ Backup mode = ' differential backup ' Declare @ Backup path 1 varchar Declare @ Backup path 2 varchar () Select @ Backup Path 1 = ' \\202.202.202.32\C$\ This_backup ' SELECT @ Backup path 2 = ' \\202.202.202.33\C$\This_BackUp ' SELECT @ backup file = @ backup file + '. dif ' Select @ retention time = ' 1 ' End--------S Elect ' ★ ' + @ Backup mode + ' ★ '--------/* Parameter check */----Check @ database name if not EXISTS (Select 1 from master.dbo.sysdatabases where [Email&nbs P;protected] Database name) Begin Select ' ERROR: The specified database name was not found, or the current instance is case-sensitive! ' Return End----Extract valid data for the last backup declare @backup_set_id int----The ID number of the backup operation declare @backup_size numeric----backup file size, Fruit/1024 to be consistent with the computer. [Unit: kb]declare @description nvarchar (510)----backup description Declare @name nvarchar----backup name, followed by ' _kb ' + then prepared library file size [in KB] Declare @backup_finish_date datetime----Backup finish Declare @expiration_date dateTime----Backup expiration----Initial value setting Select @backup_size =1, @description = ' No valid backup file found ', @name = ' None '----by backup  The method provides the last data for the Select top 1 @backup_set_id = backup_set_id, @backup_size = IsNull (backup_size/1024,1), @description = [description], @name = [name], @backup_finish_date = backup_finish_date, @expiration_date = Expiration_date from MS           Db.dbo.backupset (NOLOCK) where type = case when @ Backup mode = ' differential backup ' then ' I ' ELSE ' D ' end and database_name = @ Database name                 and Expiration_date > getdate ()----valid data order BY BACKUP_SET_ID DESC----Display intermediate data IF @Is_Run = 0 Begin Print ' Valid data for last backup ' Select @backup_set_id as ' ID number ', @backup_size as ' file Size ', @des                Cription as ' description ', @name as ' name ', @backup_finish_date as ' Finish time ', @expiration_date as ' expiry time ' END----Processing @ backup path if @ Backup mode = ' differential backup ' Begin----switch @ Backup path SELECT @ Backup path = @ Backup path 2IF @description = @ backup path 1+ ' \ ' [email protecte d] Database name Select @ backup Path = @ StandbyCopy path 2 IF @description = @ Backup path "\" [email protected] Database name Select @ backup Path = @ Backup Path 1 Else Print ' last backup ' + @descrip tion End Print ' This backup ' + @ Backup path + ' \ ' + @ database name----Check @ Backup path Select @SQL = ' IF not exist ' + @ Backup path + ' \ ' [Email pro Tected] Database name + ' \*.* MD ' + @ Backup path + ' \ ' [email protected] database name Exec @result = xp_cmdshell @SQL, no_outputif (@result = 1) beginprint (' ERROR: Unable to access the specified backup path ' + @ Backup path + ' (reason: 1, no access rights.) 2. The target disk does not exist. ') print (@SQL) returnend--------print ' backup file ' + @ backup file--------/* Pre-backup preparation work */---------------------------------------- ---------------------------------------------------------(10) Gets the remaining space size of the partition where the @ backup path is located [converted to KB, approximate = Available Bytes/1024*0.95] Select @SQL = ' dir ' + @ Backup path + ' | Find "Available Bytes" > "%temp%\rulition" ' exec xp_cmdshell @SQL, no_output Exec xp_cmdshell ' for/f ' tokens=3 delims= '%i In (%temp%\rulition) does @echo%i > "%temp%\rulition", no_outputselect @SQL = ' for/f "tokens=1-9 delims=,"%1 in (%te mp%\rulition) do @echo%1%2%3%4%5%6%7%8%9 '----to pass results to the table [must be filled with null values, otherwiseError! ]create Table #disk_space (free_sizes VarChar (+) NULL) Insert into #disk_space Exec xp_cmdshell @SQLDelete from #disk_ Space where free_sizes is null----extract result declare @disk_space numeric Select @disk_space = Convert (numeric,free_sizes)/1024* 0.95 from #disk_space Drop table #disk_ Space-------------------------------------------------------------------------------------------------(20) Get current database size (ref.: sp_spaceused) [converted to KB, approximate = statistical result/1024*0.95]select @SQL = ' Declare @dbsize bigint ' + CHAR (+) + ' Declar E @logsize bigint ' + char (Ten) + ' Declare @database_size bigint ' + char (Ten) + ' Select @dbsize = SUM (convert bigint , Case is status & 0 then size ELSE 0 end), ' + CHAR (+ ' @logsize = SUM (CONVERT (bigint,case when Statu S & <> 0 then size ELSE 0 end) ' + char (Ten) + ' from ' + @ database name + '. Dbo.sysfiles ' + char (Ten) + ' Select (conver T (Dec (15,2), @dbsize) + CONVERT (Dec (15,2), @logsize)) * 8192 '----Pass the results to the table "CREATE Table #database_size" (files_sizes Dec(15,2) NULL) Insert into #database_size Exec (@SQL)----Extract result declare @database_size numeric Select @database_size = Convert (numeric,files_sizes)/1024*0.95 from #database_size Drop table #database_ Size-------------------------------------------------------------------------------------------------(25) Consistency check /* If the consistency check takes a long time during the day, it can be set to be processed only at night.                (For details, see operation results Log) */if (@ Backup method = ' Full backup ' and (Convert (Varchar,getdate (), between ' 03:00:00 ' and ' 04:00:00 ')) Begin /* Before the database backup, the data consistency check should be done: 1. Run checkpoint process checkpoint [forces all dirty pages of the current database to be written to disk. ] 2. Check database DBCC CHECKDB 3. Check the page for DBCC CHECKALLOC 4.                 Check the system tables for DBCC CHECKCATALOG and then make a database backup. ----"DBCC CHECKDB" is a significant CPU and disk-intensive operation.                Each data page that needs to be inspected must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to sort.                It is recommended that you run DBCC CHECKDB when the server load is low.            If you run DBCC CHECKDB during peak load times, transactional throughput performance and DBCC CHECKDB finish performance will be affected.    ----DBCC Checkalloc checks for Allocations and page usage (including indexed views) in the database.                The NOINDEX option for backward compatibility only applies to indexed views as well.                If you have already executed DBCC CHECKDB, you do not have to perform DBCC CHECKALLOC.                DBCC CHECKDB is a superset of DBCC CHECKALLOC, which includes checking the allocation, in addition to checking the index structure and data integrity.                */----★ Consistency check [only keep the final result of the check to the log file, note that the user name and password used to modify Isql.exe and the path where Isql.exe is located]★print ' consistency check ' Declare @CHECK VarChar----Print ' 1. Run the checkpoint process (' + Convert ' (Varchar,getdate () (+) + ') ' IF (@Is_Run = 1) CHECKPOINT----Print ' 2. Check the database (' + Convert ' (varcha R,getdate () + + ') ' Select @CHECK = @ISQL + '-s ' + @ @SERVERNAME + '-d ' + @ Database name + '-u sa-p server-q "D                Bcc checkdb (' + @ database name + ') "-O"%temp%\check_db.log "-W 9999 & Find" checkdb ""%temp%\check_db.log "'            if (@Is_Run = 0) Print @CHECK if (@Is_Run = 1) EXEC xp_cmdshell @CHECK----    Print ' 3. Check page (' + Convert (varchar,getdate (), + ') ' Select @CHECK = @ISQL + '-S ' [email protecte D] @SERVERNAME + '-d ' + @ Database name + '-u sa-p server-q ' dbcc CHECKALLOC (' + @ database name + ') "-O"%temp%\check.log "-W 9999 &amp ; Find "Checkalloc" "%temp%\check.log" ' if (@Is_Run = 0) Print @CHECK if (@Is_Run = 1) EXEC x                P_cmdshell @CHECK----Print ' 4. Check the system table (' + Convert (Varchar,getdate (), 21) + ') '                 Select @CHECK = @ISQL + '-s ' + @ @SERVERNAME + '-d ' + @ Database name + '-u sa-p server-q ' DBCC CHECKCATALOG (' + @ database name + ') '                if (@Is_Run = 0) Print @CHECK if (@Is_Run = 1) EXEC xp_cmdshell @CHECK---- Print ' 5. Consistency check completed (' + Convert (varchar,getdate (), + ') ' End--------------------------------------- ----------------------------------------------------------(30) Delete the expired backup file----any greater than (@ retention time) 24 hours will be deleted, pay attention to the extension!! ----Forfiles.exe also specify the path, otherwise this operation cannot be performed.Declare @ delete operation nvarchar (4000)----Select @ delete operation = ' pushd ' + @ Backup path + ' \ ' + @ database name + ' && ' + @FORFILES + '-M ' [EMAIL&NB Sp;protected] Database name + ' _*.bak-c ' cmd/c echo delete @FILE & del @FILE "d ' + @ retention time + ' && ' + ' popd ' Print '" Delete file "retention  Time: ' + @ Keep time + ' days '----handle differential backups. DIFIF @ Backup mode = ' differential backup ' Select @ Delete operation =replace (@ delete operation, ' _*.bak ', ' _*.dif ')----monthly backup of number 1th. YBF IF Day (getdate ()) =1 Select @ delete operation =replace (@ delete operation, ' _*.bak ', ' _*.YBF ')----if (@Is_Run = 0) Print @ delete operation if (@Is_Run = 1) Exec x P_cmdshell @ Delete action-------------------------------------------------------------------------------------------------(40) Backup feasibility estimate----If [the remaining space] is not sufficient to store [the expected backup] size, delete the expired backup file and exit if it is not enough. ----[size of expected backup] = [size of last backup file]/[size of database at last backup] * [size of current database]----The backup strategy of SQL is to be deleted before backup, and it is a waste of time to decide beforehand. --------[the size of the database at the time of the last backup] was taken from msdb.dbo.backupset.name and marked when the backup statement was generated. ----Declare @last_data_sizes numericdeclare @ evaluation result varchar (8)----Select @last_data_sizes = 1 SELECT @ Evaluate result = ' through '----if last backup When there is no ' _kb ' tag, the default is 1. IF (CharIndex (' _kb ', @name) =0) Select @last_data_sizes = 1 Elsebegin----If there is only the ' _kb ' tag but no data, the default is 1. if (CharIndex (' _kb ', @name) + 3) > LEN (@name) Select @last_data_sizes = 1----Otherwise, extract the ' _kb ' after the data else Select @last_data_siz Es = Convert (numeric,substring (@name, CharIndex (' _kb ', @name) +3,20) End----Show details of the evaluation if (@Is_Run = 0) beginprint ' "Assessment" Select @backup_set_id as ' last Backup ID: ' Select IsNull (@backup_size, 1) as ' the size of the last backup file (KB) ', IsNull (@last_data_sizes, 1) as ' last Size of database at backup (KB) ' Select @database_size as ' current database size (KB) ', @disk_space as ' free disk space (KB) ' Print ' "This backup" ' + @ Backup path + ' \ ' + @ database name en D----Start evaluation----Check the size of the [full backup] If @ Backup method = ' Full backup '/* If [size of last backup file] = 1, compare [current database size]*0.80 and [free disk space] here (*0.80) is a person experience value, can be combined with each "assessment situation" roughly calculated.         */BEGIN IF @last_data_sizes = 1 begin IF @database_size * 0.80 > @disk_space begin Select @ Backup path + ' free disk space is insufficient to save the database backup file, exit the operation! ' + CHAR ' as ' "Error prompt 1" ' Select @database_size * 0.80 as ' @database_size * 0.80 ', @disk_space as ' @disk_space ' Return end end----[size of expected backup] = [size of last backup file]/[size of database at last backup] * [when         The size of the previous database] ELSE Begin IF (@backup_size/@last_data_sizes) * @database_size > @disk_space Begin Select @ Backup path + ' free disk space is insufficient to save to database backup file, exit operation! ' + CHAR ' as ' "Error 2" ' Select (@backup_size/@last_data_sizes) * @database_size as ' pre Backup size ', @disk_space as ' disk space ' Return end end----Check the [differential backup] size if (@ Backup method = ' differential backup ' and (@las t_data_sizes <> 1))/* If [the size of the last backup file] is more than 30% relative to [the current database size], the last full backup failed, and at the same time, a full backup has not been done recently, you must finish Full backup, otherwise, an excessive amount of large-volume differential backup files will consume the corresponding disk space, resulting in a differential backup failure. (v2.0 hints and exits) here (over 30%) is a person experience value, can be combined with each "assessment situation" roughly calculated. */begin if (IsNull (@backup_size, 1)/@database_size > 0.3) IF not EXISTS (Select 1 from Msdb.dbo.backu       PSet WHERE type = ' D ' and backup_set_id > @backup_set_id and [name] like '% ' + @ database name + '% ') Begin Print ' "sentient hint" ' + ' last (differential backup) file is large, it is possible that the last full backup failed! Please manually run "full backup" for remediation. ' Print ' "hint" ' + ' if this problem occurs more than once, please check all relevant actions or settings. ' Select @ Evaluation result = ' failed ' Return endend----IF @ evaluation result = ' through ' Print ' evaluation passed, Can back up! "'---------------------------------------------------------------------------------------------/* Organization @ BACKUP statement *  /select @ Backup statement = ' Select @ BACKUP statement = ' BACKUP database ' + @ DB name + ' to DISK = N ' ' + @ Backup path + ' \ ' [email protected] database name + ' \ ' + @ database name + ' _ ' [email protected] backup file + ' with retaindays = ' + @ retention time + ', Noformat, noinit, name = N ' + @ Database name + ' _ ' + @ Backup method + ' _ ' + @ Backup file + ' _kb ' + Convert (varchar, @database_size) + ' ', SKIP, Norewind, nounload, STATS = +, ' + ' DESCRIPTION = ' + @ Backup path + ' \ ' + @ database name + ' '----IF @ Backup method = ' differential backup ' Select @ BACKUP statement = @ BACKUP statement + ', differential '----/* Show @ BACKUP statement */print '            "Backup Statement" ' Print @ BACKUP statement----/*[differential backup] Dodge [Full backup]*/if exists (Select spid from master.dbo.sysprocesses where db_name (dbid) = @ database name and status = ' runnable ' and cmd= ' backup database ' Begin Print @ database name + ' backing up, skipping this operation! ' + CHAR (Ten) returnend----/* Perform operation */if (@Is_Run = 1) Exec (@ BACKUP statement)----Select GetDate () as ' end time '----/*[reference 1]---- Cleanup Logging SELECT * from Msdb.dbo.backupsetSelect * from msdb.dbo.restorehistory----Delete entries for all backup sets older than the specified date in the backup and restore history tables. Because some rows are added to the backup and restore history tables after a backup or restore operation, using sp_delete_backuphistory can reduce the size of the history table in the msdb database. ----Declare @dt datetime Select @dt = cast (N ' 01/01/2015 00:00:00 ' as datetime) EXEC msdb.dbo.sp_delete_backuphistory @ DT----Delete Job history EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = ' 01/01/2015 00:00:00 ' EXEC msdb. Sp_maintplan_delete_log null,null, ' 01/01/2015 00:00:00 ' [reference 2] about SQL2005 remote backup rulition 10:19 2009-10-17 to implement SQL remote backup, You must handle the problem of local remote access: First, the remote computer and the local machine preferably in the same network segment, if the network segment (or VLAN), need switch routing support, or even modify the native routing table.      Second, the "User name" and "password" that provide SQL services for this machine must be [legitimate users] of [remote computer] and be able to [fully manipulate] [backup folder] of [remote computer].      In general, when you install SQL services, you use a system account (mostly administrator), which is a much simpler process if it happens to be a legitimate user of a remote computer. Otherwise, you must use xp_cmdshell to invoke MS-DOS's net use command when making a remote backup, using the other's [Legal user] to register to operate the [backup folder].      Third, for [remote computer] [backup folder], you can pre-share the [backup folder] on [remote computer] and specify a user name that can be fully accessed.      If you don't want the unrelated person to see this [backup folder], you can add $ to the share name after sharing it, for example: Backup folder $ or, when using net use for [legitimate users] registration, use the other super user directly, so that you can directly access the [backup folder]. For example: \\COMPUTER\E$\ backup folder net use \\COMPUTER\E$\ backup folder/user: username password if it is a null password, "" Instead of:/user: User name "" */<span style= "fon t-family:arial; Background-color: #ffffff "></span> 

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

???? Dual-Machine Hot standby database backup script v2.1