Rolling out a Server2008R2 box with SQL2008R2 on it to trial some new payroll software. I have ShadowProtect virtual licences for my VM's and this box will get one.

My question is: What is the best practice for backing up this SQL database? Will the ShadowProtect software allow me to restore just the DB or will it be a case of recovering the whole server? I know I can backup the DB manually via the SQL Managment studio if needed, but is there a way to automate this step if needed. I'm guessing some kind of script which goes into the Shadow Protect nightly backup that dismounts the DB, backs it up to a file and then remounts the DB??

| improve this question | | | | |

I know this is a little bit old, but I found this page via Google, so might be useful for anyone else looking. This link gives suggestions from Storage Craft about using Shadow Protect and SQL backups. Some useful tips Maintenance Plans Versus Shadow Protect - The Pros And Cons

As per the comment below, here are a couple of the most useful points from the link :

  • You must identify the volumes that the SQL data and log files reside on and include all these volumes in the same ShadowProtect job. This is counter to their (Storage Craft's) normal recommendation of a job per volume. The other downside with ShadowProtect is that because it is sector based it will not truncate the SQL log file. Therefore it is highly recommended to create a Maintenance Plan that truncates the log file simply by backing up the log file once per day (or week) and keeping one (1) backup. This backup must be performed before the ShadowProtect incremental backups start each day

  • There is a good reason that you should not mix Shadow Protect Backups and "normal" SQL Server Backups through out the day. If you rely upon SQL backups to replay logs to "roll in" and / or "roll out" transactions, you will encounter the issue when a SQL restore requests the next backup. It will be pointing to a ShadowProtect backup but it will not find the BAK file that it is expecting and you will therefore be unable to continue and have an incomplete restore

| improve this answer | | | | |
  • Welcome to Server Fault! Generally we like answers on the site to be able to stand on their own - Links are great, but if that link ever breaks the answer should have enough information to still be helpful. Please consider editing your answer to include more detail. See the FAQ for more info. – slm Jul 24 '13 at 23:33

ShadowProtect takes images of the entire system, its is meant for disaster recovery, not cherry picking files to restore. However, in a pinch, you can mount an image inside of the ShadowProtect Server console installed on an OS and pick through the file system if need be.

Depending on how you are handling snapshots, you have some flexibility on how far back you can go.

| improve this answer | | | | |
  • Cheers Dan, thats how I restore 'lost' data now. Was kinda hoping for a simpler option with SQL. I guess I'll just have to get into the habit of doing manual backups from within the SQL Manager. Appreciate the response ;) – vlannoob Feb 15 '12 at 5:29
  • No problem. Please vote or accept answer if it helped. – DanBig Feb 15 '12 at 18:53
  • There's no need to manually backup the SQL Server databases. This can all be automated from within SQL Server by setting up jobs within SQL Server to do the database backups while the database is online and running without any user impact. – mrdenny Jul 26 '13 at 4:11

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.