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?
Best Answer
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.