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/