Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Backup skipping a few tables

Posted on 2016-11-03
7
Medium Priority
?
85 Views
Last Modified: 2016-11-15
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)
0
Comment
Question by:Dustin Saunders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 41872390
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
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 41872398
Do you need the up to the minute copy, or can you get the backup that (hopefully) occurs on schedule (nightly / hourly)?
0
 
LVL 13

Author Comment

by:Dustin Saunders
ID: 41872407
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41872421
You can also create a snapshot replication and filter out those 2 tables from the replication.
0
 
LVL 13

Author Closing Comment

by:Dustin Saunders
ID: 41888007
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41888012
How do you keep those tables up to dated?
0
 
LVL 13

Author Comment

by:Dustin Saunders
ID: 41888035
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question