i had to create a few batch files a couple of years ago to back up sql 2005 express databases. sql express does not allow you to set up maintenance plans like you can in sql standard/enterprise.

database backup:
del c:\backups\*.bak
osql -E -S <server name\instance name> -i <database>.sql

you’ll need to create the .sql file, here’s an example:
BACKUP DATABASE [<database>] TO [Backup-<database>] WITH NOFORMAT, NOINIT,  NAME = N'<database>-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

and here’s an integrity check with osql:
osql -E -S <server name\instance name> -Q “DBCC CHECKDB(‘<database>’)” -o output.txt

i’ve lost my original source for the .sql syntax and i can’t remember the reasoning for specifying so many options. from a quick google here’s another source:
http://metrix.fcny.org/wiki/display/dev/Using+OSQL+to+backup+and+restore+a+database

osql command reference:
http://technet.microsoft.com/en-us/library/aa214012(v=sql.80).aspx

Comments are closed.