Automatic backup of database __ Database

Often manual backup is unrealistic, make sure that the system automatically back up regularly.

Backup thought:

0, regular backup, not homework mo.

1, important data should be done every few hours a differential backup; Normal data is done once a week. (Our database is a simple recovery model)

2, the hard disk capacity is limited, the database is very large, can not accommodate too many backup files, using 2 backup file strategy, take turns covering. So there are always only 2 backup files per database.

Backup job:

1, get a single bi-weekly function, to determine the backup file name. Keep only 2 backup files per database

Use [Master]
go

--=============================================
--Author:		leftfist
--Create date:2010
--Description:	used to name the backup file-
-=============================================
CREATE FUNCTION [dbo]. [Jo_func_byweek]
(
)
RETURNS TINYINT
as
BEGIN
	DECLARE @JO INT;
	SET @JO = CONVERT (Int,datename (Ww,getdate ()));
	Return (@JO% 2);
End

2, according to the database name, create a folder with the same name for storing backup files

exec sp_configure ' show advanced options ', 1
go
reconfigure
go
EXEC sp_configure ' xp_cmdshell ', 1
Go
reconfigure
go

DECLARE @folder VARCHAR ();
SET @folder = ' c:\backup\db\ ';
DECLARE @name VARCHAR (m);
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 = 0
BEGIN
	SET @sql = ' mkdir ' + @folder + @name;
	ExEc xp_cmdshell @sql;
    FETCH NEXT from CurT into @name;
End Close
CurT;
Deallocate CurT;

exec sp_configure ' xp_cmdshell ', 0
go
reconfigure
to
exec sp_configure ' show advanced options ', 0< C27/>go
Reconfigure
go

3. Back up all databases (except tempdb)

Use the [master] go

DECLARE @name VARCHAR (m);
DECLARE @sql varchar (1000);
DECLARE @jo TINYINT;
DECLARE @filename NVARCHAR (m);

--[jo_func_byweek] is a custom function, Dan Zhou returns 0, returns 1
SET @jo = [master].dbo.[ Jo_func_byweek] ();
--Backs up 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 = 0
BEGIN
	-Each database is stored in a folder with the same name. The folder is built in advance
	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 = ten, Noformat ';
	EXEC (@sql);    
    FETCH NEXT from CurT into @name;
End Close
CurT;
Deallocate CurT;

Take step 3 as a job and run it regularly, such as early Saturday, to get backup files on a regular basis.

So, how does the job itself back up.

According to my understanding, back up the master, msdb, model, the homework should also be inside. But how to back up the job alone.

Online has a master wrote 2 article introduction, I do not understand, first note the address:

Backup of SQL Server Job (backup job does not back up database)

SQL Server job synchronization (combined with backup job)

Fortunately, the job update frequency is not high, we can completely add, modify the job after a manual backup.

The method is simple: Select the job, right-click the script to write the job.