SQL Server Express Edition enables automatic backup and automatic deletion of backups

Source: Internet
Author: User
Tags microsoft sql server sql server express

1. View the SQL version:

--You can see Express Edition Lite free version

Microsoft SQL Server R2 (SP2)-10.50.4000.0 (X64) June, 08:36:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601:service Pack 1) (Hypervisor)

2. SQL Server version R2 Express does not have automatic backup function, so it is necessary to manually write and add a scheduled task to the system to execute

3. Create a directory under the partition where you want to store the backup, such as D:\databackup. Name the script Jfsuqian.sql and put it under D:\databackup, and here's the script content:

--print ' Start backup of the SU Home database ' GO  DECLARE  @backupTime varchar-  -Backup time DECLARE  @fileName varchar    - -Backup Path Select  @backupTime = (CONVERT (varchar (8), GETDATE (), +replace) (CONVERT (varchar (5), GETDATE (), 114), ': ', ' )   SELECT @fileName = ' D:\databackup\JFSuQian_ ' [email protected]+ '. Bak ' backup database name to [email protected]-- print ' Start backup of su Jia database '

4. Create a BAT file, set up a txt text file on the desktop, enter the following content, and then change the txt text file to Jfsuqian.bat, also put in the D:\databackup directory

CD C:\Program Files\Microsoft SQL Server\100\tools\binn  sqlcmd-s.-I D:\databackup\JPKFSuQian.sql
Explain:
First line: is the installation path of your SQL Server

The second line: The SQL script path that needs to be executed, which is the backup script we wrote earlier

5. Create a bat file to delete the backup, as in the fourth step, create a txt text file on the desktop, enter the following, then rename the txt file to Del_4_bak.bat and put it in D:\databackup

forfiles/p c:\sqldataback\jpkf_data_backup/m *.bak/d -4/c "cmd/c del/f @path"

Explanation:/p searches for files to manipulate under this path

/M Search mask. Default matches All

/d Filters files using date criteria. The default action is +. Default unit is days

The cmd command to be executed by/C must be caused by "" and its default command is "cmd/c echo @file" [Return file name]

6. Now that the preparation is complete, create a scheduled task and perform the daily

Control Panel → top Right corner (small icon) → Administrative tools → task Scheduler (), followed by no longer described:

SQL Server Express Edition enables automatic backup and automatic deletion of backups

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.