1

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

Improve this question
4
  • 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, 2013 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, 2013 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, 2013 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, 2013 at 16:25

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.