Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can you backup individual schemas within a database?

Posted on 2010-08-13
4
Medium Priority
?
422 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
[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
4 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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