[TIPS] Automated Backup for DB SQL Express (zip included)

[icemax]

hello Friends

SQL Server Express is distributed without SQL Agent, so it is not possible to schedule a SQL Server internally automated processes, such as backup. E ', however, can get around this by creating an ad-hoc script, which take charge of the job we want to execute.

In this article we will see how to create a script to back up SQL Express, and soon after, one for backup maintenance, whose task will be to keep the three most recent copies, each time deleting the older ones.
All work will be carried out inside the folder C: \ SQL_Backup and two sub folders and scripts Logs, which will have to be created in advance.

At the end of the article there is a link to download a package ready with all the scripts and the structure of the default file

The script for backing up SQL Express

Let us go inside C: \ SQL_Backup \ scripts and create a file that will call backupDB.sql with this content:

Code: [Select]

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
--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))
--day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))
--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))
--minute 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))
--name variable based on time stamp
SET @dateString=CAST(YEAR(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 NOT IN ('TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQL_Backup\'+@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
The script will create as many as the number of database backup in SQL Express, and add a "timestamp" at the end of the name (see highlighted line), in order to identify them in time, before ending with the extension . BAK.


Deleting old backups

Always within C: \ SQL_Backup \ scripts create a file in which deleteoldsqlbaks.vbs write:

Code: [Select]

On Error Resume Next 
Dim fso, folder, files, sFolder, sFolderTarget   
Set fso = CreateObject("Scripting.FileSystemObject") 

'location of the database backup files
sFolder = "C:\SQL_Backup\"

Set folder = fso.GetFolder(sFolder) 
Set files = folder.Files   

'used for writing to textfile - generate report on database backups deleted
Const ForAppending = 8

'you need to create a folder named ìscriptsî for ease of file management & 
'a file inside it named ìLOG.txtî for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & "\Logs\cleanuplog.txt", ForAppending)

objFile.Write "================================================================" & VBCRLF & VBCRLF
objFile.Write "                     DATABASE BACKUP FILE REPORT                " & VBCRLF
objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
objFile.Write "================================================================" & VBCRLF 

'iterate thru each of the files in the database backup folder
For Each itemFiles In files 
   'retrieve complete path of file for the DeleteFile method and to extract 
        'file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   'retrieve file extension 
   b = fso.GetExtensionName(a)
       'check if the file extension is BAK
       If uCase(b)="BAK" Then

           'check if the database backups are older than 3 days
           If DateDiff("d",itemFiles.DateCreated,Now()) >= 3 Then

               'Delete any old BACKUP files to cleanup folder
               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
The task of this script is to navigate to the folder where you have saved the backup (line 6), "count" the backups and keep the three most recent, deleting others (line 36). Also writes a log (line 16) of the operations performed.

The log files

Let us go to C: \ SQL_Backup \ Logs folder and create two empty files, called backuplog.txt and cleanuplog.txt

Execution of the script

We create the file databasebackup.cmd

Code: [Select]

@ echo off
REM Launch the backup and write the log
sqlcmd -S SERVER\SQLEXPRESS -E -i C:\SQL_Backup\scripts\backupDB.sql -o C:\SQL_Backup\Logs\backuplog.txt

REM Launch script cleaning and updating cleanuplog.txt
C:\SQL_Backup\scripts\deleteoldsqlbaks.vbs

The only change to do is enter the name and instance of your server (highlighted line), replacing SERVER \ SQLEXPRESS with your correct data (presumably, the instance is called SQLEXPRESS but always change the name of the server)
scheduling

You just have to create a Scheduled Task from the Control Panel of Windows, whose simple task based on the performance of C: \ SQL_Backup \ scripts \ databasebackup.cmd, setting a time and a recurrence according to your needs



thanks

Massimo

I've tested it and it works perfectly

[emre]

Great article and useful scripts. Thank you!

[icemax]

thank emre and thank to alls

SambaPOS is very very fantastic

[Alexey]

Hi,

You can schedule backup jobs with SQLBackupAndFTP software (full, differential and transaction log backups), save backups at local folders, FTP, Dropbox, Google Drive, Amazon S3, delete old backups and configure email notifications... Basic features are available in free version or you can try all features in trial mode.

[evail]

Alexey

Thats a great find!

Thanks