Automatic Database Backup

Manual backup is often unrealistic and must be automatically and periodically backed up by the system. Backup idea: 0. Regular backup, which is not a job. 1. Differential backup should be performed for important data every few hours; regular data should be backed up once a week. (Our database is in simple recovery mode) 2. The hard disk capacity is limited, and the database is large, so it cannot accommodate too many backup files.

Manual backup is often unrealistic and must be automatically and periodically backed up by the system. Backup idea: 0. Regular backup, which is not a job. 1. Differential backup should be performed for important data every few hours; regular data should be backed up once a week. (Our database is in simple recovery mode) 2. The hard disk capacity is limited, and the database is large, so it cannot accommodate too many backup files.

Manual backup is often unrealistic and must be automatically and periodically backed up by the system.

Backup ideas:

0. Regular backup, not for jobs.

1. Differential backup should be performed for important data every few hours; regular data should be backed up once a week. (Our database is in simple recovery mode)

2. the hard disk capacity is limited, and the database is very large. It cannot accommodate too many backup files. The two backup files are used in turn to overwrite them. Therefore, each database always has only two backup files.

Backup job:

1. Obtain the single-week function to determine the name of the backup file. Keep each database with only two backup files

USE [Master] GO -- ======================================== ============ -- Author: leftfist -- Create date: 2010 -- Description: used to name the backup file -- ========================================== ============= create function [dbo]. [jo_func_byWeek] () returns tinyintasbegindeclare @ jo int; SET @ JO = CONVERT (INT, DATENAME (ww, GETDATE (); RETURN (@ JO % 2); END

2. Create a folder with the same name based on the database name to store backup files.

EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'xp_cmdshell', 1GORECONFIGUREGODECLARE @folder VARCHAR(300);SET @folder = 'c:\backup\db\';DECLARE @name VARCHAR(50);DECLARE @sql VARCHAR(1000);DECLARE curT CURSOR FOR SELECT name FROM sys.databases where database_id<>2;OPEN curT;FETCH NEXT FROM curT INTO @name;WHILE @@FETCH_STATUS = 0BEGINSET @sql = 'mkdir ' + @folder + @name;ExEc xp_cmdshell @sql;    FETCH NEXT FROM curT INTO @name;ENDCLOSE curT;DEALLOCATE curT;EXEC sp_configure 'xp_cmdshell', 0GORECONFIGUREGOEXEC sp_configure 'show advanced options', 0GORECONFIGUREGO

3. Back up all databases (excluding tempdb)

USE [master] GODECLARE @ name VARCHAR (50); declare @ SQL varchar (1000); DECLARE @ jo TINYINT; DECLARE @ filename NVARCHAR (100 ); -- [jo_func_byWeek] is a user-defined function. 0 is returned for one week, and 1 SET @ jo = [Master] is returned for two weeks. dbo. [jo_func_byWeek] (); -- backup all databases (except tempdb) DECLARE curT cursor for select name FROM sys. databases where database_id <> 2 OPEN curT; fetch next from curT INTO @ NAME; WHILE @ FETCH_STATUS = 0BEGIN -- each database is stored in a folder with the same NAME. SET @ filename = @ name + '\' + @ name + '_' + CAST (@ jo as char (1) + '. bak '; SET @ SQL = 'backup DATABASE [' + @ name + '] TO DISK = ''C: \ backup \ db \ '+ @ filename + ''' with init, NAME = n''' + @ name + ''', NOSKIP, STATS = 10, noformat '; EXEC (@ SQL); FETCH NEXT FROM curT INTO @ name; ENDCLOSE curT; DEALLOCATE curT;

Make step 3 into a job and run it regularly. For example, you can get the backup file on a regular basis in the early morning of Saturday.

So how to back up the job itself?

According to my understanding, after the Master, msdb, and model are backed up, the job should also be in it. But how to back up a job separately?

I have written two articles on the Internet. I don't know much about it. Write down the address first:

Backup of SQL Server jobs (backup jobs are not backup databases)

SQL Server Job synchronization (combined with backup job)

Fortunately, the update frequency of jobs is not high. We can manually back up a copy after adding or modifying jobs.

The method is simple: Right-click a job and choose to write a job script.