I need to set up on my server automated daily SQL db backups (sql express, so no maintenance plans).

To keep things simple I'm gonna use a backup solution (JungleDisk) that uses VSS to back up the DB file. SQL fully supports VSS and on requests freezes DB I/O, so I understand I'm taking snapshots.

JungleDisk supports doing differential back up and compression, so it simplifies things and keeps the cost/bandwidth down.

Is it enough to just backup up db file (mdf). Do I need to back up transaction log (ldf) file as well? I'm ok with losing a day's worth of work (since the last backup).

if I go this route, what's the best way to restore the database?

are there any issues with this approach I'm not aware of?

| improve this question | | | | |

If you did want to go down this road, you would need to back up both the .mdf and .ldf files. However, I have been told that while a VSS backup does technically work, there is the potential for database corruption using this method since the database isn't aware of the VSS backup and cannot ensure that everything is committed before the backup.

What I have done for SQL Express boxes is to perform a SQL backup with a T-SQL script executed by Windows Task Scheduler using the SQLCMD command-line interface. (If you're not familiar with the BACKUP syntax, you can setup the backup using the GUI and then click the Script button to create the script.) If you have other SQL Servers that do have maintenance plans, you can also use those servers to run maintenance plans against the SQL Express boxes.

| improve this answer | | | | |
  • thanks. I eventually came to the same conclusion, plus with T-SQL backup up I can properly do differential and log backups. – Sumrak Dec 23 '09 at 1:13
  • 2
    btw, my understanding is that SQL VSS Writer is the service that makes database aware of the backup that takes place. if there's any corruption, it's probably due to the bug or misconfiguration? – Sumrak Dec 31 '09 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.