3

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?

migrated from stackoverflow.com Apr 22 '13 at 23:07

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

  • 1
    This looks like a question for the DBA site, but it isn't clear what you want to do. Specifically, what does "extract into these tables" mean? Are you asking about partial backups, or file backups, or something else? – Pondlife Apr 22 '13 at 20:51
7

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 
    

enter image description here

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 enter image description here

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.