January 12, 2012 at 5:33 am
Hi, we have a SQL 2008 database in which one table contains images. The database backup size is now too big to backup and remotely copy.
My requirement, which I wonder if anyone can help with, is to separate that table to another database, but have it linked seamlessly as though a table in the original database.
Can that be done? Can anyone help?
Thank you.
January 12, 2012 at 6:07 am
Yes, it can be done moving the table to a different database and creating a view with the same name in the original database.
Be careful: some things, such as foreign keys, may prevent you from moving to a different DB.
-- Gianluca Sartori
January 12, 2012 at 6:18 am
1. What kind of backup do you have today ?
Do you use :
- differential backups
- compressed backup (if your SQL Server is Enterprise Edition on sql 2008 or Standard / Enterprise on 2008 R2)
?
2. Is there a good reason to store those images in a database ?
January 12, 2012 at 6:24 am
OK I will look into that, thanks.
January 12, 2012 at 6:26 am
We do full backups daily on that system.
The images are copies of documents that have been printed or scanned. There are too many to efficiently keep on disk as individual files.
January 12, 2012 at 6:28 am
kevin.smith-1067125 (1/12/2012)
Hi, we have a SQL 2008 database in which one table contains images. The database backup size is now too big to backup and remotely copy.
That tells me either your database is in the Petabyte size range or that your network has serious issues. Please tell me, though, that the issue is backing up across the network, not backing up period. Because if you've stopped backing up your db, you'll be in a world of hurt when it goes down.
SQL Server now does compression for its backups. It will take longer than a regular backup, but it is worth it. Do the compression, copy the backup across the network or save it to an external disk drive and plug the disk drive into the new server.
January 12, 2012 at 6:33 am
Thanks. The database is not that big (100GB), but it is too big to copy from the client to our office using RDC for example. Also if we try to back it up whilst users are busy, the backup slows everyone down so we can't do that in working hours.
We want to get regular copies of the data (without resorting to sending USB drives around) but we do not need the image data for our purposes, so moving it to a separate DB seems a good idea in that we can back up during working hours and also copy the non-image data remotely.
January 12, 2012 at 6:36 am
I see. What about moving the binary data in a separate filegroup and perform a partial backup?
Compression won't probably help in this case, because images are usually already compressed.
-- Gianluca Sartori
January 12, 2012 at 6:36 am
kevin.smith-1067125 (1/12/2012)
Thanks. The database is not that big (100GB), but it is too big to copy from the client to our office using RDC for example. Also if we try to back it up whilst users are busy, the backup slows everyone down so we can't do that in working hours.
Speaking as someone with databases of 100-300 GB, if your backups are slowing down your server, you have hardware issues. It is not the size of the database that is causing problems.
Run Performance Monitor on your server. Check for memory and I/O issues. Check for CPU problems (but my money is on I/O or Memory).
January 12, 2012 at 6:42 am
Hmmmm, separate filegroup and partial backup. Will look into that too. Do you think this preferable to the 'view' solution mentioned earlier? Thanks.
January 12, 2012 at 6:47 am
IMHO, no. Taking FULL backups is much easier.
-- Gianluca Sartori
January 12, 2012 at 6:49 am
Don't overlook Brandie's suggestions. Fix the server first of all.
-- Gianluca Sartori
January 12, 2012 at 6:49 am
kevin.smith-1067125 (1/12/2012)
Hmmmm, separate filegroup and partial backup. Will look into that too. Do you think this preferable to the 'view' solution mentioned earlier? Thanks.
You cannot restore a single filegroup or a partial backup to a database that has never had been restored from a full backup of the source database.
Full backups are required for the initialization of all other backups and restore processes.
January 12, 2012 at 8:45 am
Ugly Solution:
•Create a dummy database.
•Create the table that matches with Image_table in your main database.
•Export the data into dummy database.
•Clean the data (Images) in main database.
•Create FULL backups for these two databases. Optionally you can compress the backups.
•FTP to your customer in two steps (considering bandwidth).
•Give proper instructions to restore the databases.
•Give proper instructions to import Images back into main database.
•Once verified drop dummy database.
January 12, 2012 at 9:04 am
All,
This is not quite a double post. There is other advice happening in a different thread on this same topic.
http://www.sqlservercentral.com/Forums/Topic1234687-391-1.aspx#bm1234904
I think Paul has it right that the OP should consider using a view rather than actually moving the data.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply