SQL Server creates a backup schedule

For a backup plan, Microsoft provides a set of features in SQL Server, and the Maintenance Plans Wizard enables you to perform related maintenance work on the database.

Through the wizard, you can perform such as regular backup and cleanup work.

The prerequisite is that the installation media contains features that are maintained by SQL Server and that the SQL Server Agent service starts normally. In the Express version, the Maintenance plan feature set is not available.

For the express version, we can implement the backup operation through the Windows Task Scheduler.

First we write a bunch of SQL statements to use as a backup cleanup plan.

1 --bk--2 Declare @filename varchar(255)3 Set @filename =N'C:\BACKUP\test_' + cast(DatePart(yyyy,getdate()) as varchar)4 + cast(DatePart(MM,getdate()) as varchar)+ cast(DatePart(DD,getdate()) as varchar)+ '. bak'5 6 Backup Database[Test]  to Disk = @filename  withInit7 Go8 9 --Delete bk--Ten Declare @date datetime One Set @date = getdate()- 3 A  - ExecuteMaster.dbo.xp_delete_file0N'C:\BACKUP'N'Bak',@date,1 - Go

The above SQL creates a backup of the test database and retains the backup record for 3 days.

The above uses a system stored procedure xp_delete_file. Describes this stored procedure related parameters:

1. File Type = 0 for backup files or 1 for report files.

2. Folder Path = The folder to delete files. The path must end with a backslash "\".

3. File Extension = This could is ' BAK ' or ' TRN ' or whatever you normally use.

4. Date = The cutoff date for who files need to is deleted.

5. subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

OK, after creating the SQL, you need to write a bat again. Called to the sqlcmd command, the relevant parameters can indicate that you can look at the help document. The following represents the input SQL script and the Spit execution log.

1 sqlcmd-i "C:\backup\job.sql"-O "C:\backup\err.txt"

You can perform a bat script as a task and set the execution time period by using the Task Scheduler tool in Windows Accessories.

SQL Server creates a backup schedule