January 27, 2010 at 1:29 pm
I need to do a backup of a database.
Like to include all tables but exclude one of them ( That tables is huge and takes sapce since it has images )
What is the easiest way ? Anyone knows the syntax ?
January 27, 2010 at 1:36 pm
If that table is in the same filegroup as all the other tables, con't do it. If it was in a separate filegroup, then you could use filegroup backups to separate the tables to separate backups. You may want to do some reading regarding this in BOL (Books Online) as there is more to using them that you should be aware of when it comes to restoring databases. It may not be the solution you are looking for in this instance.
January 27, 2010 at 2:06 pm
I know the name of the table. What would be the syntax to find out to chich file group the table belongs ? Any ideas?
January 27, 2010 at 2:14 pm
You can see the filegroup a table belongs to by looking at the properties of the table in Management Studio. If you only see "primary" for the filegroup, you probably don't have more than one filegroup.
Greg
January 27, 2010 at 2:17 pm
Can I create a new filegroup and them move this table to that file group ?
What would be the syntax ?
January 27, 2010 at 3:17 pm
Yes, you can. Here's a good explanation of how to do it: http://www.mssqltips.com/tip.asp?tip=1112
Greg
January 28, 2010 at 8:34 am
Is there a way in SQL sever 2005 to create a back up using a set of tables ( but not all of them )?
January 28, 2010 at 8:37 am
mw112009 (1/28/2010)
Is there a way in SQL sever 2005 to create a back up using a set of tables ( but not all of them )?
Do you mean using the BACKUP DATABASE command? No.
January 28, 2010 at 8:40 am
Yes the BACKUP command
Or if there is another tool or way let me know as well.
Either way is fine.
January 28, 2010 at 8:42 am
mw112009 (1/28/2010)
Yes the BACKUP commandOr if there is another tool or way let me know as well.
Either way is fine.
The only way you can do that is to "copy" the data from the tables you want to backup to a new database and then backup that database.
There is no way to run BACKUP DATABASE on a database and only select specific tables to be backed up.
January 28, 2010 at 8:49 am
That's a lot of work.
Our database has about 2000 tables ( no kidding ). It's just that there are one or 3 tables that store images and we don't want to copy them ( since they take too much space ).
1. )Certainly creating a new database is straight forward. I can copy the script from the source database ( Create Database .... statement )
2.) What will be the next step ?
How do I copy the tables ?
January 28, 2010 at 9:09 am
Are these 1 or 3 tables updated on a regular basis? If so, you need to back them up as well to keep your database transactionally consistant.
January 28, 2010 at 9:12 am
Oh, and trust me, I feel your pain in a way. We are now storing images and attachments for job applicants in our database. It has resulted in a significant growth in the database, but it is, IMHO, the easiest way to keep things in sync.
January 28, 2010 at 9:16 am
Those tables are updated regularly. However we do not need to copy the data in those tables.
We will not be using those tables.
I guess I didn't exlplian my issue clearly.
I need to copy the PROD database to the Devlopment Server. However we have a space issue with the Devlopment Server. So the plan is to somehow get a perfect copy from the PROD database and copy it over to Development. However, we don't need 3 tables to come from PROD to developoment. If we can have those tables empty ( I man in the development database ) that is fine. i don't care about the integrity of the data.
Hope this explains...
January 28, 2010 at 9:24 am
Use the Import/Export wizard to create a SSIS package to move the data from selected tables to another database.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply