Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Ignore table during backup

Posted on 2006-06-06
25
Medium Priority
?
244 Views
Last Modified: 2012-05-05
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.

0
Comment
Question by:kfoster11
  • 7
  • 7
  • 5
  • +3
24 Comments
 
LVL 7

Author Comment

by:kfoster11
ID: 16848097
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16848257
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16848746
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16848760
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
 
LVL 7

Author Comment

by:kfoster11
ID: 16849453
-->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
 
LVL 11

Expert Comment

by:anyoneis
ID: 16849549
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16850124
>>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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16851149
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16851811
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16852333
that flat spot on the front of my forehead is coming back after reading that Anthony...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16853386
<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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16853399
My last comment was directed at Mike.
0
 
LVL 7

Author Comment

by:kfoster11
ID: 16853444
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16853652
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
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16854058
<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
 
LVL 7

Author Comment

by:kfoster11
ID: 16854284
Offtopic ...  but RIGHT ON!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16854737
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
 
LVL 7

Author Comment

by:kfoster11
ID: 16854801
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
 
LVL 7

Author Comment

by:kfoster11
ID: 16854837
alright, when I go to All Tasks|Backup Database the option for FILE/FILEGROUP is grayed out.  Any ideas?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16854983
your DB in simple Recovery mode - must be not in Simple to make File\filegroup available
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17485812
answer was provided:
"must be not in Simple to make File\filegroup available"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17486253
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17486609
Thanks.  I appreciate the points.
0
 
LVL 7

Author Comment

by:kfoster11
ID: 17491571
sry, I just moved over the last few weeks and I forgot to award the points.
K Foster
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 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