SQL Server Multi-point, timely backup tips

To ensure the security of the database, we plan the database disaster recovery strategy, including local backup, offsite backup, RAID, or offsite disaster recovery using high availability (such as log shipping, mirroring, replication, and so on). Because the SQL Server database is backed up with only one backup policy (such as full-diff-log), create only one complete policy for a database, do not create multiple, otherwise the backup chain is not up, and the backup is on one side. For backups, a full backup + differential backup + log backup is generally used, or full backup + differential backup + log shipping , but only one file is backed up. In order to have the same backup stored offsite, SQL Server has several parameters that can be set to be saved in multiple places, such as MIRROR to, copy_only, but only for full backups. In all other cases, use Windows commands to copy to other places to do redundant storage.

In the past, a method has been used to copy data from n days to other places (refer to Forfiles and xcopy to copy files changed within n days under Windows), but using the method copy of Windows jobs is not timely. This introduces a method, in time offsite storage to the network path, that is, the database backup is completed, immediately copy the files to a remote location.

In a folder, there are many backup files, how to filter out the files just backed up?

Batch processing with Windows commands should be done, but it will be too cumbersome to come out. Then find it in SQL Server! ~

Each backup in SQL Server has a record, and the backup name, path, and time waits can be found from the backup history.

Take master full backup as an example:

SELECT TOP (1) media_set_id, namefrom msdb.dbo.backupset WHERE database_name = ' master ' and type = ' D ' ORDER by Backup_finis H_date descselect physical_device_name from msdb.dbo.backupmediafamily WHERE media_set_id = 2048

These backups are automatically backed up by a maintenance plan, with only one backup file in each backup set, so you can uniquely identify a file! ~

By the way, the name in Backupset sometimes does not match the actual physical file name, so this name is not used. However, when there is time information in the backupset, it is sorted by the backup finish time to take the first one.

Note: The field type in backupset indicates a different backup type, D = database; I = differential database; L = log. (refer to Backupset)

As a result, you can determine the latest full backup file for master!

Select Physical_device_name from msdb.dbo.backupmediafamily where media_set_id = (select TOP (1) media_set_id from Msdb.dbo.backupset WHERE database_name = ' master ' and type = ' D ' ORDER by Backup_finish_date DESC)

To make it easier to manage in SQL Server, copies of files are also executed in the database, requiring system commands to be enabledxp_cmdshell。

exec sp_configure ' show advanced options ', 1reconfigureexec sp_configure ' xp_cmdshell ', 1reconfigure

For offsite inclusion, network mapping needs to be established:

EXEC master.dbo.xp_cmdshell ' net use \\IP\yourPath "password"/user:ip\user '

The final copy script is copied as follows in SQL Server using xp_cmdshell :

DECLARE @OldPath NVARCHAR ($) DECLARE @NewPath NVARCHAR (+) DECLARE @cmdSQL NVARCHAR (+) SET @NewPath = N ' \ \ 192.168.1.111\master\ ' SELECT @OldPath = physical_device_name from msdb.dbo.backupmediafamily WHERE media_set_id = ( SELECT TOP (1) media_set_id from msdb.dbo.backupset WHERE database_name = ' master ' and type = ' D ' ORDER by Backup_finish_dat e DESC) SET @cmdSQL = N ' xcopy "' [Email protected]+ '" "' [email protected]+ '"/y '--select @cmdSQLEXEC MASTER. Dbo. xp_cmdshell @cmdSQL

The command is complete, where do you put it? The next step of the database backup job, of course! ~ Haha o (∩_∩) o talk to yourself ~ ~

Open the Maintenance plan , select the control flow "execute T-SQL statement" task , paste the above statement into the inside, as a system database backup after the next, complete! ~

Take a look! ~ So the backup is faster! ~ (also try a full backup of other databases + differential backup + log backup )

SQL Server Multi-point, timely backup tips