Sign in
Microsoft.com
 
SQL Server TechCenter
 
 
 
SQL Server TechCenter > SQL Server Forums > SQL Server Disaster Recovery and Availability > Backup & Restore partially
Ask a questionAsk a question
Search Forums:
 

AnswerBackup & Restore partially

  • Friday, November 06, 2009 8:20 PMCanadaDBA Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Has Code
    Sign In to Vote
    0
    Sign In to Vote
    I have a 1TB database that inherited. The database has 1 mdf file, 55 ndf, and 1 ldf. It is set to simple recovery mode. The database is on a SQL Server 2005 EE 64-bit.

    Three files are PRIMARY filegroup. And the rest each file has a unique filegroup name.

    Sp_helpfile returns the following. (I have deleted some extra identical rows for "Nocks?" to keep the look simpler.) The database name is RIM. 
    
name	fileid	filename	        filegroup  size         maxsize	        growth          usage

    
ROE	1	E:\MDFS\ROE.mdf	        PRIMARY	230066688 KB	Unlimited	102400 KB	data only
ROE_log	2	F:\LDFS\RIM_log.LDF	NULL	2203520 KB	Unlimited	10%	        log only
Nocks1	3	E:\MDFS\Nocks1.ndf	Nocks1	3072 KB	        Unlimited	10%	        data only
Nocks15	17	E:\MDFS\Nocks15.ndf	Nocks15	7987520 KB	Unlimited	10%	        data only
Nocks16	18	E:\MDFS\Nocks16.ndf	Nocks16	8226688 KB	Unlimited	10%	        data only
Nocks17	19	E:\MDFS\Nocks17.ndf	Nocks17	8218816 KB	Unlimited	10%	        data only
Nocks18	20	E:\MDFS\Nocks18.ndf	Nocks18	9926592 KB	Unlimited	10%	        data only
Nocks53	55	E:\MDFS\Nocks53.ndf	Nocks53	3072 KB	        Unlimited	10%	        data only
RIM	56	G:\MDFS\RIM.ndf	        PRIMARY	40758272 KB	61440000 KB	102400 KB	data only
RIM2	57	F:\LDFS\RIM2.ndf	PRIMARY	40859648 KB	40960000 KB	102400 KB	data only

    I want to refresh our Dev environment but there is not enough space on Dev that I can backup the prod database and restore into Dev. Therefore, I was asked to refresh only ROE, Nocks15, and Nocks20.

    The following command created a bak file and I moved to Dev. 
    
BACKUP DATABASE RIM  
	FILE = 'ROE',
	FILE = 'Nocks15'
   TO DISK = 'H:\Nocks15.bak'

    But in Dev get an error when run the following:
    
RESTORE DATABASE RIM
FILE = 'Nocks15', 
FROM DISK = 'G:\Nocks15.bak'
WITH RECOVERY






    Msg 3004, Level 16, State 2, Line 1
    The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    The Dev environment is set to simple recovery mode as well. How can I implement this?
    Thanks,
     

Answers

  • Saturday, November 07, 2009 1:01 PMEwan Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    1
    Sign In to Vote
    Hi CanadaDBA

    This is a variation on a common question on these forums. I'm afraid the answer to your question is 'it can't be done'

    Tibor has a blog post on this topic in general at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx

    Even if you changed the source database so you could do a variation of this (ie make the filegroups that you didn't want to restore read-only, enabling a partial restore), SQL would still attempt to initialise the target read-only files. This means that the required disk-space on your Dev database is the same...

    HTH

    Ewan
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked As Answer byCanadaDBA Monday, November 09, 2009 12:59 PM
    •  
     

All Replies

  • Saturday, November 07, 2009 1:01 PMEwan Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    1
    Sign In to Vote
    Hi CanadaDBA

    This is a variation on a common question on these forums. I'm afraid the answer to your question is 'it can't be done'

    Tibor has a blog post on this topic in general at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx

    Even if you changed the source database so you could do a variation of this (ie make the filegroups that you didn't want to restore read-only, enabling a partial restore), SQL would still attempt to initialise the target read-only files. This means that the required disk-space on your Dev database is the same...

    HTH

    Ewan
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked As Answer byCanadaDBA Monday, November 09, 2009 12:59 PM
    •