What is the best way.............

Posted on 2006-05-03
Medium Priority
Last Modified: 2010-04-03
Hi guys,

What is the best way to backup windows 2003 server and SQL server.  They are both on the same server.

Software recommendations??

Question by:davidalley
  • 2
LVL 88

Accepted Solution

rindi earned 200 total points
ID: 16594633
The cheapest is to use SQL dump to create dumps of the database to a separate HD, and then use ntbackup to backup the server's data and the SQL dump files to either a tape or further HD.

Author Comment

ID: 16594736
Ok many thanks


Expert Comment

ID: 16624712
First of all, you can not make a backup of a running system without special precautions to keep the backup in a consistent state.

Say, for example, you've got a database containing a simple forum. In this database you've got 2 tables, `header` and `text`. Text contains all the contents the users have posted on your forum. Header contains all additional information of the posts, like posttime, user, etc. Now, you start a backup of 'header' and once it is finished you start the backup of 'text'. This shouldn't be a problem, except when someone posts anything during the backup. Because there is a big chance that you'll end up with a backup of one table in the situation BEFORE the post, and one table in the situation AFTER the post. So after a restore you've got inconsistent tables causing posts without a header and headers without content.

The same applies for backing-up the operatingsystem. Last thing you want is to find out that your registry is corrupted after you had to restore your OS.

So the easiest way to make desent backups is when the operating-system came to a complete halt. One of my customers has made a backup-solution based on dual-boot. On 2:00 every night, the system starts a script that alters the boot.ini default and restarts te system. The system restarts into the other Windows installation and logs in automatically. This Windows installation is very basic, only the required drivers and software is present. First thing that happens at boot is that the boot.ini file is set back to it's original state so it boots the real- OS by default. Once that's done, it starts a backup of the entire system, except the partition on which the backup-OS resides. After the backup, the system ejects the tape and restarts again to the normal OS. In the morning, the operator replaces the tape and checks the backup-log for any failures.   The downside of this approach is that the server is down between 2:00 and ? every night.

Of course there are solutions to backup an entire running operating-system, but how do you restore your OS? You need at least a way to boot and start the recovery. Professional software do provide a boot-CD or something like that, but to an additional fee. So you might want to consider if it's worth it.

Then the database. Every self-respecting backup-tool has an agent available that integrates with your SQL-software to provide consistent backups. Again, at an additional fee. The agent puts the database (partly or entirely) in backup-mode and waits for the running transactions to finish. In the backup-mode, all transactions are written to the logfiles, instead of the database itself. Therefore the database is consistent and ready to backup. Once the backup has finished, the agent stops the backup-mode and the database applies all changes that were written to the logfiles since the start of the backup.

But for the database you may also choose for an offline-backup. This way you don't need an expensive backup-agent, but you do need to shutdown your database. The quickest way is to stop the database, copy the database-files from one harddisk to another, start the database and move the copied files to tape.

The way Rindi provides, is also possible, but you have to be carefull as I don't know if you can be sure that the SQL-dump is consistent. But maybe Rindi or someone else can give you that guarantee.
LVL 88

Expert Comment

ID: 16629201
The dump is consistent, usually more so than the database itself. A database often contains empty data that was deleted and isn't necessary anymore, and without running special tools on it that space is left unused. It can also contain corrupt data and all that wouldn't be dumped, so a database created from a dump is usually more clean than one that is never "cleaned".

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
A look at what happened in the Verizon cloud breach.
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question