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>Whats done within the sql script (You don't need to care about this - everything is put into the script):
-i <sqlfile>: this sql-file to be executed
-o <logfile>: a log file for stdout redirection
- 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 -
