Backing up file groups with restore to earlier backup

Question

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?

Answer 1

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.

1. Create a database called "FGTest"

   create database [FGTest] on primary (
   name = N'FGTest'
   ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest.mdf'
   ,SIZE = 3072 KB
   ,FILEGROWTH = 1024 KB
   )
   ,FILEGROUP [lamb] (
   name = N'lamb'
   ,-- fileGroup1
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_2.ndf'
   ,SIZE = 3072 KB
   ,FILEGROWTH = 1024 KB
   )
   ,FILEGROUP [lamb1] (
   name = N'lamb1'
   ,-- fileGroup2
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_3.ndf'
   ,SIZE = 3072 KB
   ,FILEGROWTH = 1024 KB
   ) LOG on (
   name = N'FGTest_log'
   ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\FGTest_log.ldf'
   ,SIZE = 1024 KB
   ,FILEGROWTH = 10 %
   )
   

   go

2. Now create tables on different file groups - lamb and lamb1

   create table TAB1 (
   TAB1_ID int IDENTITY(1, 1)
   ,TAB1_NAME varchar(100)
   ,constraint PK_TAB1 primary key (TAB1_ID)
   ) on lamb -- Filegroup we created.
   go
   
   create table TAB1_lamb1 (
   TAB1_ID int IDENTITY(1, 1)
   ,TAB1_NAME varchar(100)
   ,constraint PK_TAB1_lamb1 primary key (TAB1_ID)
   ) on lamb1 -- 2nd Filegroup we created.
   go
   

3. Take a Base backup

   -- Take a base backup
   BACKUP DATABASE [FGTest] to 
   DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
   with init, stats = 10
   go 
   

4. Now insert some values .... You can load a CSV at this point

   INSERT INTO FGTest..TAB1(TAB1_NAME)
   select ('TAB1')
   union all
   select ('TAB2')
   

5. backup filegroup lamb -- with 2 records

   -- backup filegroup lamb - with 2 records
   
   BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
   TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
   WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
   STATS = 10
   GO
   
   -- log backup filegroup lamb - with 2 records
   
   BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn' WITH INIT 
   

6. Now insert few more values in lamb and lamb1 file groups

   INSERT INTO FGTest..TAB1(TAB1_NAME)
   select ('TAB3')
   union all
   select ('TAB4')
   
   INSERT INTO FGTest..TAB1_lamb1(TAB1_NAME)
   select ('TAB3')
   union all
   select ('TAB4')
   

7. Now backup lamb and lamb1 filegroups along with the T-logs

   -- -- backup filegroup lamb - with 4 records
   
   BACKUP DATABASE [FGTest] FILEGROUP = N'lamb' 
   TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4Records.bak' 
   WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
   STATS = 10
   GO
   
   -- log backup filegroup lamb - with 4 records
   
   BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_4records.trn' WITH INIT 
   
   
   -- -- backup filegroup lamb1 - with 2 records
   
   BACKUP DATABASE [FGTest] FILEGROUP = N'lamb1' 
   TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1.bak' 
   WITH  INIT,  NAME = N'FGTest-Full Filegroup Backup', 
   STATS = 10
   GO
   

   -- log backup filegroup lamb1 - with 2 records

   BACKUP LOG [FGTest] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_lamb1_log.trn' WITH INIT 
   

Now comes the restore Part :

1. Restore the database with PARTIAL and NORECOVERY

    use master
    go
   
    -- restore the base backup with PARTIAL and NORECOVERY
   
    restore database [FGTest]
    filegroup = 'Primary'
    from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_basebackup.bak'
    with REPLACE, PARTIAL, NORECOVERY
    go
   

2. Now Restore the lamb filegroup along with the T-log

    RESTORE DATABASE [FGTest] FILE = N'lamb' FROM  
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2Records.bak' 
    WITH  FILE = 1, REPLACE,  STATS = 10
    GO
   
    RESTORE LOG [FGTest] FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FGTest_2records.trn'
    WITH FILE = 1, NORECOVERY
   

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