I have a task: I have created a database with a filegroup lamb
that has a few tables.
I need to extract a csv into a table in a filegroup named lamb
and back up that filegroup.
Then extract another csv file again and back up again.
But I need to be able to restore the first backup.
What is the backup and restore method? Is it possible to restore only the lamb
filegroup?
Best Answer
What is the backup and restore method? It is called Piece Meal Restore and unrestored filegroups can be restored at a later time.
Is it possible to restore only the lamb filegroup? Yes it is possible to restore only the lamb filegroup.
Below will show you - how you can do it.
Create a database called "FGTest"
go
Now create tables on different file groups - lamb and lamb1
Take a Base backup
Now insert some values .... You can load a CSV at this point
backup filegroup lamb -- with 2 records
Now insert few more values in lamb and lamb1 file groups
Now backup lamb and lamb1 filegroups along with the T-logs
-- log backup filegroup lamb1 - with 2 records
Now comes the restore Part :
Restore the database with PARTIAL and NORECOVERY
Now Restore the lamb filegroup along with the T-log
-- since you want to restore the database to 1st backup and you already restored the sequence, its time to bring the database back online with 2 records.
restore database [FGTest] with recovery
Since you have not brought the other file group online, when you try to query TAB1_lamb1 table residing on lamb1 filegroup, the query processor will throw an error.