SQL Server multi-point timely backup techniques, SQL Server multi-point backup

SQL Server multi-point timely backup techniques, SQL Server multi-point backup

To ensure database security, we plan database disaster recovery policies, includingLocal backup, remote backup, raid, Or useHigh Availability(Such as log transmission, mirroring, and replication) for Remote Disaster Tolerance. Since there is only one backup policy (for example, full-differential-log) for SqlServer database backup, only one full policy is created for a database. Do not create multiple backup policies, otherwise, the backup chain cannot be connected, and each backup is on one side. GenerallyFull backup + differential backup + Log backup, OrComplete backup + differential backup + Log TransmissionBut the backup only has one file. In order TO store the same backup in different regions, SqlServer has several parameters that can be set TO save in multiple places, such as mirror to, COPY_ONLY, but only for full backup. In all other cases, the windows command is used to copy to other places for redundant storage.

Previously, I used a method to copy data from N days to other places (refer to forfiles and xcopy to copy files that have been changed in N days in windows ), however, it is not timely to use the windows job COPY method. Here we will introduce a method to store files in a remote network path in a timely manner, that is, copy the files to a remote location immediately after the database backup is complete.

There are many backup files in a folder. How can I filter the files just backed up?

Using windows Command batch processing should be completed, but it will be too troublesome. Find it in SqlServer !~

Each backup in SqlServer has a record. The backup name, path, and time wait can all be found in the backup history.

Take the complete backup of the master as an example:

SELECT TOP(1) media_set_id ,nameFROM msdb.dbo.backupset WHERE database_name = 'master' and type = 'D'ORDER BY backup_finish_date DESCSELECT physical_device_name FROM msdb.dbo.backupmediafamily WHERE media_set_id = 2048

These backups are automatically backed up according to the maintenance plan. Each backup set has only one backup file, which can uniquely identify one file !~

By the way, the name in the backupset sometimes does not match the actual physical file name. However, if there is time information in the backupset, the first latest one is obtained based on the backup completion time.

Note: The type field in backupset indicates different Backup Types, D = database; I = Differential database; L = log. (Refer to backupset)

Therefore, you can confirm the latest full backup file of the 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 files in SqlServer and to copy files in the database, you must enable the system command xp_mongoshell.

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

For remote scenarios, you need to establish a network ing:

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

The final copy script is as follows:

DECLARE @OldPath NVARCHAR(200)DECLARE @NewPath NVARCHAR(100)DECLARE @cmdSQL NVARCHAR(300)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_date DESC)SET @cmdSQL = N'xcopy "'+@OldPath+'" "'+@NewPath+'" /y '--SELECT @cmdSQLEXEC MASTER.DBO.XP_CMDSHELL @cmdSQL

After the command is completed, where can I execute it? Of course it is the next step in the Database Backup job !~ Haha O (∩ _ ∩) O self-talking ~~

OpenMaintenance Plan, Select control flowTasks that execute T-SQL statements, Paste the above statement into it, as the next step after the system database backup, complete !~

Run the command !~ This makes backup faster !~ (Try other databases for full backup + differential backup + Log backup)