Solved

Can you backup individual schemas within a database?

Posted on 2010-08-13
4
413 Views
Last Modified: 2012-05-10
I have an sql 2008 datbase which contains two data schema's.  The data schema's are two seperate sets of live data which are called from third party application
I need to be able to backup the schema's individual so that if necessary I can restore one data schema set without effecting the other schema
The standard maintenance plan task in SQL 2008 appears to only allow the entire database to be backed up and does not appear to have an option to specify the schemas seperately.
0
Comment
Question by:HBPROCK
4 Comments
 
LVL 29

Accepted Solution

by:
Rich Weissler earned 500 total points
ID: 33430466
No.
But, here is a discussion on the issue: http://www.sqlservercentral.com/Forums/Topic434637-357-1.aspx
The best option that was presented was using the available VB source code to write a custom backup routine to backup schemas.
(Of course, that would make me nervous, 'cause restores could be problematic in the event of a disaster...)

So, maybe the best answer should be -- "No, that functionality is not available."
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 33430504
Schemas are logical entities, while the backup operates on a physical level. Technically, you could create tables of the two schemas in different filegroups and then you would be able to back up a single filegroup. However, the database integrity is maintained across filegroups, and if you restore just one filegroup, you will have to roll it forward to the end of logs that are applicable to that filegroup. You cannot have a part of the database (filegroup or otherwise) restored to one point in time, while the rest of the database is at a later point in time.

If you need these schemas to live independent lives, they have to reside in separate databases. You could then use synonyms to refer in one database to the objects in another database.
0
 
LVL 3

Expert Comment

by:Viperhawke
ID: 33430511
It is not possible to backup/restore a schema.
You also can can not partition based on Schema either.
This information was confirmed via MS internal SQL discussion.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33434728
What you can do is purchase a reliable backup third party tool such as Red-Gate's SQL Backup (http://www.red-gate.com/products/SQL_Backup/index.htm) which supports restoring specific tables.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now