Share SQL Server R2 data backup, synchronize server data (i. Local backup)

A recent deployment of a system, in the data security considerations, so the corresponding database server scheduled backup, and data synchronization to the backup service. Before in another project also did the corresponding operation, but the operation is to follow the found article step-by-step operation, encountered some details of the problem is not recorded, so in this deployment process encountered the same problem when the pace of chaos, delayed a lot of time

To avoid the next need to deal with the same situation can increase their efficiency, special record of these procedures

The primary backup processing is:

1.Sql Server Agent --Jobs

Script to back up the database file to a local by calling the SQL-backed backup service

2. Replication--Local subscription local publishing

Synchronizing the database to the backup server, taking into account the loss of data in the event of a system collapse

SQL Server Agent Job First step

Open SQL Server, connect to the Object Server, in order to avoid the subsequent operation of some other operational permissions issues, here when connecting to the server, the server name select the calculator name, authentication select Windows Authentication

Step Two

Open SQL Server Agent, if you do not start the agent service when you install SQL Server, you need to start the SQL Server Agent service, select New job, fill out the job name

Step Three

Create a new job step, and you can create more than one step if you need to. Add command, RAR compression is used here, so you need to add a compressed file to the corresponding directory, if not the execution of the job will fail.

 Use  [Topevery]; Declare @filename varchar(Ten)Set @filename = CONVERT(varchar(8),GETDATE(), the )Declare @path varchar( $)Set @path='C:\bakdb\'+@filename+'. bak'Backup Database [Topevery]   to Disk=@path  withNoinitDeclare @cabpath varchar( $)Set @cabpath='C:\bakdb\'+substring(@filename,1,Ten)+'. rar'Print @cabpathDeclare @cmdRar varchar( $)Set @cmdRar = 'C:\rar.exe A-ag'+@cabpath+' '+@pathexecxp_cmdshell@cmdRar, No_outputDeclare @cmdDel varchar( $)Set @cmdDel = 'del'+@pathexecxp_cmdshell@cmdDel, no_output

If you do not start xp_cmdshell, you need to execute the following script

' Show advanced Options ',1reconfigurego'xp_cmdshell', 1 Reconfigure Go

Fourth Step

New job schedule, mainly set the work cycle of the job, you can set the detailed operation parameters, there are other settings, such as alerts, notifications, the target is not applied to this time is not recorded.

Fifth Step

Test results, select the corresponding new job selection job start step, if successful will be in the corresponding file directory to generate the corresponding backup file, failure will need to check the error log failure reasons.

5.1 Here at the time of operation because of the script filling error when going to modify the steps, the following error occurred on the network to find the document, the main COM component problem. Copy a solution here and put it in the text.

32-bit operating system:

Open run (Command prompt),

One, enter the CD C:\Windows\System32 into the C:\Windows\System32 path

Ii. input regsvr32 "C:\Program Files\Microsoft SQL Server\100\dts\binn\dts.dll"

64-bit operating system:

Open run (Command prompt),

One, enter the CD c:\windows\syswow64 into the C:\WINDOWS\SYSWOW64 path

Ii. input regsvr32 "C:\Program Files (x86) \microsoft SQL Server\100\dts\binn\dts.dll"

If it fails, the run is opened as an administrator.

Finally, restart the SQL Server Agent service

Share SQL Server R2 data backup, synchronize server data (i. Local backup)