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

Posted on 2006-05-03
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
    LVL 87

    Accepted Solution

    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

    Ok many thanks

    LVL 3

    Expert Comment

    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 87

    Expert Comment

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    If you have a USB Drive that is not recognized by Windows the problem is usually that you have too many network drives or other drives that occupy all the drive letters D: E: or F: which is the normal drive letter of a usb drive. The way to correct …
    I have written before on the benefits of using a Boot media other than your HDD when it has become infected.   The article I wrote about creating a bootable CD/DVD/USB ( was mainly concerned with building a UBCD4Win on CD …
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now