How to backup a MS SQL Express database

As MS SQL Express doesn’t come with a backup facility like MS SQL 2005, I thought I would share my experiences with you on what I use now. The details below will create 7 days worth of db backups.

Create the files:

  1. Create a new folder for your DB backups or chose an existing one.
  2. Open notepad and add the following line:-sqlcmd -S LOCALHOSTSQLEXPRESS -i DB_backup.sql -o output.txt
  3. Save this as DB_backup.bat into your folder from step 1 and close notepad.
  4. Open a new notepad again and add the following line – making sure you change the elements in red to your requirements:-BACKUP DATABASE [YOUR DATABASE NAME] TO  DISK = N’YOUR DB Backup Directory from step 1YOUR DATABASE NAME.mdf’ WITH  RETAINDAYS = 7, NOFORMAT, NOINIT,  NAME = N’YOUR DATABASE NAME-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO
  5. Save this as DB_backup.sql into your folder from step 1.
  6. Now to test double click the DB_backup.bat file and if successful the new databases backup will have run and you will see a new file called ****.mdf (*** = the name of your databases you stated in step 4.)
  7. If you do not see the new file recheck you have replaced all the red elements from step 4, and all your filenames are correct! If in doubt check the output.txt for more information.

Scheduling the backup

  1. To schedule the task everyday then follow a guide like the one below:-http://www.iopus.com/guides/winscheduler.htm

Leave a Comment

Your email address will not be published.

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: