Sql-server – Best way to perform backups with filegroups and then restore those backups

backupfilegroupssql server

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

Best 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.

Related Question