Solved

Can you backup individual schemas within a database?

Posted on 2010-08-13
4
420 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 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

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.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

691 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