davidalley
asked on
What is the best way.............
Hi guys,
What is the best way to backup windows 2003 server and SQL server. They are both on the same server.
Software recommendations??
Cheers,
Johno
What is the best way to backup windows 2003 server and SQL server. They are both on the same server.
Software recommendations??
Cheers,
Johno
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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".
ASKER