SQL Server 2008 Automatic backup (non-maintenance plan)

The database used in a project is SQL Server R2 Express. I did not expect the express version of the features of some restrictions, has not been known before. Baidu Encyclopedia can see its limitations:

"1. Database size limit: SQL Server 2005 Express and SQL Server Express databases have a maximum size limit of 4GB, the latest version of SQL Server R2 Express database has a maximum size limit of 1 0G. This size limit is limited to the data file, and the transaction file is not subject to this limit. 2. Only one CPU can be used to operate, which can be wasteful on multiple CPU computers. 3. The maximum memory capacity can be used is only 1GB. 4. There is no SQL Agent, to do the dispatch service must write its own program. "So, because there is no SQL Agent (SQLServerAgent) service, there is no way to do a database backup by creating a new job." If you do not want to reload the database, you can try the following methods. The idea is as follows: 1. Write a SQL script to make a database backup. For example: Backup.sql, we put it under F:\Database_Backup, and later backup files are placed in this directory.  

GO

DECLARE

@backupTime VARCHAR (a)

DECLARE

@fileName VARCHAR (+)

SELECT

@backupTime = (convert (varchar (8), GETDATE (), +) +REPLACE (convert (varchar (5), GETDATE (), 114), ' : ', ' )

SELECT

@fileName =' F:\Database_Backup\DB_ ' [email protected]+'. Bak '

Backup database DBName to [email protected]

DBName is the name of your database, and the file name of the backup is like Db_201302252140.bak.

2. Write a batch file to execute this SQL script. For example: Backup_database.bat, we also put it under F:\Database_Backup.

1. Sqlcmd-s. -I. F:\Database_Backup\backup.sql

3. Create a new Windows scheduled task that executes the batch command on a regular basis (such as daily). Select "Schedule Task" in the management tool select "Create basic task" Enter Task name and description Select execution time select action as "Launcher" the program or script here chooses the backup_database.bat we built.

Finally, you can determine.

As for deleting outdated backup files, we can also do this through a task scheduler. forfiles Command, you can implement the bulk deletion of files, the use of Baidu itself. We write a batch file Delete_bakfile.batwith the following content:

1. forfiles/p f:\database_backup/m *.bak/d -7/c "cmd/c del/f @path"

Here, we delete the 7-day backup, create a new task schedule, point the program it executes to the bat, and the execution time can be specified as one months or once a week.

SQL Server 2008 Automatic backup (non-maintenance plan)