Backup & Restore partially - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Backup & Restore partially

Author

Topic

CanadaDBA

583 Posts

Posted - 2009-11-06 : 10:16:43

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?

Canada DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-06 : 16:09:15

If you do not have enough space in your dev environment to do a full restore, you will not be able to use backup and restore to refresh the database.

CODO ERGO SUM

CanadaDBA

583 Posts

Posted - 2009-11-25 : 09:35:31

I understand the above does not work and I have to have more space and full backup of the database. Now:

Every night a backup of the database is being created on tape. We are using Symantec Backup Exec 12.5 for that reason.

Currently the available space on my Prod hard disks is half space of the database size; if I want to create a backup on the hard using SQL native backup it doesn't fit. But a SysEng suggests to use a 1TB external hard drive (USB) to have a Full backup of the database, then move the hard to the Dev and restore it. My argument with this is that the process would be too slow to be feasible. Also, I don't think it might fit in the Dev hard disks. But anyhow, does the idea work?

1. What is the best way to backup the database using the above hardware?

2. What is the best way regarding backup/restore of such big databases?

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources