October 17, 2006 at 2:47 am
Hi,
I'm all new in backup SQL database, so let me try to explain my situation.
I have a PC running SQL 2000 with about 50 different databases in it, altogether almost 200GB of data. In this PC, I have 3 SATA-drives. drive d & e are 250GB. All databases are located on drive d & get updates every night. I now need a daily backup of all databases on drive e without having to stop SQL. I've tried several synchronise tools, drive mirror tools, etc... but all have them needed SQL to be stopped and it took almost 4 hours to synchronise. So I started testing with the native Full SQL BACKUP DATABASE, scheduled daily, which makes a backup to the e-drive real fast but now I see that some of these backups are almost double size than the original mdf-file & ldf-file together.
What's the best, smoothest & fastest way to backup/mirror all my database?
Should I keep looking at the native BACKUP DATABASE or use replication or something else?
Please help me out on this as soon as possible. Thank you in advance.
I did a search here & seen a few things that I've got to consider but not found a similar situation, so excuse me if been asked before.
October 17, 2006 at 9:44 am
If the size of the backup files are growing that large, than you are not initing the device when you run the next backup. That is, you have multiple backups to same file.
backup database northwind to 'e:\sqlbackup\northwind.bak' with init
The with init formats the file to be just a single backup of a database.
Tom
October 18, 2006 at 1:37 am
Hi there,
Tom's answer should save you a reasonable amount of space and will perhaps allivate the problem enough for you that SQL Servers native backup tools are the most appropriate for you.
If however you are still looking to reduce the size of your backups further then you may want to look into using a third party tool such as Red Gate's SQL Backup which will generally backup much quicker than a native SQL Server backup and will also compress and optionally encrypt a backup for you saving time and space.
Hope this helps,
- James
--
James Moore
Red Gate Software Ltd
October 19, 2006 at 1:05 am
Thanks Tom & James for the replies, I'll check and change the WITH INIT addition and see what the Red Gate's backup is about.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply