[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can you backup individual schemas within a database?

Posted on 2010-08-13
4
Medium Priority
?
424 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 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

872 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