SQL Backup skipping a few tables

I've got a Microsoft CRM database that I need to work with in order to prep for a data conversion to our new internal software.

However, this database is 40GB and it's problematic for me to get fresh copies of it from production.  That being said, there are 2 tables that are 30GB in size, just blobs that I don't need.  Can I run a backup into a .bak and exclude those from being exported?  (This is SQL2005 fyi)
LVL 16
Dustin SaundersDirector of OperationsAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can also create a snapshot replication and filter out those 2 tables from the replication.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Can I run a backup into a .bak and exclude those from being exported?  
What do you mean exclude them from being exported? A backup takes all tables so you can't exclude tables from a backup.
But if you have the database with more than one filegroup and have a filegroup only for those 2 tables you can backup the filegroup that you need and restore it after so you're excluding the filegroup with the 2 tables inside.
0
 
Shaun KlineLead Software EngineerCommented:
Do you need the up to the minute copy, or can you get the backup that (hopefully) occurs on schedule (nightly / hourly)?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Dustin SaundersDirector of OperationsAuthor Commented:
I just need to grab a copy every now and then, but waiting for the 40GB file to move to my dev environment takes a lot of time.

I'll need to write and test the SQL queries that will be used when we do the Go Live on the new software (so I will need to pull a fresh copy on that day too).  I'm thinking at this point my best bet is going to be to copy this database to a new one, skipping those tables, and then grabbing that .bak
0
 
Dustin SaundersDirector of OperationsAuthor Commented:
I ended up created a new database with just the tables I needed, then backing that up-- but both of these posts contain good information on how to accomplish this in another way.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How do you keep those tables up to dated?
0
 
Dustin SaundersDirector of OperationsAuthor Commented:
I don't, but I don't have a need to.

As mentioned, I'm doing a data conversion from our existing database into our new system so I just need a dummy copy of the database to write the data conversion SQL; so I just needed to be able to play with the data structure, test for conversion errors, etc.

On go-live day, I'll run the data conversion against the actual production database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.