Ignore table during backup

I have a database that is relatively small except for 1 table that is 1.5 gigs (document storage).  

Periodically, I back this database up and copy it to my development server.  Is there a way to back up the database while telling SQL Server to NOT backup the one table.  For the example the Database name is GRANTMGMT and the table to ignore is DOCUMENTS.

LVL 7
Kyle FosterCEOAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Typically you would create the View on the database that contains the table. So assuming you have moved the Documents table to Database2 than to create it you would use something like this:

Use Database2
GO
Create view dbo.vw_DOCUMENTS

as

select RID, ENROLLMENTRID,DOC
from dbo.DOCUMENTS


To use it from another database (Database1) you would do something like this:

Use Database1

Select RID
From Database2.dbo.vw_Documents


Don't forget to enable cross-database ownership chaining on both databases.
0
 
Kyle FosterCEOAuthor Commented:
I don't believe it is relevant, but if I was sure I would already have my answer.  I already have the large table in it's own filegroup.
0
 
MikeWalshCommented:
Look at this linke:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E3GAG

The short answer is Not Easily....

The long answer is yeah you can do filegroup backups just of the filegroups that don't want to, and restore those sort of.. It is cumbersome, you will have to isolate that table on a filegroup by itself and deal with that hassle..

You can do differential backups.. Just restore the full backup once and then take differential backups and restore those, it will be smaller to copy...

Is the size that big of a deal for doing the restore?

It's a lot easier for you (especially for consistent dev and testing) to just take the full backup wit hthat table and all and restore that to Dev... Even if you just bring differential backups (which will most likely be smaller) to save some of the time and bandwidth required.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
Another approach you can take is move the table to it's own database.  Downside is that you lose any relationships to this table and you have to enable cross-database ownership chaining.
0
 
MikeWalshCommented:
I have conveniently managed to ignore cross-database ownership chaining my entire career.. <grin> Since you mention it, Anthony, I'll have to look it up now and play with it in a play environment.
0
 
Kyle FosterCEOAuthor Commented:
-->Incremental Backups
I use a Simple Recovery mode.  How do I do incremental backups with this mode?

-->Another approach you can take is move the table to it's own database.  Downside is that you lose any relationships to this table and you have to enable cross--->database ownership chaining.  

My App maintains a connection to the database which costs my clients a sql user.  Ignore my ignorance, but if I use a seperate database then wou;dn't I need another connection?

--> MikeWalsh - as far as playing <grin> I seem to have spent the entire week doing just that <grin again>
0
 
anyoneisCommented:
What about using DTS in Enterprise Manager? "All Tasks", "Export Data", set the destination to <new>, Deselect "Copy all objects" and select everything except the one table you want to skip.

David
0
 
imran_fastCommented:
>>Another approach you can take is move the table to it's own database.  Downside is that you lose any relationships >>to this table and you have to enable cross-database ownership chaining.
This is good idea move the table to New database and create a view of that table in your orignal database from this New Database.

>>but if I use a seperate database then wou;dn't I need another connection?

Not at all because you will use the view to access the new database from orignal database

Create view GRANTMGMT.dbo.DOCUMENTS as
select col1,col2...coln from Newdatabse.dbo.DOCUMENTS

0
 
MikeWalshCommented:
Kfoster:

-->Incremental Backups
-->I use a Simple Recovery mode.  How do I do incremental backups with this mode?
In Simple Recovery you cannot do Transaction Log backups, but you can still do differential backups.. Look up Differential Backup in books online. You will see a How To in there. Rather than copy and paste the How To, I'll point you there.. But basically you just select "Database-Differential" in the backup type from Enterprise Manager, or use the "WITH DIFFERENTIAL" addition to your backup script in T-SQL.. The restore How To's are also there.. Just note that you have to restore your Full With NoRecovery, then restore your Differential with Recovery..

Also important to note that you don't have to "apply" multiple differential backups when doing a restore. You don't have
0
 
Anthony PerkinsCommented:
Mike,

>>I have conveniently managed to ignore cross-database ownership chaining my entire career..<<
We will have to work on that :)

For a light-hearted exchange (or how you can lead a horse to water, but you can't make him drink) on the subject see the following recent thread:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21871987.html


kfoster11,
>>Ignore my ignorance, but if I use a seperate database then wou;dn't I need another connection?<<
No.
0
 
MikeWalshCommented:
that flat spot on the front of my forehead is coming back after reading that Anthony...
0
 
Anthony PerkinsCommented:
<offtopic>
For another thread in much the same vein from one of the current contributors to this thread, check out:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21876600.html
</offtopic>
0
 
Anthony PerkinsCommented:
My last comment was directed at Mike.
0
 
Kyle FosterCEOAuthor Commented:
For now let's look at using a seperate database.  This will be simpler for me.  If I create a view into a 2nd database that holds the large table.

Table DOCUMENTS
---------------------
RID int (PRIMARY KEY)
ENROLLMENTRID int
DOC image


Create view GRANTMGMT.dbo.DOCUMENTS as
select RID,ENROLLMENTRID,DOC from GRANTMGMTDOCS.dbo.DOCUMENTS

and my application uses a couple of SQL Statements to access the data
---> SELECT RID,DOC FROM GRANTMGMT.dbo.DOCUMENTS WHERE ENROLLMENTRID=1
or
---> SELECT RID,DOC FROM GRANTMGMT.dbo.DOCUMENTS WHERE RID=1

Where and how is the indexing?  

I am using SQL 2000.

0
 
MikeWalshCommented:
<offtopic to anthony>
Still banging my head on the desk.. I guess if he knew so much why bother asking (not referring to this thread)... I love it... Ask a loaded question with preconceived notions and argue all of the answers from the experts who focus on that aspect of technology... great laugh.. I saw the subject line of that question and steered clear.. SELECT * is for bad developers or writing a quick one off select when no one else is looking
</offtopic>
0
 
Kyle FosterCEOAuthor Commented:
Offtopic ...  but RIGHT ON!
0
 
Eugene ZCommented:
move the  DOCUMENTS in new database file or\and filegroup:
and use File or\and fileGroup backup Sql server functionality:

more:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E3GAG
++++++++++=
Post here if you do not know how to move the table in new datafile
0
 
Kyle FosterCEOAuthor Commented:
The table is already in its own filegroup that has 1 datafile.  So it basically is in a seperate file.  I will look at the link for backing up a single filegroup.  This should work for me.
0
 
Kyle FosterCEOAuthor Commented:
alright, when I go to All Tasks|Backup Database the option for FILE/FILEGROUP is grayed out.  Any ideas?
0
 
Eugene ZCommented:
your DB in simple Recovery mode - must be not in Simple to make File\filegroup available
0
 
Eugene ZCommented:
answer was provided:
"must be not in Simple to make File\filegroup available"
0
 
Anthony PerkinsCommented:
A number of good solutions were provided, I would recommend that at the very least the question be PAQ'd, whether the points are split or not, I will leave up to the questioner or the Moderator (whoever comes first)
0
 
Anthony PerkinsCommented:
Thanks.  I appreciate the points.
0
 
Kyle FosterCEOAuthor Commented:
sry, I just moved over the last few weeks and I forgot to award the points.
K Foster
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.