SQL Server Express automated backup and deletion of backup files

Requirements Analysis: Because the company to comply with the license of genuine software, in line with the fine tradition of cost-saving, so did not use any pirated software, with Microsoft's SQL Server Express. Because of the importance of data, it is necessary to backup the database every day, but also deleted two weeks before the backup data, access to information, came to the following set of plans, but also hope that the road heroes pointing twos:

The current environment is a server that installs SQL Server Express and the Microsoft SQL Server Management Studio Express graphical management tool.

Now create the following two folders under C-disk: C:\Script and c:\db_backup,script are used to store script files, and db_backup to store backup data.

1, the C:\Script folder has the following three files: Schedule.cmd,deletedb.vbs and Backupdb.sql

2, Backupdb.sql has the following content:

DECLARE @dateString Char (@dayStr char (2), @monthStr char (2), @hourStr char (2), @minStr char (2)

--Define the month variable

IF (SELECT LEN (CAST (MONTH (GETDATE ()) as CHAR (2)) =2

SET @monthSTR =cast (MONTH (GETDATE ()) as CHAR (2))

ELSE

SET @monthSTR = ' 0 ' + CAST (MONTH (GETDATE ()) as CHAR (2))

--Define the day variable

IF (SELECT LEN (CAST (GETDATE () as CHAR (2))) =2

SET @daySTR =cast (Day (GETDATE ()) as CHAR (2))

ELSE

SET @daySTR = ' 0 ' + CAST (Day (GETDATE ()) as CHAR (2))

--Define the hour variable

IF (SELECT LEN (DATEPART (hh, GETDATE ())) =2

SET @hourStr =cast (DATEPART (hh, GETDATE ()) as CHAR (2))

ELSE

SET @hourStr = ' 0 ' + CAST (DATEPART (hh, GETDATE ()) as CHAR (2))

--Define a variable

IF (SELECT LEN DATEPART (MI, GETDATE ())) =2

SET @minStr =cast (DATEPART (MI, GETDATE ()) as CHAR (2))

ELSE

SET @minStr = ' 0 ' + CAST (DATEPART (MI, GETDATE ()) as CHAR (2))

--Defines a variable based on the current timestamp

SET @dateString =cast (GETDATE () as CHAR (4)) + @monthStr + @dayStr + @hourStr + @minStr

--=================================================================

DECLARE @IDENT INT, @sql VARCHAR (1000), @DBNAME VARCHAR (200)

SELECT @IDENT =min (database_id) from SYS. DATABASES WHERE [database_id] > 0 and NAME = ' master '

BEGIN

SELECT @DBNAME = NAME from SYS. DATABASES WHERE database_id = @IDENT

SELECT @SQL = ' BACKUP DATABASE ' + @DBNAME + ' to DISK = ' D:\SQLTEST\ ' + @DBNAME + ' _db_ ' + @dateString + '. BAK ' with INIT '

EXEC (@SQL)

SELECT @IDENT =min (database_id) from SYS. DATABASES WHERE [database_id] > 0 and database_id> @IDENT and NAME not in (' TEMPDB ')

End

3, under the Deletedb.vbs has the following content:

On Error Resume Next

Dim FSO, folder, files, Sfolder, Sfoldertarget

Set fso = CreateObject ("Scripting.FileSystemObject")

' Save the database backup file path

Sfolder = "D:\SQLTEST\"

Set folder = fso. GetFolder (Sfolder)

Set files = folder. Files

' Used to write a text file and generate a delete database backup report

Const ForAppending = 8

' Create an empty TXT file under scripts: Log.txt

Set objfile = fso. OpenTextFile (Sfolder & "\log.txt", ForAppending)

Objfile.write "================================================================" &

VBCRLF & VBCRLF

Objfile.write "Database Documentation Report" & VBCRLF

Objfile.write "Date:" & FormatDateTime (Now (), 1) & "" &

VBCRLF

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Objfile.write "Time:" & FormatDateTime (Now (), 3) & "" &

VBCRLF & VBCRLF

Objfile.write "================================================================" &

VBCRLF

' Enumerate backup file directory files

For each itemfiles in files

' Get the filename of the file you want to delete

A=sfolder & Itemfiles.name

' Get file name extension

b = fso. Getextensionname (a)

' Check if the extension is Bak

If UCase (b) = "BAK" Then

' Check if the database backup is 14 days ago

If DateDiff ("D", Itemfiles.datecreated,now ()) >= Then

' Delete old backup

Fso. DeleteFile A

objFile.WriteLine "Backup file deleted:" & A

End If

End If

Next

objFile.WriteLine "================================================================" &

VBCRLF & VBCRLF

Objfile.close

Set objfile = Nothing

Set FSO = Nothing

Set folder = Nothing

Set files = Nothing

4. Create a text file under D:\SQLTEST\: Log.txt, for saving delete log

5, Schedule.cmd has the following content:

CD C:\Program Files\Microsoft SQL Server\90\tools\binn\

Sqlcmd-s. \SQLEXPRESS-E-i "D:\SQLTEST\BackupDB.sql"

C:\Script\ Deletedb.vbs

6, the following as long as the scheduled task to set the scheduled run Schedule.cmd command.

Reference article: http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/