| Author |
Topic |
|
systemDown
Starting Member
11 Posts |
 Posted - 2008-07-14 : 22:53:46
|
| We originally intended to backup our live .mdf files using the Amazon S3 service.After finding out about the 5G filesize transfer limit imposed by the S3 service we now have to rethink our strategy.Our databases are considerably larger than 5G and have already been shrunk to their smallest capacity.Is it possible to successfully break a .mdf up into smaller files for a backup to a remote location and then if need be restore it without any data corruption? If so, what would be the best utility to do this with?ORWould it be a better scenario to add physical files to the filegroup keeping the filesizes under the 5G limit and just upload them individually?In addition to all of the above we are also using the Jungle Disk app to update our backups on S3. This program is supposed to only backup the parts of a physical file that change. Is this going to be of any real concern when working with an .mdf file as compared to any other file type?Any expertise on this matter would be greatly appreciated. We have at lease 4 very large dbs we would like to backup offsite using an online service. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-14 : 23:09:44
|
| You can add more smaller data files to the db, but that makes manage db more diffecult. Why don't backup db? Or possivle to zip .mdf backup file? |
 |
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-14 : 23:44:03
|
quote:
Originally posted by rmiao
You can add more smaller data files to the db, but that makes manage db more diffecult. Why don't backup db? Or possivle to zip .mdf backup file?
If I were to do this, will it have any adverse affects on the .mdf file integrity?AndWhat type of compression ratio do you get on .mdf's usually? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-15 : 00:58:40
|
why are you backing up mdf / ldf ? Wouldn't that cause downtime to your system ? Why not using the BACKUP DATABASE to perform the backup ?You can specify multiple filename in BACKUP DATABASE to split the backup into multiple files.Or you can just backup into a single backup file and use your compression utility (zip or rar etc) to compress and split into multiple compressed files.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-15 : 09:23:52
|
quote:
Originally posted by tkizer
Backing up live database files (mdf, ldf, ndf, or custom extensions) is not a good backup strategy as you probably will not be able to recover using these. The only good way to recover from these is to copy them while the SQL Server service is stopped. But these are considered cold database backups. Hot backups are much better as there is no downtime associated with them and they are very reliable. Use BACKUP DATABASE and/or BACKUP LOG for hot backups. Here's my backup utility:http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspxAnd here's all of my database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Thank you very much Tara, I will take a look at these today. If I have any questions should I reply to your post again or is there another preferred way to contact you if I may? |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-15 : 10:00:22
|
| Assuming I were to use a daily full backup with splitting the backup into multiple backup files (like khtan suggests).A main concern would be how each of these files would change individually as the database changes. Let me try to explain what I mean.We are using Jungle Disk to get the files to Amazon S3. Jungle Disk will monitor a file for changes and only update those changes to the file on Amazon.If I take a db backup broken into five physical files, on the next sql backup how different will those files be? Would changes be reflected only in the last of the 5 files or across all 5 files. If changes would be across all files, would they be minor?Basically would the Jungle Disk utility still be cost effective using it's partial file update capabilities. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-15 : 10:15:02
|
| Well this may make me sound like a fossil but do you have an option to back up to tape and ship the tapes to a secure site? How frequently are you going to be doing this?Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-15 : 11:24:46
|
| On the next sql backup, you wouldn't use the same files. So you'd be creating new files each time. Can't the Jungle Disk software copy over new files?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-15 : 15:36:08
|
| Tara,The Jungle Disk application has an additional pay for feature which enables block-level file updates, allowing you to upload only the changed portions of large files.So if you orginally pointed Jungle Disk to somefile.mdb it would upload it entirely. Then over the coarse of the next day changes are made to the file. Jungle Disk compares that file to the online file and only uploads the portions that have changed.This way the charge for bandwidth and transfer is kept very minimal. If we don't use the block level update feature we would be uploading over 50G every night.We currently use this service daily to backup access files among other file types.What we were hoping to do is..Create a full sql backup (named the same, at the same location, with the same options, splitting into multiple physical files) each night, tehn use the Jungle Disk app to upload only the changes of each physical file.Do you think the file integrity would stay intact?Our overall goal was to get 4 of our critical sql db's backed up at a location offsite. The combination of Amazon's S3 service and the Jungle Disk app were going to be the ticket, until we ran into the 5G filesize transfer limit. Our db's are substantially larger than that, so I am trying to come up with a way to get them there on a nightly bases. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-15 : 15:40:48
|
| The full backup is going to be the size of the used portion of the data files. So if you are using 50GB inside the database, then the full backup is going to be 50GB. It'll be that size every time you run a full. It doesn't matter how many files you use, they'll total 50GB (from my example).If you want smaller files, you need to use differentials and/or transaction log backups. But you'll still need to run a full backup on say a weekly basis, otherwise your recovery ability is either at jeopardy or will take too long.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-15 : 18:03:24
|
quote:
Originally posted by tkizer
The full backup is going to be the size of the used portion of the data files. So if you are using 50GB inside the database, then the full backup is going to be 50GB. It'll be that size every time you run a full. It doesn't matter how many files you use, they'll total 50GB (from my example).If you want smaller files, you need to use differentials and/or transaction log backups. But you'll still need to run a full backup on say a weekly basis, otherwise your recovery ability is either at jeopardy or will take too long.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I do understand that the sum of the size of each individual file will still equal the size of data inside the db.For example I have a db that has a size of 16802.31 MB with an available amount of 6027.69 MB and the size of disk is almost 11G. I know every time I do a full backup, providing I don't add/remove a lot of data to the db I will be close to this physical size no matter if it is one file at 11G or 3 files at 3+Gig.So let's say I run a full backup on night 1 splitting it into 3 physical files. Say they are named 1A, 1B, and 1C just for the heck of it. The db, runs for 24 hrs.Now on night 2, I run a full backup exactly the same way breaking the backup into 3 physical files. Naming them 2A, 2B, 2CThe big question... How different is 1A compared to 2A, and how different is 1B compared to 2B and how different is 1C compared to 2CIf for example a large portion of 1A is the same inside the file as 2A then using Jungle Disk would probably be beneficial. If it is going to change drastically from night to night then it would be better just to move all 3 files offsite each night instead of trying to use the block update feature. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-15 : 18:08:15
|
| I was just discussing a situation similar to this with my supervisor. We are going to look into using a netapp with de-duplication. It works the same way as you've described according to him. We'll see huge disk savings if the database doesn't drastically change between backups.If the Jungle Disk works like a netapp (or maybe it is a netapp) where it looks at the blocks, then you don't even have to bother with multiple files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-15 : 22:30:38
|
| >> How different is 1A compared to 2A, and how different is 1B compared to 2B and how different is 1C compared to 2CDon't compare them since they are from different backup, and contents depend on data & schema changes during the day. You need all three files of the day to restore the db. |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-16 : 09:19:33
|
| The reason for the multiple files and the concern for the comparison between backups, is because of the use of Amazon S3.You can not upload a physical file larger than 5G therefore we are forced to split each backup that it over 5G initially.If you do not have the whole file uploaded in some manner (whether that logical file is in one physical file or multiple physical files) there would be no way to do a block level updates on it.So the concern for the comparison between backups is legitimate because it will define how much your block level update bandwidth usage will cost. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-16 : 10:39:04
|
| How different the backups are will depend on what is happening in the database. For example, re-indexing, index defragmentation, or shrinkfile operations will result in almost completely different files.I think the only way you will find that out is to test it.CODO ERGO SUM |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-16 : 11:05:41
|
How reliable is this S3 ? I would rather have all my backup files stored together as a set rather than relying on some tools that upload only when files are different. It will be disasters when you need to restore and only to find out some mismatch between the backup file set.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-16 : 11:31:54
|
| You could use SQL Litespeed or another SQL backup utility to compress your database backups. It is fairly typical to reduce the size of database backups to about 30% of an uncompressed backup.You could also compress the database backup files using a command line file compression utility, like PKZIP. This requires extra steps and additional local disk space, but you can reduce the backup files to 20% or less of an uncompressed backup. CODO ERGO SUM |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-16 : 17:55:33
|
| From what I am being told if the files are compressed in any way the block update feature will not work correctly.Michael,If the db is pre-shrunk and re-indexed before my initial backup would it be necessary to do it again that frequently? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-16 : 22:37:20
|
| >> If the db is pre-shrunk and re-indexed before my initial backup would it be necessary to do it again that frequently? Depends on maintenance schedule and db settings. By the way, db can be changed in many ways. |
|
|
|
systemDown
Starting Member
11 Posts |
Posted - 2008-07-17 : 17:20:52
|
| Using ...BACKUP DATABASE db_name TODISK = "DB_FILE1.BAK",DISK = "DB_FILE2.BAK"WITH INITIs there anyway to control the filesize other than sql automatically distributing evenly between all files? |
|
|
|
Next Page
|
Off site backups of .mdf files