Your basket is currently empty

SQL 2005 Express Dababase Backup Script
SQL Server 2005 Express supports backing up a database, however, it doesn't have the SQL Server Agent which allows you to create a automated backup plan.

It's possible to backup a database using a T-SQL script, using Windows Task Schedule to automate execution of the script.
  1. Download the T-SQL SP and run against your database
  2. Create a folder on the local disk of the machine running SQL Server called 'Scripts', i.e. 'C:\Scripts'. You don't have to create a 'Scripts' folder - it just makes it easier to follow our example!
  3. Create a file called fullbackup.sql in the Scripts folder containing the following SQL statement:
    4. exec expressmaint
    @database = '$(DB)',
    @optype = 'DB',
    @backupfldr = '$(BACKUPFOLDER)',
    @reportfldr = 'C:\Databases\Reports',
    @verify = 1,
    @dbretainunit = '$(DBRETAINUNIT)',
    @dbretainval = '$(DBRETAINVAL)',
    @rptretainunit = 'copies',
    @rptretainval = 2,
    @report = 1
  4. Create another file called fullbackup.bat and add the line below. This is the .bat file you will execute using scheduled tasks:
    5. sqlcmd -S .\SQLExpress -i"C:\Scripts\fullbackup.sql" -v DB="ALL_USER" -v BACKUPFOLDER="C:\Databases" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"
Obviously, change the paths above to where you would like to backups to be created. In the above example the database backups get placed in a folder 'C:\Databases' and the backup report in 'C:\Databases\Reports'.

We also use the excellent application 7-Zip file archiver to compress the backups ready for exporting to a backup server using FTP. See below for an example of how to use this progam.
C:\"Program Files"\7-zip\7z.exe a -tzip C:\SQL_Backups\backup-zip\backupdb.zip C:\SQL_Backups\*.* -pmypassword

Published Tuesday, August 15, 2006