| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-09-06 : 16:39:52
|
| Hi FriendsI need your expert advise on this one.We've a database that's going on huge on size .most of this size contributes one table that contains patient images. we reached a point our backup become so huge to do anything(for offline testing for e.g.)what we can do to improve this situation ? one suggestion is to a create a separate file group and move that one huge table there.your ideas much appreciated.Cheers |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-06 : 22:23:50
|
| Or you can move old records to archive table. |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-06 : 22:30:49
|
| but still archive table has to exist somewhere ,should it same file group or different one ?my question is, separate file groups is best solution for this kind of issues?Cheers |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-06 : 23:27:55
|
| Not really from performance point of view if you put all file groups on same disk array. Yes you can backup file group, but makes restore more complex. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-06 : 23:31:47
|
| at the moment we are not worried abt performance but only abt backups as they are getting huge. so any ideas on this much appreciated.Cheers |
|
|
|
Kristen
Test
22859 Posts |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-07 : 23:35:08
|
| If you really want to address backup issue, should take look at third party tools. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-09 : 16:47:59
|
| KristenThat's what i'd suggest but its too late now as clients are already live.Rmiaoapart from backups are they any other issues we'll need to deal with such a huge db sizes ?Thanks all for your ideas.Cheers |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-09 : 17:55:12
|
| Performance, but you said you are not worried about it. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-09 : 18:04:03
|
| yah just want to know if any other things i missed out.performance am not worried at the moment as at point of time user will be dealing with 10-15 rows only at max.Cheers |
|
|
|
Bharat
Starting Member
3 Posts |
Posted - 2007-09-13 : 09:00:03
|
| Hi Rajini ,What SQL server you are using ?In 2005 You can partition the Table into different filegroups and take the backup of each filegroup !!Reagards,Bharat |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-13 : 16:51:16
|
| we use sql2005 but standard edition. as you know partitioning available only in enterprise edition :(finally we decided to do that manually i mean moving older records to a archive table in a different file group.Cheers |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 17:37:13
|
| Why is the size of the backup file a problem? Do you not have enough space for your backups files? How big is your database? How big is the backup file?If your database is really big, you really need to make sure you have the needed amount of disk to be able to manage it properly.CODO ERGO SUM |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-13 : 17:52:50
|
| only issue here is our clients import lot of pictures into database. once imported normally they dont change these pictures. problem is if for any reason if we want their (as a backup) data to troubleshoot some problem it became a problem as those dont go into a DVD ,take lot of time uploading to ftp site., since many times we dont want their pictures but just data we want to somehow exclude them from backups.Cheers |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 18:10:57
|
| You need to find another method to get your test data. You cannot exclude data from a backup, that is not what they are meant for.You might use BCP to extact specific tables to files, but you will have to do this one table at a time.You may want to look into another method for tranfering the data, insted of using FTP or DVDs. How about buying a USB drive big enough to hold the backup, send it to them, have them copy the data onto the USB drive, and then send it back to you. If these are sensetive medical records, you should have them PGP encrypt the backup file before shipping it. You should also encrypt it if you are using FTP; it is not a secure method for sending sensitive data.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 18:23:35
|
| That's often a problem when images are stored in the database itself. No way around it that I can think of, you need the database for DEV. Just exporting the data may not give you a reliable "copy" to work on - e.g. the index fragmentation etc. will be different - so you may not be able to reliably work on the issues that the client is seeing.I would have another go at recommending that the pictures are moved out of the database ... best I can suggest I'm afraid.Kristen |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-13 : 18:29:11
|
| Thank you all for your input. we decided moving older records to a archive table in a different file group. so that we can do file based backups.Cheers |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 18:33:31
|
quote:
Originally posted by rajani
Thank you all for your input. we decided moving older records to a archive table in a different file group. so that we can do file based backups.Cheers
I don't see how that can help. You still need all the backup files to be able to do a recovery.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 18:34:09
|
| I suppose you might be able to put the Images in a different filegroup. But a Restore of a database with multiple filegroups is potentially tricky - i.e. trying to get the restore of all filegroups to an identical moment in time ... worth practicing before you have a real disaster to try to recover!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 18:44:44
|
Maybe you can do a full backup to multiple files. With enough backup files, you should be able to get them small enough to fit on DVDs. You can split the backups into as many files as necessary to get them to fit.Zipping the backup files probably won't help very much if they contain a lot of images.Example of full backup to multiple files:backup database [MyBigDatabase]to disk = 'E:\BACKUP\MyBigDatabase_db_200709131454_001.BAK' , disk = 'E:\BACKUP\MyBigDatabase_db_200709131454_002.BAK' , disk = 'E:\BACKUP\MyBigDatabase_db_200709131454_003.BAK' , disk = 'F:\BACKUP\MyBigDatabase_db_200709131454_004.BAK' , disk = 'G:\BACKUP\MyBigDatabase_db_200709131454_005.BAK'with init, stats = 5 CODO ERGO SUM |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-09-13 : 18:56:04
|
| Michaelwe still finalising how to do this. am surprised your comment abt not able to restore without all files. since we've not tried yet,are you saying if we dont have all file groups we cant restore database ?looks like i need to do more investigation on this. Thanks for your valuable advise. I'll also look at your other suggestion abt multiple backup files.Cheers |
|
|
|
Next Page
|
database size