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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm new one with SQL Server, and I have the following question:

My database is not big, about 1 GB, and I make regular backups with the help of third party tool. How can I be sure that if a failure occurs I will be able to restore my database with the minimal loss?

share|improve this question
2  
A good practice I learned the hard way is: not only plan a back up but plan how to recover and that also means you can take some time to drill your skills recovering a DB from the backups from a simulated disaster. – jean Feb 18 at 13:24

The only way to be 100% certain is to restore the backups. If you are restoring to the same SQL Server instance then you will want to restore with a new database name and make use of the MOVE restore syntax to restore the .mdf and .ldf files to a different directory.

Alternatively, you can restore to a separate SQL server instance with the same database name. Again you will need to consider if the MOVE syntax is relevant.

See Retore SQL Server database to different filenames and locations for help.

For added piece of mind, you should create all your backups with checksum and after validate the metadata consistency with a RESTORE VERIFYONLY check. The restore verify can be automated by using using Ola Hallengrens excellent backup solution SQL Server Maintenance Solution

share|improve this answer
6  
"If you haven't tried restoring your backup, you don't have a backup" - Anon – icc97 Feb 18 at 3:51

Short answer: test your backups, and validate the backup frequency.

It's difficult to write a long enough answer to properly address this topic; I'll explain a little on my short answer and give you some links to more information.

Testing backups is extremely important; merely taking backups will not ensure that they're useful. Most DBAs will periodically restore the backups to another database or another server - for practice and to test the validity of the backup.

The frequency of backups is important to address "minimal loss" - it depends on how frequently the database is used, and more importantly, how much data loss is acceptable. If the users/analysts can lose an entire day of data and be okay with re-entering it, you only need daily backups. If they can only lose 5 minutes, you need to be taking log backups every 5 minutes to satisfy that requirement. Communication with the database stakeholders is key to identifying a proper backup strategy. The other thing they can tell you is how long ago they will need to restore. If there are occasionally mistakes they need "rolled back" up to 3 months ago, your backup plan needs to keep backups around for 90 days so you're able to pull from those.

Check out Brent Ozar's site for a more complete discussion of backup practices and why and when to employ them.

share|improve this answer

How can I be sure that if a failure occurs I will be able to restore my database with the minimal loss?

Well this totally depends upon the Recover Point Objective (RPO) and Recovery Time Objective (RTO) as defined under business standards.So you need to have those details handy.

RPO here will be the data loss business willing to loose, mostly it will be very minimal. But lets say 15 mins ( in our case), so we have full backups followed by differentials and log backups every 10-15 mins (depending upon how critical the DB is).Therefore its very important that you should have a proper backup strategy in place in case there is a disaster and you need to recover back.

Next, concluding from above brings us to RTO, which is the time you take to bring up the systems, or bring system back to healthy state, by performing those chains of operation from FULL, diff to log backups, depending upon the scenario.Therefore its important that you should have proper testing done to gather that time, not regularly but proactively to answer the business on RTO. Again this is not technical but good to know as backup strategy.

Then followed by automating most of you're backup and restore, which should be easy in you're case as you are using third party tool.Also, you need to test and validate that your backups are working and that you can successfully restore them.

Some tips on testing here.

Just to add, in case backups are going to tape, it would be good practice to pull them up and check for healthy restore to validate the backups sent to tape. Just to make sure on a safer side.

share|improve this answer

You should RESTORE your backups to another SQL Server instance to check that the backups are OK and that they can be restored. Doing this everyday will soon become a pain so automating the process with a SQL Agent job or some PowerShell would be ideal.

This restored version of your database also gives you a great place to run consistency checks without adding extra load to your production system.

share|improve this answer

Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let’s explore CHECKSUM and VERIFYONLY options.

The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. To make the checksum recorded into each page of the backup file the “WITH CHECKSUM” option needs to be added to “BACKUP DATABASE” command.

The RESTORE VERIFYONLY option is a good choice to check each backup after the backup has completed. Unfortunately, this takes additional processing time for this to complete, but it is a good practice to put in place.

RESTORE VERIFYONLY FROM DISK = D:\AdventureWorks.bak

This command will check the backup file and return a message of whether the file is valid or not. If it is not valid, this means the file is not going to be useable for a restore and a new backup should be taken. One thing to note is that if there are multiple backups in a file, this only checks the first file.

You can test your database backups, but if the failure occurs you will lose all changes that will be made since the last backup.

share|improve this answer
    
VERIFYONLY simply validates that the file is, in fact, a SQL backup and verifies that it is complete and readable. From MSDN: RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. The only way to check that your backups work is to actually restore them, in their entirety. – Kris Gruttemeyer Feb 17 at 14:10

You'll need to perform transaction log backups at regular intervals between FULL backups in order to allow point-in-time recovery of your database.

ola.hallengren.com can provide scripts to do the backups.

Ensure your database is in FULL recovery model. Consider using native SQL Server backups if your database is a mere 1GB.

Test your backups obviously - restore them and perform DBCC CHECKDB to ensure their integrity.

share|improve this answer

Your Answer

 
discard

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