How to backup SQL Server 2008 Express

I’m facing a lot of situations where customers prefer to use a bunch of free SQL Server 2008 Express or SQL Server 2005 Express Editions - mostly installed by every Client-Server-App they use - instead of using a paid but more powerful SQL Server.  The last time I had to use an Express Edition and implemented the backup for the databases I took the time to review my SQL backup script.

 

How to backup SQL Server 2008 (Express Edition) using the command line - the whole script is downloadable below:

• Adjust the parameters within the sql file:

• @BACKUP_PATH = <Backup target path> - the path where the sql server creates the media set for the backup
• @MEDIASET_PREFIX = <Backup filename prefix> - Used to give backups meaningful names. The files get automatically a timestamp attached.
• @RETENTION = <number of backups to keep> - Specifies the of backup sets to keep.

• Call sqlcmd from a command line or trigger a batch job calling sqlcmd and pass through the sql script doing the backup for you:

sqlcmd -i <sqlfile> -o <logfile>

-i <sqlfile>: this sql-file to be executed
-o <logfile>: a log file for stdout redirection

Whats done within the sql script (You don't need to care about this - everything is put into the script):

• Create a media set

EXEC sp_addumpdevice @devtype, @logicalname,@physname

@devtype: In our case set to 'disk' as we do a backup to disk not to tape.
@logicalname: the name of the mediaset created using the defined @MEDIASET_PREFIX and the current timestamp
@physname: the path and filename of the mediaset, simply the backup file

• Iterarte through all online databases - offline databases cannot be backed up and do a backup to the created media set

DECLARE databaseNamesCursor CURSOR FOR
    SELECT 
        name
    FROM
        master.dbo.sysdatabases
    WHERE
        name != 'tempdb'
        AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN databaseNamesCursor;

FETCH NEXT FROM databaseNamesCursor INTO @databaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    /* create a backup of the current database*/
    BACKUP DATABASE @databaseName TO @mediaset;
    FETCH NEXT FROM databaseNamesCursor INTO @databaseName;
END

CLOSE databaseNamesCursor;
DEALLOCATE databaseNamesCursor;

• Delete not needed backups 

sp_dropdevice @logicalname, @delfile='DELFILE'

@logicalname: the name of the mediaset to delete
@delfile: if set to 'delfile' the physical file behind the mediaset will be deleted

Download the files: 

- link broken, suggest  you send me an email if you need the scripts -