locked
Filegroup restore on a different database in SQLServer 2005 RRS feed

  • Question

  • Hi,

    I have a database DB1, which has multiple filegroups and each filegroup has 1 file each. I am trying to take the back up of 1 file group say FG1 from DB1 and restore the same filegroup to the database DB2, which has all the objects same as DB1 and they are empty (this database DB2 just holds the same schema as DB1 but without any data). I am not able to do this and I am getting error as following:

     

    Msg 3154, Level 16, State 4, Line 1

    The backup set holds a backup of a database other than the existing 'DB2' database.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

     

    Is there any possibility to back up a filegroup from one database and restore that in a different new database? If so, then what are the minimum requirements for this job to be done?

    Thanks for your help...

    Uday

     

    Script I am using as below:

    -------------- CREATE DB1 DATABASE Script ---------------

    USE [master]

    GO

    CREATE DATABASE [DB1] ON PRIMARY

    ( NAME = N'DB_1', FILENAME = N'E:\Data\DB1.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),

    FILEGROUP [FG1]

    ( NAME = N'FG_DT1', FILENAME = N'E:\Data\FG_DT1_DB1.ndf' , SIZE = 10176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),

    FILEGROUP [FG2]

    ( NAME = N'FG_DT2', FILENAME = N'E:\Data\FG_DT2_DB1.ndf' , SIZE = 6912KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

    LOG ON

    ( NAME = N'DB1_log', FILENAME = N'L:\LOG\DB1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    EXEC dbo.sp_dbcmptlevel @dbname=N'DB1', @new_cmptlevel=90

    GO

    -------------- CREATE DB2 DATABASE Script ---------------

    USE [master]

    GO

    CREATE DATABASE [DB2] ON PRIMARY

    ( NAME = N'DB_2', FILENAME = N'E:\Data\DB2.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),

    FILEGROUP [FG1]

    ( NAME = N'FG_DT1', FILENAME = N'E:\Data\FG_DT1_DB2.ndf' , SIZE = 10176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),

    FILEGROUP [FG2]

    ( NAME = N'FG_DT2', FILENAME = N'E:\Data\FG_DT2_DB2.ndf' , SIZE = 6912KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

    LOG ON

    ( NAME = N'DB2_log', FILENAME = N'L:\LOG\DB2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    EXEC dbo.sp_dbcmptlevel @dbname=N'DB2', @new_cmptlevel=90

    GO

    ---------------------- ADD a table to DB1 / FG1 --------------------

    USE DB1

    GO

    CREATE TABLE T1(ID INT IDENTITY(1,1), FName NVARCHAR(20)) ON FG1

    INSERT T1(FName) SELECT 'Name1'

    GO

    ---------------------- Backup the filegroup FG_DT1 -------------------

    USE MASTER

    GO

    BACKUP DATABASE [DB1]

    FILEGROUP = N'FG1'

    TO DISK = N'E:\Backup\DB1_Full_FilegroupFG1Backup.BAK'

    WITH NOFORMAT, INIT,

    NAME = N'DB1-Full_Filegroup_Backup',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    ------------------ RESTORE SCRIPT ----------------------

    USE MASTER

    GO

    RESTORE DATABASE [DB2]

    FILE = N'FG_DT1'

    FROM DISK = N'E:\Backup\DB1_Full_FilegroupFG1Backup.BAK'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

     

    Saturday, November 8, 2008 2:00 AM

Answers

  • To my knowledge, to do so would require that DB2 was created from a full backup of DB1, and that you have the filegroup backup and all of the Transaction Log Backups from the full that created DB2 to bring the database and filegroup to a consistent state.  Without this, you can't do what you are asking.  If all that you want is to move the data, then a SSIS package should do the job the best.

    Saturday, November 8, 2008 3:58 AM

All replies

  • To my knowledge, to do so would require that DB2 was created from a full backup of DB1, and that you have the filegroup backup and all of the Transaction Log Backups from the full that created DB2 to bring the database and filegroup to a consistent state.  Without this, you can't do what you are asking.  If all that you want is to move the data, then a SSIS package should do the job the best.

    Saturday, November 8, 2008 3:58 AM
  • Thanks Jonathan for your reply on the full backup solution. In fact the purpose I need this file group back up to avoid the full backup and restore operation as our database is huge and it will take hours for this operation. We want to use a process for archiving the old monthly data which is in a monthly filegroup and which has corresponding data files which holds only the monthly data on a partitioned table. We want to backup this filegroup and store that and free the partition from the monthly table and restore it later on a different database either on the same server or a different server when we need the old data for investigation/reporting purposes. The new database could be from the backup of this database, but the sate of the database will not be same as the filegroup backup and complete backup could have been taken on different time frames.

    The option of the new database could be generated from the script which would create the whole database similar to the original database without any data on it and we want to restore this filegroup on this new generated database. After creating this database we want to restore the filegroup which we had backed up from the main database.

    Please let me know if this is at all possible using the filegroup backup or not. Or is there any other option available for the same purpose?

    In the other possible option we have is to take the full backup of the database and restore that in a different server and then truncate the data from the partition table which we do not want to archive and then take the full backup and store it! But this is lots of work and can not be automated.

    I need a solid workable option for this which can be automated too.

     

    (Pl. ignore the typo if any...)

    Thanks again.

    Saturday, November 8, 2008 5:12 AM