Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed a Microsoft SQL Server 2012 on a server that also are used as a file server. Each day the server is automatic backed up with Acronis Backup & Recovery to a USB-disc that we switch every day.

My question is whether it is enough to make a system backup, or if I also need to do a SQL Server Backup. Are there any disadvantages to doing like this?

share|improve this question

3 Answers 3

No, you cannot rely on system or NTFS backups as an appropriate disaster recovery strategy for SQL Server. If all your current solution is doing is backing up the database files (i.e. mdf, ldf, ndf) and if you needed to revert to those "backups", then you could potentially run into a horrific situation where SQL Server can't use those database files.

You need to ensure that you're taking SQL Server backups (full, differential, transaction log [unless you're in simple recovery]). This will provide you with the correct way to recover from disaster in the event you need to do a restore.

A few notes:

  1. Store your backup files separately from the server in order to not have a single point of failure

  2. You need to test your backed up databases for corruption(i.e. restore database, run DBCC CHECKDB against restored database). If you have been backing up a corrupt database unknowingly for 3 weeks, and your backup retention is only 2 weeks, then you could have only a corrupt database that has been backed up

share|improve this answer
    
+1 for appropriate use of bold text! –  Matt Sep 16 '13 at 13:26

Unless you are running all of your databases in simple recovery mode, you are eventually going to run out of transaction log space. Read some good write-ups about transaction log management.

As transaction log management is fundamental a topic of managing an Sql Server, I'd recommend you to pick up a book about the subject too. For example, Microsoft Press training kit for 70-642 examn is decent an introduction.

The conventional wisdom about backups is to use Ola Hallengren's scripts. Both system and user databases need to be backed up. Use Acronis or whatever to back up the DB backups to secure a location.

share|improve this answer

Just tell me that you don't have databases on the system drive. :)

Seriously, what they said. If your databases aren't in simple recovery, they must have transaction log backups or the log will grow until it eventually consumes the disk. (Which is why I said "not the system disk." One of my lesser colleagues once installed software, including SQL databases, on the system drive, and it ate Windows.)

Also, there's no guarantee that those .mdf, .ndf, and .ldf files will attach off your backup, especially if there's any kind of time difference between when they were backed up. (They might, but it's not supported, and they might not.)

So... what Thomas Stringer and vonPryz said. Back up through SQL. Run DBCC CHECKDB. (Reindex, even.) Back up the backup files with Acronis.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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