March 26, 2009 at 7:03 am
I'm trying to implement a structure whereby we're moving all our DBs into one DB (roughly 650GB) split into schema's / filegroups. With our current structure, in our test environment we can easily restore one of the dbs without affecting any of the others.
Now, what i want to know is, if for example a table is dropped from a particular filegroup, can i restore that particular filegroup to a before the table is dropped (Using Full / TLog backups) whilst keeping the rest of the files online? Everything i've read seems to indicate that you can restore the file but have to restore all logs and the tail of the log to keep them in sync but i just want to confirm this with you guys....
If something gets dropped, i was hoping i don't have to restore the FULL backup and potentially have testing downtime whilst this takes place.
Any info is greatly appreciated 😀
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
March 27, 2009 at 12:48 am
May I ask why you want to do this? From the sounds of it these databases are separate entities. If that is the case by merging them all into one database you are increasing your work and creating possibly more issues.
As to answer your question yes you can do single file group restores and then later apply logs. When applying logs it will not apply the transactions to file groups which are ahead of LSN already. It will only apply transactions that are behind the LSN number in logs in this case the File Group you just restored.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 27, 2009 at 1:17 am
that's the problem, they are seperate in the fact they're seperate physical dbs, but in actual fact they all refer to each other as part of one application but there is no RI between them. Some tables are in one db that should actually be in another and n some respects, the tables are duplicated but have different structures and different data!!!! This is a system I've inherited and it's shocking to be honest.
The reason I ask the question I did is because if during testing something goes wrong, it's easy for us to restore a particular db. Now, if for example someone drops a lookup table from our lookup schema and would like it restored, can I restore this file to before the table was dropped but leave the other schemas as they are? Everything I've been trying indicates that I need to restore all logs plus the tail of the log which takes me back to after the table was dropped....
It may be the case that I just tell them it's not possible, but would like to know either way
Cheers
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
March 30, 2009 at 4:45 pm
Swirl80 (3/27/2009)
that's the problem, they are seperate in the fact they're seperate physical dbs, but in actual fact they all refer to each other as part of one application but there is no RI between them.
So you decision is correct if they all reference each other it is better to have it one database.
Some tables are in one db that should actually be in another and n some respects, the tables are duplicated but have different structures and different data!!!! This is a system I've inherited and it's shocking to be honest.
Must be difficult I have ran into some not so fun systems welcome to DBA world ;-).
The reason I ask the question I did is because if during testing something goes wrong, it's easy for us to restore a particular db. Now, if for example someone drops a lookup table from our lookup schema and would like it restored, can I restore this file to before the table was dropped but leave the other schemas as they are? Everything I've been trying indicates that I need to restore all logs plus the tail of the log which takes me back to after the table was dropped....
It may be the case that I just tell them it's not possible, but would like to know either way
Cheers
Yes you have multiple options here:
1) Make sure each schema in question is in different file or file group; then you can do piecemeal restores. There is no Filegroup cross dependices; that is there is a table in filegroup a that must have record for File group 2 to be restored then.
2) Another option is you can restore the database under another name and then copy the table over.
3) Use RedGate backup tools (I have not tried it); but I read that it lets you restore single tables.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply