User Tools

Site Tools


database:mssql:express_backups

MSSQL Express Backups

See here for instruction:
http://blogs.orcsweb.com/jeremy/archive/2006/11/06/Automating-Database-Backups-with-Microsoft-SQL-Server-2005-Express-Edition.aspx

Alternatively use the open source expressMaint.exe utility which mimics some of the behavior of the MS sqlmaint utility.

Ho-To

  1. Create a new user named “backupadmin” for your database
  2. Map the user to your databases you want to backup with the role “db_backupoperator”
  3. Go to one of the databases ⇒ “Tasks” ⇒ “Backup”
  4. Enter your settings and press Ctr+Shift+F to save as a slq script “D:\admin\db_backup_job.sql”
  5. Edit the script to add other databases
  6. Run the script with sqlcmd as a scheduled task:

sqlcmd batch script

sqlcmd utility can be found under “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE”
@echo off 
sqlcmd -S EUW0001302\SQLEXPRESS -U backupadmin -P <Password> -i "D:\admin\db_backup_job.sql" -o "D:\admin\db_backup.log"

sql backup script

Example SQL Script:

/* 
   Nik Wolfgramm | 29.02.2008
 
   script to create a full backup of MSSQL Express Databases
   variable "filename" is used to set the date into the filename
*/
 
DECLARE @filename VARCHAR(100)
 
SET @filename = 'D:\admin\db_backups\Tourbillon-Full-'+CONVERT(CHAR(8),GETDATE(),10)+'.bak'
BACKUP DATABASE [Tourbillon] 
  TO DISK = @filename
  WITH NOFORMAT, NOINIT, 
  NAME = N'Tourbillon - Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 
 
SET @filename = 'D:\admin\db_backups\example-Full-'+CONVERT(CHAR(8),GETDATE(),10)+'.bak'
BACKUP DATABASE [example] 
  TO DISK = @filename
  WITH NOFORMAT, NOINIT, 
  NAME = N'example - Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 
GO
/srv/wiki.niwos.com/data/pages/database/mssql/express_backups.txt · Last modified: 2009/08/15 12:14 (external edit)