May 2, 2009 at 11:52 am
Paul White (5/1/2009)
And: Darn it - now I have to go and find out what a transportable thingy is in Oracle!
It allows for tablespaces (SQL filegroups) to be moved from one database to another. The SQL equivalent would be if we could backup (or detach) a filegroup from Database A and restore it (or attach it) to Database B.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2009 at 12:21 pm
troy.hodge (5/1/2009)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
Some of the real questions should be, how much work would it take to make the switch, what could go wrong as part of the switch, do you have any performance problems now, and will you have any in the future?
There will be as many folks on one side of the fence as there are others on this one. Some will correctly tell you this is what schemas are for and that you should change, others will correctly tell you that being able to do a quick and separate restore is the most important and that you shouldn't change. You'll find that some of them are absolute zealots in their stand and that some of those zealots may be at work. 😉
I used to be one of those zealots and ended up burning myself more than once simply by moving too fast on a task of such large impact.
It's time for you to make a list of pro's and con's. Make a list of questions like I started asking at the beginning of this reply. Ask other things like what are the ACTUAL differences between using schema vs using physical databases? Is there really a performance penalty especially if synonyms are used? How much of a penalty? Is that penalty worth it if it enables other features like non-interfering restores during a crisis? Will table partitioning come into play? Is there an alternative to support ease of maintenance and "interference time"? How much time will it take to make and test?
Obviously, you'll need to conduct some simple tests to answer some of those questions and you need to take you're time doing it. We're not talking about a simple checkbook routine or spreadsheet here... we're talking about the life-blood of a business.
The other thing to consider is that what you currently have works and so do things like the separate restores you spoke of. So ask the question, how much of a gain in performance and scalability would be realized if you made the change? Is it worth possibly breaking code? Is it worth the regression testing hours that you'd be insane not to invest in if you made the change?
I'm an anti-cursor zealot... I believe no new code should ever have a cursor in it. But what of existing code? I ask my self the same questions on those as you should be asking yourself about this database change... use the rules of thumb and "expert" opinion that you got for free on the web as a list of things to test for and not the final word. The bottom line in business is what is the investment in time/regression testing and what is the long term ROI in terms of data safety/accuracy, performance, stability, and scalability?
Even if you decide in favor of the change, the questions should not discontinue. Would the process of simple "continuous process improvement through normal attrition" do the same job with less expense? Does the whole system need to be moved all at once or can it be done incrementally?
The answer to your original questions of ...
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
... is, "It Depends" and I don't believe that enough research has been done to answer any of those questions correctly. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2009 at 4:28 pm
Michael Valentine Jones (5/2/2009)
You can restore files or filegroups independently, but as a final step, you need to apply transaction logs to bring the database forward to a transactional consistent state. The end result is no different than restoring from a full database backup and then applying transaction logs. This is obviously not what the OP had in mind, as he was referring to being able to independently restore filegroups to a point in time.Basically, you can restore using a combination of full, differential, file, and\or filegroup backups, but you still need to reach a transactional consistent state by applying the transaction logs before the filegroups that you restored can be brought online in read-write mode.
Well yes - and no! I see why you would say that, but...
Bear in mind, for a moment, the fact that the OP did not specify point-in-time restore in his post. But let us assume he does (and it is an assumption).
And let's imagine the OP has a VLDB on SQL Server 2005 Enterprise Server, with several hundred filegroups (plus primary), and he needs to recover data on a single filegroup to a point in time. The database design is such that the primary filegroup is devoid of user objects, and another filegroup is default.
You would do a partial restore consisting of the empty primary plus the target filegroup only to a temporary new database.
This step will be fast because instant file initialization can be used to create the files, and we only restore data to the target filegroup from the last full file backup.
You would then restore the log backups (with some stop option, such as stop at mark for example) to the primary and the target filegroup only.
(Differential file backups could also be applied as I mentioned previously, and Gail confirmed.)
You would then recover the database in the normal way.
Now the database is accessible as at the point in time requested - though only primary plus the target filegroup are online - the remaining filegroups are marked RECOVERY_PENDING and cannot be accessed.
It is then just a matter of transferring the required data back to the production database.
Michael Valentine Jones (5/2/2009)
So my original statement is correct: "You should use separate databases if you want to be able to restore data independently."...I wasn't too happy about being called wrong when I was right.
Sorry about that. We must have read the original post very differently, because though I see why you said what you said, I still don't think you can claim to be unequivocally 'right'.
I still don't see any reference to point-in-time backups in the original post; however even if that is a requirement, I think I have shown how it could be done quickly and minimal impact on concurrent users. The above method supports point-in-time, and the (compatible!) strategy in my previous post would allow for online data page, file, and filegroup restores, as well as the more usual options.
Now I'm not saying that the methods I describe are necessarily better (or more appropriate for the OP) than individual databases, I'm just pointing out that it is a viable option - "just in case there is still any confusion on this".
Paul
May 2, 2009 at 4:31 pm
GilaMonster (5/2/2009)
Paul White (5/1/2009)
And: Darn it - now I have to go and find out what a transportable thingy is in Oracle!It allows for tablespaces (SQL filegroups) to be moved from one database to another. The SQL equivalent would be if we could backup (or detach) a filegroup from Database A and restore it (or attach it) to Database B.
Right. Yes - that would be handy.
Co-incidentally, I think I described the closest SQL equivalent in my reply to Michael Jones just now.
I assume the transportable thingy is a fixed snapshot - you can't apply it to a point in time?
Do we win on that count?
Paul
May 2, 2009 at 5:44 pm
I suppose I should also add that database snapshots can be used to provide another level of protection against data accidents.
I've only been asked to restore data twice in the last 18 months or so, and both times I was able to get the information from a snapshot.
Ok so I have been lucky on that front - one requirement was just to 'undo' an accidental update (single row), but those sorts of things do happen and it is tedious in the extreme to have to restore a whole database just to correct a few rows...
If you need to rollback a whole database to 'roughly about that sort of time' a restore from a snapshot can't be beaten for speed.
If it's just a few rows, just copy them across!
Keeping snapshot copies around (maybe hourly, for a rolling few-hours) can make one's life slightly less painful on occasion.
All those benefits on top of the real reason you created it in the first place - cool.
In case anyone is wondering - using snapshots in this way is not a replacement for a bullet-proof backup strategy!
Paul
May 2, 2009 at 8:13 pm
Paul White (5/2/2009)
Michael Valentine Jones (5/2/2009)
You can restore files or filegroups independently, but as a final step, you need to apply transaction logs to bring the database forward to a transactional consistent state. The end result is no different than restoring from a full database backup and then applying transaction logs. This is obviously not what the OP had in mind, as he was referring to being able to independently restore filegroups to a point in time.Basically, you can restore using a combination of full, differential, file, and\or filegroup backups, but you still need to reach a transactional consistent state by applying the transaction logs before the filegroups that you restored can be brought online in read-write mode.
Well yes - and no! I see why you would say that, but...
Bear in mind, for a moment, the fact that the OP did not specify point-in-time restore in his post. But let us assume he does (and it is an assumption).
And let's imagine the OP has a VLDB on SQL Server 2005 Enterprise Server, with several hundred filegroups (plus primary), and he needs to recover data on a single filegroup to a point in time. The database design is such that the primary filegroup is devoid of user objects, and another filegroup is default.
You would do a partial restore consisting of the empty primary plus the target filegroup only to a temporary new database.
This step will be fast because instant file initialization can be used to create the files, and we only restore data to the target filegroup from the last full file backup.
You would then restore the log backups (with some stop option, such as stop at mark for example) to the primary and the target filegroup only.
(Differential file backups could also be applied as I mentioned previously, and Gail confirmed.)
You would then recover the database in the normal way.
Now the database is accessible as at the point in time requested - though only primary plus the target filegroup are online - the remaining filegroups are marked RECOVERY_PENDING and cannot be accessed.
It is then just a matter of transferring the required data back to the production database.
Michael Valentine Jones (5/2/2009)
So my original statement is correct: "You should use separate databases if you want to be able to restore data independently."...I wasn't too happy about being called wrong when I was right.
Sorry about that. We must have read the original post very differently, because though I see why you said what you said, I still don't think you can claim to be unequivocally 'right'.
I still don't see any reference to point-in-time backups in the original post; however even if that is a requirement, I think I have shown how it could be done quickly and minimal impact on concurrent users. The above method supports point-in-time, and the (compatible!) strategy in my previous post would allow for online data page, file, and filegroup restores, as well as the more usual options.
Now I'm not saying that the methods I describe are necessarily better (or more appropriate for the OP) than individual databases, I'm just pointing out that it is a viable option - "just in case there is still any confusion on this".
Paul
The OP wrote he was told this about SQL Server:
"They also said we would be able to back up and recover each individual business unit by each file group and no affect to the rest of the data within the database."
So the OP wants to be able to backup and restore the data for individual business units completely independently of each other. This would amount to recovering the data in one filegroup to a different point in time than the other parts of the database, and that is something that SQL Server does not allow, except for a read only file group.
As I have said all along, the OP will need to use separate databases to be able to restore data independently.
May 3, 2009 at 12:36 am
Paul White (5/2/2009)
GilaMonster (5/2/2009)
Paul White (5/1/2009)
And: Darn it - now I have to go and find out what a transportable thingy is in Oracle!It allows for tablespaces (SQL filegroups) to be moved from one database to another. The SQL equivalent would be if we could backup (or detach) a filegroup from Database A and restore it (or attach it) to Database B.
Right. Yes - that would be handy.
Co-incidentally, I think I described the closest SQL equivalent in my reply to Michael Jones just now.
I assume the transportable thingy is a fixed snapshot - you can't apply it to a point in time?
Do we win on that count?
Assuming I undertand what you mean, then no, Oracle Transportable Tablespaces are not fixed snapshots. Once they are snapped into another database they are live and writable.
My understanding of how they can do this (and we cannot) is that apparently we (SQL Server) always have some part of the object definition/header in the primary filegroup, whereas their object definitions/headers can be completely localized to a tablespace.
Thus if we take a filegroup to another database and try to attach/restore it into that database, the filegroup's object header/definitions won't match the primary's part of the object headers (fyi: I am hand-waving here, I don't know the actual details).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 2:08 am
Michael Valentine Jones (5/2/2009)
This would amount to recovering the data in one filegroup to a different point in time than the other parts of the database, and that is something that SQL Server does not allow, except for a read only file group.
I'm not sure that is correct (about the OP's requirements), but in any case point-in-time restore is perfectly possible without separate databases as I said in my last post.
Michael Valentine Jones (5/2/2009)
As I have said all along, the OP will need to use separate databases to be able to restore data independently.
I don't agree. Here is a demo script to illustrate my point.
The first script creates a test database and makes some changes.
The second script restores a single table to a point-in-time between log backups.
You should have a c:\temp directory before running the script.
Ideally, it would run in SQLCMD mode so it can clean up completely after itself.
Cheers,
Paul
quaero ergo sum
USE [master]
GO
CREATE DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675]
ON
PRIMARY
(
NAME = 'primary',
FILENAME = N'c:\temp\primary.mdf',
SIZE = 3MB,
MAXSIZE = 3MB
),
FILEGROUP FG_DEFAULT DEFAULT
(
NAME = 'default',
FILENAME = 'c:\temp\default.mdf',
SIZE = 8MB
),
FILEGROUP UNITA_TABLEA
(
NAME = 'unita_tablea',
FILENAME = N'c:\temp\unita_tablea_data.ndf',
SIZE = 1MB
),
FILEGROUP UNITA_TABLEB
(
NAME = 'unita_tableb',
FILENAME = N'c:\temp\unita_tableb_data.ndf',
SIZE = 1MB
),
FILEGROUP UNITB
(
NAME = 'unitb',
FILENAME = N'c:\temp\unitb_data.ndf',
SIZE = 1MB
)
LOG ON
(
NAME = 'primary_log',
FILENAME = N'c:\temp\primary_log.ldf',
SIZE = 16MB,
MAXSIZE = 16MB
)
COLLATE LATIN1_GENERAL_BIN2;
GO
ALTER DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] SET RECOVERY FULL;
GO
BACKUP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] FILE = 'primary' TO DISK = 'c:\temp\primary_file.bak' WITH FORMAT;
BACKUP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] FILE = 'default' TO DISK = 'c:\temp\default_file.bak' WITH FORMAT;
BACKUP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] FILE = 'unita_tablea' TO DISK = 'c:\temp\unita_tablea_file.bak' WITH FORMAT;
BACKUP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] FILE = 'unita_tableb' TO DISK = 'c:\temp\unita_tableb_file.bak' WITH FORMAT;
BACKUP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675] FILE = 'unitb' TO DISK = 'c:\temp\unitb_file.bak' WITH FORMAT;
BACKUP LOG [842F6400-6FDD-467D-A6D3-620C413BF675] TO DISK = 'c:\temp\log.bak' WITH FORMAT;
GO
USE [842F6400-6FDD-467D-A6D3-620C413BF675];
GO
CREATE TABLE dbo.TableA (S VARCHAR(50)) ON UNITA_TABLEA;
CREATE TABLE dbo.TableB (S VARCHAR(50)) ON UNITA_TABLEB;
CREATE TABLE dbo.TableC (S VARCHAR(50)) ON UNITB;
INSERT dbo.TableA VALUES ('A')
INSERT dbo.TableB VALUES ('B')
INSERT dbo.TableC VALUES ('C')
GO
BACKUP LOG [842F6400-6FDD-467D-A6D3-620C413BF675] TO DISK = 'c:\temp\log.bak'
GO
-- Point in time restore to here
BEGIN TRAN MarkUpdate WITH MARK;
-- Make changes
UPDATE dbo.TableA SET S = '1' WHERE S = 'A'
UPDATE dbo.TableB SET S = '2' WHERE S = 'B'
UPDATE dbo.TableC SET S = '3' WHERE S = 'C'
COMMIT
use master
BACKUP LOG [842F6400-6FDD-467D-A6D3-620C413BF675] TO DISK = 'c:\temp\log.bak' WITH NORECOVERY
GO
Script 2
use master
IF DB_ID('E43488B0-BBDD-49AF-8CA9-FED963DDC851') IS NOT NULL DROP DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
GO
-- Restore primary and desired filegroup only to to temp database
RESTORE DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
FILEGROUP = 'primary'
FROM DISK = 'c:\temp\primary_file.bak'
WITH
MOVE 'primary' TO 'c:\temp\primary_x.mdf',
MOVE 'primary_log' TO 'c:\temp\primary_log_x.ldf',
PARTIAL,
NORECOVERY
GO
-- Restore the target filegroup
RESTORE DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
FILEGROUP = 'UNITA_TABLEB'
FROM DISK = 'c:\temp\unita_tableb_file.bak'
WITH
MOVE 'unita_tableb' TO 'c:\temp\unita_tableb_data_x.ndf',
NORECOVERY
GO
-- Do the point-in-time restore
RESTORE LOG [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
FILEGROUP = 'primary', FILEGROUP = 'UNITA_TABLEB'
FROM DISK = 'c:\temp\log.bak'
WITH NORECOVERY, FILE = 1, STOPBEFOREMARK = 'MarkUpdate'
-- the above returns:
-- This log file contains records logged before the designated mark.
-- The database is being left in the Restoring state so you can apply another log file.
GO
RESTORE LOG [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
FILEGROUP = 'primary',
FILEGROUP = 'UNITA_TABLEB'
FROM DISK = 'c:\temp\log.bak'
WITH NORECOVERY, FILE = 2, STOPBEFOREMARK = 'MarkUpdate'
-- the above returns:
-- This log file contains records logged before the designated mark.
-- The database is being left in the Restoring state so you can apply another log file.
GO
RESTORE LOG [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
FILEGROUP = 'primary',
FILEGROUP = 'UNITA_TABLEB'
FROM DISK = 'c:\temp\log.bak'
WITH NORECOVERY, FILE = 3, STOPBEFOREMARK = 'MarkUpdate'
-- the above returns:
-- RESTORE LOG successfully processed 6 pages in 0.038 seconds (1.199 MB/sec).
GO
-- Bring the partial database online
use master
RESTORE DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] WITH RECOVERY
-- Data in filegroup DEFAULT is offline, and deferred transactions exist. Use RESTORE to recover the filegroup, or drop the filegroup if you never intend to recover it. Log truncation cannot occur until this condition is resolved.
GO
-- Note db is online and filegroup status
USE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
selecttype_desc,
[name],
physical_name,
state_desc
from sys.database_files
GO
-- Drop unneeded filegroups
ALTER DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] REMOVE FILEGROUP FG_DEFAULT
ALTER DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] REMOVE FILEGROUP UNITA_TABLEA
ALTER DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] REMOVE FILEGROUP UNITB
GO
-- Show details of now-defunct filegroups
USE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
selecttype_desc,
[name],
physical_name,
state_desc
from sys.database_files
GO
-- Resolve deferred transaction
use master
ALTER DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] SET EMERGENCY WITH ROLLBACK IMMEDIATE
ALTER DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851] SET ONLINE
GO
USE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
GO
select * from TableA
-- The query processor is unable to produce a plan for
-- the table or view 'TableA' because the table resides
-- in a filegroup which is not online.
GO
select * from TableB -- returns 'B' not '2'
GO
select * from TableC
-- The query processor is unable to produce a plan for
-- the table or view 'TableC' because the table resides
-- in a filegroup which is not online.
GO
-- Tidy up
use master
DROP DATABASE [E43488B0-BBDD-49AF-8CA9-FED963DDC851]
DROP DATABASE [842F6400-6FDD-467D-A6D3-620C413BF675];
GO
-- Manually delete the .bak files or run the following in SQLCMD mode
!! del c:\temp\*.bak
edit: added '(about the OP's requirements)' in this first sentence to clarify my meaning
May 3, 2009 at 2:16 am
RBarryYoung (5/3/2009)
Assuming I undertand what you mean, then no, Oracle Transportable Tablespaces are not fixed snapshots. Once they are snapped into another database they are live and writable.
I was just checking that an OTT (ha!) doesn't have some sort of spooky ability to restore to a point in time other than the exact time at which is was created.
RBarryYoung (5/3/2009)
My understanding of how they can do this (and we cannot) is that apparently we (SQL Server) always have some part of the object definition/header in the primary filegroup, whereas their object definitions/headers can be completely localized to a tablespace.
Don't know about Oracle, but what you say is true of SQL Server.
RBarryYoung (5/3/2009)
Thus if we take a filegroup to another database and try to attach/restore it into that database, the filegroup's object header/definitions won't match the primary's part of the object headers (fyi: I am hand-waving here, I don't know the actual details).
That's ok - wave away. Apparently my last post wasn't as clear as I thought it was, so I have just posted a demo to illustrate my point.
Do you know if Oracle can restore in the same way as my demo - or to the same effect without restoring the whole database?
The point is simply one of recovery time - that, and proving it can be done!
Cheers,
Paul
May 3, 2009 at 2:43 am
Paul White (5/3/2009)
RBarryYoung (5/3/2009)
Thus if we take a filegroup to another database and try to attach/restore it into that database, the filegroup's object header/definitions won't match the primary's part of the object headers (fyi: I am hand-waving here, I don't know the actual details).That's ok - wave away. Apparently my last post wasn't as clear as I thought it was, so I have just posted a demo to illustrate my point.
Do you know if Oracle can restore in the same way as my demo - or to the same effect without restoring the whole database?
The point is simply one of recovery time - that, and proving it can be done!
Hmm, I never dealt with OTT from the standpoint of point-in-time recovery (the OTT feature(s) that our customer paid us to emulate in SQL Server worked more like Detach/Attach), but thinking about it I am sure that they must have been able to. The architectural comparison from Microsoft made it pretty clear to me that Oraclle Tablespaces were intended to give them some of the same separation & independence that we get from separate databases.
As I said, I did not really get into the backup/restore issues, but I seems very likely that they should be able to do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 2:48 am
RBarryYoung (5/3/2009)
Hmm, I never dealt with OTT from the standpoint of point-in-time recovery (the OTT feature(s) that our customer paid us to emulate in SQL Server worked more like Detach/Attach), but thinking about it I am sure that they must have been able to. The architectural comparison from Microsoft made it pretty clear to me that Oraclle Tablespaces were intended to give them some of the same separation & independence that we get from separate databases.As I said, I did not really get into the backup/restore issues, but I seems very likely that they should be able to do this.
Cool, thanks Barry.
I guess I'm not exactly in the perfect place to ask detailed Oracle questions anyway! 😀
Paul
May 3, 2009 at 3:23 am
Paul White (5/3/2009)
Michael Valentine Jones (5/2/2009)
As I have said all along, the OP will need to use separate databases to be able to restore data independently.I don't agree. Here is a demo script to illustrate my point.
That's not independent. That's just showing that you can restore 1 or 2 filegroups of a DB that has more. That's just partial database availability. That's great when the DB is down and you need to restore parts of it really fast so that the users can carry on going, but it's not going to allow you to restore just filegroup 6 to an existing database because someone stuffed up and deleted important data from 20 tables.
What cannot be done with filegroups and, what I read the OP wants is to restore a filegroup backup over the filegroup in an existing database and leave it at a different time that the rest of the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2009 at 3:30 am
RBarryYoung (5/3/2009)
My understanding of how they can do this (and we cannot) is that apparently we (SQL Server) always have some part of the object definition/header in the primary filegroup, whereas their object definitions/headers can be completely localized to a tablespace.
In SQL, the system tables are always entirely in Primary. Plus, iirc, the file header pages have info on what DB thay belong to. Combination means that a file alone has no info as to what's in it. The metadata's entirely in the primary filegroup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2009 at 4:17 am
GilaMonster (5/3/2009)
That's not independent. That's just showing that you can restore 1 or 2 filegroups of a DB that has more. That's just partial database availability. That's great when the DB is down and you need to restore parts of it really fast so that the users can carry on going, but it's not going to allow you to restore just filegroup 6 to an existing database because someone stuffed up and deleted important data from 20 tables.What cannot be done with filegroups and, what I read the OP wants is to restore a filegroup backup over the filegroup in an existing database and leave it at a different time that the rest of the database.
The original post says nothing about restoring to a different point in time. It really doesn't.
If the OP wants to be able to fully restore an individual filegroup that is trivial. This is the most common requirement in the real world.
If he requires point-in-time recovery, you can use the technique I demonstrated. It is fast and easy to do. As I said previously, once the data is recovered to a point in time, it is simple and fast to copy that back to the main database.
The remainder of the live database stays online and accessible - something which is not true for a full database restore. The recovery time will also be quicker - you must agree that these things are important.
I think the solution describes meets any sensible definition of 'independent' - if anything it is more independent that a separate database solution.
Maybe you are assuming that each 'department' would have just one filegroup? There is no requirement for that. You can have 32,767 filegroups if you really want. Placing large or tightly-coupled tables on their own filegroup makes good sense, from a recovery point of view.
Add some rolling database snapshots to my scenario and this makes for a very flexible solution.
Insisting that a filegroup must be able to be restored to a point in time in the existing database is an imaginary restriction.
Paul
May 3, 2009 at 4:30 am
I'm going to take this bit by bit to be as clear as I can for Gail and Michael:
troy.hodge (5/1/2009)
Our Legacy DB2 Z/OS System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. All R/I is contained within each databaseThis gives us the ability to back up each business area at a time that is most convenient for that business group. This also gives us 13 separate recovery areas, so if we lose one, we can restore it while the other 12 are up and running.
It seems clear that availability is important - keeping as much as possible online while a restore is in progress.
Database restore: entire database unavailable while full recovery of the database is performed.
Online filegroup restore: entire database available except for the object(s) stored on the filegroup being restored.
The OP may also not be aware that SQL Server backups are always online backups.
troy.hodge (5/1/2009)
I have been told that in SQL Server, we can and should do all of this with 1 database. (by a someone here at work.) They said we can separate filegroups by schema. They also said we would be able to back up and recover each individual business unit by each file group and no affect to the rest of the data within the database.
Backup and restore the individual business unit by each file group - the separate database idea does not cover this.
No affect on the rest of the data within the database - whichever way you read this, filegroup restore does everything and more compared to a database backup.
troy.hodge (5/1/2009)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
The OP seems to be looking for options and ideas - not dogma. You may argue about the relative merits of each approach - and I am more than happy to do so - but the OP should be aware that there is more than one way to do what he wants.
Paul
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply