Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario:

Database consists of these file groups:

PRIMARY
xxTEST2
xxTEST3
xxTEST4

Database consists of the following files in those file groups:

xxTEST_RESTORE - Rows Data - PRIMARY - xxTEST_RESTORE.mdf
xxTEST_RESTORE2 - Rows Data - xxTEST2 - xxTEST_RESTORE2.ndf
xxTEST_RESTORE3 - Rows Data - xxTEST3 - xxTEST_RESTORE3.ndf
xxTEST_RESTORE4 - Rows Data - xxTEST4 - xxTEST_RESTORE4.ndf
xxTEST_RESTORE_log - Log - N/A - xxTEST_RESTORE_log.ldf

2 tables are created with row:

dbo.COUNTRIES
dbo.CUSTOMERS

Then a full database backup is performed, and rows are added to the Customers table.

Question:

  1. Want to add 1 record and have a backup of this

  2. Want to add a 2nd record and have a backup of that

  3. Want to roll back to the previous time when the 1st record was added (Q #1 above)

    • Should I perform a TRANS LOG backup after adding the 1st record or a FileGroup backup?

    • What order of backups should I perform to ensure I can roll back properly as I keep getting errors that either the database is in restore mode, doesn't like the trans log I pick, etc....

Looking for the proper sequence for backup and then restore

share|improve this question
    
PLEASE, next time around DO NOT SHOUT at all of us! Writing everything IN UPPERCASE is considering SHOUTING at people - and it's rude and offensive and won't help you get answers - do don't it, please! –  marc_s Apr 12 '13 at 15:42
    
First off, sorry for the uppercase...copied and pasted from notes...I should know better after 20+ yrs :-< Not writing SQL (unless you suggest which I am open too). Was using a combination of enterprise manager and sql scripting. As it is easier to put text in this forum than pictures SQL is fine and will help. –  ShaunStL Apr 12 '13 at 15:56
    
Questions about backups usually get a better response on the DBA site, although here it looks like you're asking how to roll back individual INSERTs. If so, then using backups seems like a very awkward solution, you might consider using row versioning or timestamps instead. But your aim here isn't totally clear, so I may have misunderstood. –  Pondlife Apr 12 '13 at 16:16
    
The setup is that 2 individual db's are melded into 1. Those tables which belonged to the first db get alot of action, while those that belonged to the 2nd db don't get much action. The original dba created a series of filegroups to split the tables from db1 and db2. I am now trying to devise a scenario (documentation) that shows the proper sequence of backup and then restore in case of failure. –  ShaunStL Apr 12 '13 at 16:25
add comment

migrated from stackoverflow.com Apr 12 '13 at 17:29

This question came from our site for professional and enthusiast programmers.

1 Answer

You cannot restore a single filegroup to an earlier state, because that will leave the database in an inconsistent state.

You can restore a database one filegroup at a time. However, as long as a particular filegroup is not up to date with the PRIMARY filegroup, any access to a table that is stored (even in part) on that filegroup will cause the query to error out. Only after all filegroups are restored to the same level (meaning to the same "log sequence number") can the entire database be accessed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.