locked
Filegroup Restoration Post FG Restore Index Not reflecting RRS feed

  • Question

  • Hi All,

    I have a situation here.

    I have created a Database. Then I created a Secondary File Group and Placed a File in it.

    Now I created a Table on Primary File Group and Created 2 Indexes on secondary File Group

    I took a backup of secondary Filegroup and then deleted the indexes. Now when I restore the secondary FIle Group I am unable to see indexes. Why Indexes are not being restored. Below is the script,

    USE [master]
    GO
    CREATE DATABASE [MSSD_LAB] 
    GO
    USE [master]
    GO
    ALTER DATABASE [MSSD_LAB] ADD FILEGROUP [IDX_FG]
    GO
    ALTER DATABASE [MSSD_LAB] ADD FILE 
    ( NAME = N'MSSD_LAB_IDX', FILENAME = N'D:\MSSQLSERVER\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MSSD_LAB_IDX.ndf' , 
    SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [IDX_FG]
    GO
    USE [MSSD_LAB]
    GO
    CREATE TABLE TESTIDXBackup
    (
    Col_ClusIdx int
    ,Col_NClusIdx int
    ,Col_NoIdx varchar(10)
    ,Col_NoIdx1 nvarchar(10)
    ) ON [PRIMARY]
    GO
    --CREATE INDEX ON SECONDARY FILE GROUP
    CREATE CLUSTERED INDEX [IDX_Clus_TESTIDXBackup_Col_ClusIdx] ON [dbo].[TESTIDXBackup] 
    (
    	[Col_ClusIdx] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [IDX_FG]
    GO
    CREATE NONCLUSTERED INDEX [IDX_Clus_TESTIDXBackup_Col_NClusIdx] ON [dbo].[TESTIDXBackup] 
    (
    	[Col_NClusIdx] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [IDX_FG]
    GO
    USE master
    GO
    --BACKUP SECONDARY FILE GROUP
    BACKUP DATABASE MSSD_LAB
    File = 'MSSD_LAB_IDX',
    FILEGROUP = 'IDX_FG'
    TO DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\MSSD_LAB_IDX_FG.FLG'
    GO
    --DROP INDEXES ON SECONDARY FILEGROUP
    USE MSSD_LAB
    GO
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TESTIDXBackup]') AND name = N'IDX_Clus_TESTIDXBackup_Col_ClusIdx')
    DROP INDEX [IDX_Clus_TESTIDXBackup_Col_ClusIdx] ON [dbo].[TESTIDXBackup] WITH ( ONLINE = OFF )
    GO
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TESTIDXBackup]') AND name = N'IDX_Clus_TESTIDXBackup_Col_NClusIdx')
    DROP INDEX [IDX_Clus_TESTIDXBackup_Col_NClusIdx] ON [dbo].[TESTIDXBackup] WITH ( ONLINE = OFF )
    GO
    --RESTORE SECONDARY FILE GROUP
    USE master
    GO
    ALTER DATABASE MSSD_LAB SET  SINGLE_USER with ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE MSSD_LAB
    File = 'MSSD_LAB_IDX',
    Filegroup = 'IDX_FG'
    FROM DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\MSSD_LAB_IDX_FG.FLG'
    WITH RECOVERY,  MOVE 'MSSD_LAB_IDX' TO 'D:\MSSQLSERVER\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MSSD_LAB_IDX.ndf'
    GO
    ALTER DATABASE MSSD_LAB SET  MULTI_USER with ROLLBACK IMMEDIATE
    

    I am not sure what wrong I am doing. Any help greatly appreciated.


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Thursday, July 17, 2014 7:04 AM

Answers

  • Thanks to Tibor, see

    USE master 
    IF DB_ID('fgr') IS NOT NULL DROP DATABASE fgr 
    GO 
    --Three filegroups 
    CREATE DATABASE fgr ON  PRIMARY  
    ( NAME = N'fgr', FILENAME = 'E:\fgr.mdf'),  
     FILEGROUP fg1  
    ( NAME = N'fg1', FILENAME = 'E:\fg1.ndf'),  
     FILEGROUP fg2  
    ( NAME = N'fg2', FILENAME = 'E:\fg2.ndf') 
     LOG ON  
    ( NAME = N'fgr_log', FILENAME = 'E:\fgr_log.ldf') 
    GO 
    ALTER DATABASE fgr SET RECOVERY FULL 

    --Base backup 
    BACKUP DATABASE fgr TO DISK = 'E:\fgr.bak' WITH INIT 
    GO 

    --One table on each filegroup 
    CREATE TABLE fgr..t_primary(c1 INT) ON "PRIMARY"
    CREATE TABLE fgr..t_fg1(c1 INT) ON fg1 
    CREATE TABLE fgr..t_fg2(c1 INT) ON fg2 

    --Insert data into each table 
    INSERT INTO fgr..t_primary(c1) VALUES(1) 
    INSERT INTO fgr..t_fg1(c1) VALUES(1) 
    INSERT INTO fgr..t_fg2(c1) VALUES(1) 
    CREATE INDEX my_idx ON  fgr..t_fg2(c1) ON fg2

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH INIT --1 

    --Filegroup backup of fg2 
    BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'E:\fgr_fg2.bak' WITH INIT 

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --2 

    --Drop from t_fg2 
    --Ths is our accident which we want to rollback!!! 
    DROP TABLE fgr..t_fg2 

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --3 

    --Now, try to restore that filegroup to previos point in time 
    RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'E:\fgr_fg2.bak' 
    GO 

    SELECT * FROM fgr..t_fg2 --error 8653 
    GO 
    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --4 


    --We must restore *all* log backups since that db backup 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 2 --out of 3 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 3 --out of 3 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 4 --out of 3 

    GO 

    --What we can do is restore into a new database instead, 
    --to an earlier point in time. 
    --We need the PRIMARY filegroup and whatever more we want to access 
    ---------------------------------------------------------------------------- 
    IF DB_ID('fgr_tmp') IS NOT NULL DROP DATABASE fgr_tmp 
    GO 
    RESTORE DATABASE fgr_tmp FILEGROUP = 'PRIMARY' FROM DISK = 'e:\fgr.bak' 
    WITH 
     MOVE 'fgr' TO 'e:\fgr_tmp.mdf' 
    ,MOVE 'fg2' TO 'e:\fg2_tmp.ndf' 
    ,MOVE 'fgr_log' TO 'e:\fgr_tmp_log.ldf' 
    ,PARTIAL, NORECOVERY 

    RESTORE DATABASE fgr_tmp FILEGROUP = 'fg2' FROM DISK = 'e:\fgr_fg2.bak' 

    RESTORE LOG fgr_tmp FROM DISK = 'e:\fgr.trn' WITH FILE = 1, NORECOVERY 
    RESTORE LOG fgr_tmp FROM DISK = 'e:\fgr.trn' WITH FILE = 2, RECOVERY 

    --Now the data in PRIMARY and fg2 is accessible 
    SELECT * FROM fgr_tmp..t_fg2 
    --The index I created before
    SELECT * FROM fgr_tmp.sys.sysindexes where id=object_id('fgr_tmp..t_fg2')


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Friday, July 18, 2014 9:00 AM
    • Marked as answer by Manjunath C Bhat Monday, July 21, 2014 11:00 AM
    Thursday, July 17, 2014 12:32 PM
    Answerer
  • Tried to repoduce but failed, need couple more eyes to see what is going on :-))

    CREATE DATABASE mywind
    GO
    ALTER DATABASE mywind SET  RECOVERY FULL
    ALTER DATABASE mywind ADD FILEGROUP new_customers
    ALTER DATABASE mywind ADD FILEGROUP sales
    GO

    ALTER DATABASE mywind ADD FILE 
       (NAME='mywind_data_1',
       FILENAME='e:\mw.dat1') 
       TO FILEGROUP new_customers
    ALTER DATABASE mywind 
       ADD FILE 
       (NAME='mywind_data_2',
       FILENAME='e:\mw.dat2') 
       TO FILEGROUP sales

    BACKUP DATABASE mywind
       TO DISK ='e:\mywind.bak'
       WITH INIT
    GO

    USE mywind
    GO

    CREATE TABLE mywind..t1 (id int) ON new_customers
    CREATE TABLE mywind..t2 (id int) ON sales
    CREATE INDEX my_idx ON  mywind..t2(id) ON new_customers
    GO
    INSERT INTO  mywind..t1 (id ) VALUES (1)
    INSERT INTO mywind..t2 (id ) VALUES (6)
    GO
    BACKUP LOG mywind TO DISK='E:\mywind.BAK'WITH NOINIT
    GO
    DROP TABLE  mywind..t2 

    /*
    RESTORE FILELISTONLY FROM DISK='E:\mywind.bak'
    GO
    RESTORE HEADERONLY FROM DISK='E:\mywind.bak'

    */

    GO


    RESTORE DATABASE mywind_part 
       FILEGROUP = 'sales'
       FROM DISK='E:\mywind.bak' 
       WITH FILE=1,NORECOVERY,PARTIAL,
       MOVE 'mywind' TO 'E:\mw2.pri',
       MOVE 'mywind_log' TO 'E:\mw2.log',
       MOVE 'mywind_data_2' TO 'E:\mw2.dat2',REPLACE
    GO

    RESTORE LOG mywind_part 
       FROM DISK = 'e:\mywind.bak' 
       WITH FILE = 2,RECOVERY
    GO

    ----T2 is accessible after the partial restore operation.
    SELECT * FROM mywind_part..t2
    INSERT INTO mywind_part..t2 (id ) VALUES (10)
    --Msg 8607, Level 16, State 1, Line 1
    --The table 't2' cannot be modified because one or more non-clustered indexes reside in a filegroup which is not online.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 20, 2014 11:02 AM
    Answerer

All replies

  • Do you see the table on that filegroup but do not see the indexes created on that table? Can you issue BACKUP LOG before and after the deletion?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 17, 2014 7:26 AM
    Answerer
  • Yes, this happens, because once you have restored the filegroup, the transaction log is applied to the restored filegroup to give you a consistent database.

    That is, if you lose a file in a filegroup because of crashed disk, you can shorten the recovery time by restoring only the backup for that filegroup. If the backup was taken at time A, and the disk goes south on time B, the backup would be useless if you would get back the database on time A. Therefore SQL Server reapplies everything in the transaction log for the filegroup.

    Consider this scenario: say that the filegroup includes the Customers table, and there is an Order with a customer added after time A. If the filegroup was restored to time A and left that way, the order would now have an orphaned customer. That would be a serious breach of SQL Servers promising to uphold referential integrity.

    A consequence of this is that if you want to recover from an "oops", you will need to restore the entire database and apply transaction logs to the time before the oops.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Uwe RickenMVP Thursday, July 17, 2014 1:00 PM
    Thursday, July 17, 2014 8:27 AM
  • Hi.

    So in this way, If i keep all my indexes on one filegroup and then take a backup of this filegroup and reapply, it would not consider


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Thursday, July 17, 2014 9:19 AM
  • I do not think it is possible to have NCI  and table on separated file groups.. All you need as Erland pointed is to start backup a log file....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 17, 2014 9:31 AM
    Answerer
  • Now this is the thing I did.

    USE [master]
    GO
    IF EXISTS(select name from sys.databases where name = 'MSSD_LAB')
    BEGIN 
    DROP DATaBASE [MSSD_LAB]
    END
    CREATE DATABASE [MSSD_LAB] 
    GO
    USE [master]
    GO
    ALTER DATABASE [MSSD_LAB] ADD FILEGROUP [IDX_FG]
    GO
    ALTER DATABASE [MSSD_LAB] ADD FILE 
    ( NAME = N'MSSD_LAB_IDX', FILENAME = N'D:\MSSQLSERVER\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MSSD_LAB_IDX.ndf' , 
    SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [IDX_FG]
    GO
    USE [MSSD_LAB]
    GO
    CREATE TABLE TESTIDXBackup
    (
    Col_ClusIdx int
    ,Col_NClusIdx int
    ,Col_NoIdx varchar(10)
    ,Col_NoIdx1 nvarchar(10)
    ) ON [PRIMARY]
    GO
    --CREATE INDEX ON SECONDARY FILE GROUP
    CREATE CLUSTERED INDEX [IDX_Clus_TESTIDXBackup_Col_ClusIdx] ON [dbo].[TESTIDXBackup] 
    (
    	[Col_ClusIdx] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [IDX_FG]
    GO
    CREATE NONCLUSTERED INDEX [IDX_Clus_TESTIDXBackup_Col_NClusIdx] ON [dbo].[TESTIDXBackup] 
    (
    	[Col_NClusIdx] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [IDX_FG]
    GO
    USE master
    GO
    --BACKUP SECONDARY FILE GROUP
    BACKUP DATABASE MSSD_LAB
    File = 'MSSD_LAB_IDX',
    FILEGROUP = 'IDX_FG'
    TO DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\MSSD_LAB_IDX_FG.FLG'
    GO
    --BACKUP TRANSACTION LOG
    BACKUP LOG [MSSD_LAB] TO  DISK = N'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\TLogPostFGBackup.trn' 
    WITH NOFORMAT, NOINIT,  NAME = N'MSSD_LAB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO
    --DROP INDEXES ON SECONDARY FILEGROUP
    USE MSSD_LAB
    GO
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TESTIDXBackup]') AND name = N'IDX_Clus_TESTIDXBackup_Col_ClusIdx')
    DROP INDEX [IDX_Clus_TESTIDXBackup_Col_ClusIdx] ON [dbo].[TESTIDXBackup] WITH ( ONLINE = OFF )
    GO
    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TESTIDXBackup]') AND name = N'IDX_Clus_TESTIDXBackup_Col_NClusIdx')
    DROP INDEX [IDX_Clus_TESTIDXBackup_Col_NClusIdx] ON [dbo].[TESTIDXBackup] WITH ( ONLINE = OFF )
    GO
    --RESTORE SECONDARY FILE GROUP
    USE master
    GO
    ALTER DATABASE MSSD_LAB SET  SINGLE_USER with ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE MSSD_LAB
    File = 'MSSD_LAB_IDX',
    Filegroup = 'IDX_FG'
    FROM DISK = 'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\MSSD_LAB_IDX_FG.FLG'
    WITH NORECOVERY,  MOVE 'MSSD_LAB_IDX' TO 'D:\MSSQLSERVER\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\MSSD_LAB_IDX_FG.FLG'
    GO
    RESTORE LOG [MSSD_LAB] FROM  DISK = N'D:\MSSD_LAB\MSSQL_BACKUPS\Test_IDX_Backup\TLogPostFGBackup.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
    ALTER DATABASE MSSD_LAB SET  MULTI_USER with ROLLBACK IMMEDIATE
    

    Create Database

    Add Secondary FG

    Create Table On Primary

    Create Indexes on Secondary

    Take backup of secondary FG

    Take A TLOG backup

    Drop the Indexes

    Restore the FG backup taken before Deleting Index

    Restore the TLOG taken after FG backup

    Now, Indexes are not restored and I am getting addition below message, Not sure what to Roll FOrward

    Processed 8 pages for database 'MSSD_LAB', file 'MSSD_LAB_IDX' on file 1.
    Processed 5 pages for database 'MSSD_LAB', file 'MSSD_LAB_log' on file 1.
    BACKUP DATABASE...FILE=<name> successfully processed 13 pages in 0.029 seconds (3.367 MB/sec).
    100 percent processed.
    Processed 5 pages for database 'MSSD_LAB', file 'MSSD_LAB_log' on file 1.
    BACKUP LOG successfully processed 5 pages in 0.008 seconds (4.577 MB/sec).
    Processed 8 pages for database 'MSSD_LAB', file 'MSSD_LAB_IDX' on file 1.
    RESTORE DATABASE ... FILE=<name> successfully processed 8 pages in 0.029 seconds (2.155 MB/sec).
    100 percent processed.
    Processed 0 pages for database 'MSSD_LAB', file 'MSSD_LAB_IDX' on file 1.
    The roll forward start point is now at log sequence number (LSN) 19000000019800001. Additional roll forward past LSN 19000000024400001 is required to complete the restore sequence.
    RESTORE LOG successfully processed 0 pages in 0.005 seconds (0.000 MB/sec).

     :(


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Thursday, July 17, 2014 9:32 AM
  • If you see Above, I have backed the logfile but this isnt working too

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Thursday, July 17, 2014 9:49 AM
  • Thanks to Tibor, see

    USE master 
    IF DB_ID('fgr') IS NOT NULL DROP DATABASE fgr 
    GO 
    --Three filegroups 
    CREATE DATABASE fgr ON  PRIMARY  
    ( NAME = N'fgr', FILENAME = 'E:\fgr.mdf'),  
     FILEGROUP fg1  
    ( NAME = N'fg1', FILENAME = 'E:\fg1.ndf'),  
     FILEGROUP fg2  
    ( NAME = N'fg2', FILENAME = 'E:\fg2.ndf') 
     LOG ON  
    ( NAME = N'fgr_log', FILENAME = 'E:\fgr_log.ldf') 
    GO 
    ALTER DATABASE fgr SET RECOVERY FULL 

    --Base backup 
    BACKUP DATABASE fgr TO DISK = 'E:\fgr.bak' WITH INIT 
    GO 

    --One table on each filegroup 
    CREATE TABLE fgr..t_primary(c1 INT) ON "PRIMARY"
    CREATE TABLE fgr..t_fg1(c1 INT) ON fg1 
    CREATE TABLE fgr..t_fg2(c1 INT) ON fg2 

    --Insert data into each table 
    INSERT INTO fgr..t_primary(c1) VALUES(1) 
    INSERT INTO fgr..t_fg1(c1) VALUES(1) 
    INSERT INTO fgr..t_fg2(c1) VALUES(1) 
    CREATE INDEX my_idx ON  fgr..t_fg2(c1) ON fg2

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH INIT --1 

    --Filegroup backup of fg2 
    BACKUP DATABASE fgr FILEGROUP = 'fg2' TO DISK = 'E:\fgr_fg2.bak' WITH INIT 

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --2 

    --Drop from t_fg2 
    --Ths is our accident which we want to rollback!!! 
    DROP TABLE fgr..t_fg2 

    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --3 

    --Now, try to restore that filegroup to previos point in time 
    RESTORE DATABASE fgr FILEGROUP = 'fg2' FROM DISK = 'E:\fgr_fg2.bak' 
    GO 

    SELECT * FROM fgr..t_fg2 --error 8653 
    GO 
    BACKUP LOG fgr TO DISK = 'E:\fgr.trn' WITH NOINIT --4 


    --We must restore *all* log backups since that db backup 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 2 --out of 3 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 3 --out of 3 
    RESTORE LOG fgr FROM DISK = 'E:\fgr.trn' WITH FILE = 4 --out of 3 

    GO 

    --What we can do is restore into a new database instead, 
    --to an earlier point in time. 
    --We need the PRIMARY filegroup and whatever more we want to access 
    ---------------------------------------------------------------------------- 
    IF DB_ID('fgr_tmp') IS NOT NULL DROP DATABASE fgr_tmp 
    GO 
    RESTORE DATABASE fgr_tmp FILEGROUP = 'PRIMARY' FROM DISK = 'e:\fgr.bak' 
    WITH 
     MOVE 'fgr' TO 'e:\fgr_tmp.mdf' 
    ,MOVE 'fg2' TO 'e:\fg2_tmp.ndf' 
    ,MOVE 'fgr_log' TO 'e:\fgr_tmp_log.ldf' 
    ,PARTIAL, NORECOVERY 

    RESTORE DATABASE fgr_tmp FILEGROUP = 'fg2' FROM DISK = 'e:\fgr_fg2.bak' 

    RESTORE LOG fgr_tmp FROM DISK = 'e:\fgr.trn' WITH FILE = 1, NORECOVERY 
    RESTORE LOG fgr_tmp FROM DISK = 'e:\fgr.trn' WITH FILE = 2, RECOVERY 

    --Now the data in PRIMARY and fg2 is accessible 
    SELECT * FROM fgr_tmp..t_fg2 
    --The index I created before
    SELECT * FROM fgr_tmp.sys.sysindexes where id=object_id('fgr_tmp..t_fg2')


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Friday, July 18, 2014 9:00 AM
    • Marked as answer by Manjunath C Bhat Monday, July 21, 2014 11:00 AM
    Thursday, July 17, 2014 12:32 PM
    Answerer
  • I do not think it is possible to have NCI  and table on separated file groups..

    A non clustered index can be in a different filegroup, only a clustered index must be in the same FG.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 17, 2014 12:38 PM
  • Hi Manjunath,

    you need to get familiar with RECOVERY. Erland has hitten the nail with his explanation. You cannot restore the formerly backed up filegroup as a single option. It need to be "synchron" with all other changes which have taken in between. Therefore you need the tail log backup!

    Some other - important - information (because you seems to mix some issues)

    1. In the moment you create a CLUSTERED INDEX on a different filegroup you MOVE the table to that filegroup!

    2. The tail log backup need to be used with NORECOVERY

    Get datails concerning filegroup restore here:

    http://msdn.microsoft.com/en-us/library/ms179451.aspx

    This will explain FG-restore / piecemeal restore in detail and with an example!

    @Uri: YOu can have clustered index / heap and nci on different FG ;)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Thursday, July 17, 2014 1:06 PM
  • So in this way, If i keep all my indexes on one filegroup and then take a backup of this filegroup and reapply, it would not consider

    Not consider what? What do you really want to achieve? Let's start there and skip the scripts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 17, 2014 10:22 PM
  • Hi All,

    Sorry for delayed response.

    I just wanted to know whether Can I place all my indexes on a Different Filegroup. THIS yes I could do.

    Now can I backup only my indexes and not anything  else. This is what I was exploring.

    Sorry for all confusion :(


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Friday, July 18, 2014 10:37 AM
  • I just wanted to know whether Can I place all my indexes on a Different Filegroup. THIS yes I could do.

    Now can I backup only my indexes and not anything  else. This is what I was exploring.

    Yes you can, but would be the point? The indexes are recoverable, so it is not the most critical part of your database.

    A somewhat more relevant question would be, once you have your non-clustered indexes on a separate filegroup, could you reduce the backup size, by not backing up that filegroup and in case of a disaster instead rebuild that filegroup? (The answer is, yes, you can, but it takes quite some trickery. It does not work out of the box.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 18, 2014 11:07 AM
  • Now can I backup only my indexes and not anything  else. This is what I was exploring.

    And what will you achieve with this scenario?

    A separation of table (clustered index / heap) and indexes does make sence (eventually) if you have a heavy OLTP-System with extrem workload and different filegroups / files located on separate disks with separate controllers. Otherwise your scenario is worthless and you have to have much more administrative effort with non ROI :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Friday, July 18, 2014 11:46 AM
  • Here's a one sentence fact which is the bottom line to why this won't work for you (what I assume that you are after):

    SQL Server will never, using backup and restore, give you a database where different parts are from different points in time (indexes included).

    I.e., all data are always from the same point in time.


    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, July 19, 2014 7:40 PM
  • Clustered index means entire the table, but do you really able to have NCI and its table on separated file groups??? I have not seen this before...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 20, 2014 6:39 AM
    Answerer
  • Yes, you can separate the NC index(es) from the data (clustered ix or heap) using the ON clause in the CREATE INDEX command.

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, July 20, 2014 9:21 AM
  • Interesting, so I need to restore the file group of the "data" + file group of its indexes??? 

    I think if nci file group is offline  you cannot use a table for DML ,am I right?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 20, 2014 9:48 AM
    Answerer
  • Basically, whener you restore file/filegroup of a database (as in this case) you need transaction log restores to synchronize to a point in time.

    I *believe* that the data in a table can be available if you do the restore using PARTIAL without some of the NC indexes, but it was a while since I played with this. I'm Easy thing to test, but I'm sure somebody else knows this by heart... :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, July 20, 2014 10:44 AM
  • Tried to repoduce but failed, need couple more eyes to see what is going on :-))

    CREATE DATABASE mywind
    GO
    ALTER DATABASE mywind SET  RECOVERY FULL
    ALTER DATABASE mywind ADD FILEGROUP new_customers
    ALTER DATABASE mywind ADD FILEGROUP sales
    GO

    ALTER DATABASE mywind ADD FILE 
       (NAME='mywind_data_1',
       FILENAME='e:\mw.dat1') 
       TO FILEGROUP new_customers
    ALTER DATABASE mywind 
       ADD FILE 
       (NAME='mywind_data_2',
       FILENAME='e:\mw.dat2') 
       TO FILEGROUP sales

    BACKUP DATABASE mywind
       TO DISK ='e:\mywind.bak'
       WITH INIT
    GO

    USE mywind
    GO

    CREATE TABLE mywind..t1 (id int) ON new_customers
    CREATE TABLE mywind..t2 (id int) ON sales
    CREATE INDEX my_idx ON  mywind..t2(id) ON new_customers
    GO
    INSERT INTO  mywind..t1 (id ) VALUES (1)
    INSERT INTO mywind..t2 (id ) VALUES (6)
    GO
    BACKUP LOG mywind TO DISK='E:\mywind.BAK'WITH NOINIT
    GO
    DROP TABLE  mywind..t2 

    /*
    RESTORE FILELISTONLY FROM DISK='E:\mywind.bak'
    GO
    RESTORE HEADERONLY FROM DISK='E:\mywind.bak'

    */

    GO


    RESTORE DATABASE mywind_part 
       FILEGROUP = 'sales'
       FROM DISK='E:\mywind.bak' 
       WITH FILE=1,NORECOVERY,PARTIAL,
       MOVE 'mywind' TO 'E:\mw2.pri',
       MOVE 'mywind_log' TO 'E:\mw2.log',
       MOVE 'mywind_data_2' TO 'E:\mw2.dat2',REPLACE
    GO

    RESTORE LOG mywind_part 
       FROM DISK = 'e:\mywind.bak' 
       WITH FILE = 2,RECOVERY
    GO

    ----T2 is accessible after the partial restore operation.
    SELECT * FROM mywind_part..t2
    INSERT INTO mywind_part..t2 (id ) VALUES (10)
    --Msg 8607, Level 16, State 1, Line 1
    --The table 't2' cannot be modified because one or more non-clustered indexes reside in a filegroup which is not online.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 20, 2014 11:02 AM
    Answerer
  • As I indicated earlier, you restore the database without the NC indexes, and then recreate them, but it takes some hacking. Greg Linwood wrote about it in Volume 1 of MVP Server Deep Dives,
    http://www.sqlservermvpdeepdives.com/.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 20, 2014 9:59 PM
  • Awesome, THIS worked like charm.

    So, Bottom line we can have all of our Index on separate FileGroup and these can be backed up


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Monday, July 21, 2014 11:03 AM
  • Hi, Yes, actually that is also one of the Point. if I have a Large database and want to move it or copy it to some other location, I can exclude this Index FG and take backup of only Data which will help me reduce the size of backup

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Monday, July 21, 2014 11:04 AM
  • Hi.

    yes actually,

    I was able to have table created on PRIMARY FG and CI and NCI on separate FG. 


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Monday, July 21, 2014 11:06 AM
  • Yes, I cannot use it, That is what bothering me how to bring it online

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Monday, July 21, 2014 11:07 AM
  • Hi, Yes, actually that is also one of the Point. if I have a Large database and want to move it or copy it to some other location, I can exclude this Index FG and take backup of only Data which will help me reduce the size of backup

    Please beware that will not work out of the box. Make sure that you read Chapter 33 in this book:
    http://www.sqlservermvpdeepdives.com/

    (Author royalties for the book goes to WarChild international, not to be authors.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 21, 2014 10:06 PM