Restore From File Group - 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 2000 Forums

SQL Server Administration (2000)

Restore From File Group

Author

Topic

shaankit
Starting Member

4 Posts

Posted - 2003-03-03 : 12:17:29

Hello,

I would like to do a restore from file group for one of my databases. I was able to do a restore when I had the database under FULL recovery model. Is there a way to restore from file groups under the "SIMPLE " recovery model ie without performing a transaction log.

Pleas help. Thanks

Shaan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-03 : 12:37:40

From Books Online:

"FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
Names one or more filegroups to include in the database restore.
When this option is used, the transaction log must be applied to the database files after the last file or filegroup restore operation to roll the files forward to be consistent with the rest of the database. If none of the files being restored have been modified since they were last backed up, a transaction log does not have to be applied. The RESTORE statement informs the user of this situation. "

Tara

shaankit
Starting Member

4 Posts

Posted - 2003-03-03 : 15:44:04

Here are the steps that I have tried:
Step 1: Complete backup.
Step 2: I deleted 1 table.

Now I want to restore the database from the old backup without the transaction log.

Step 3:

RESTORE FILELISTONLY FROM DISK='F:\BACKUP\APS_TEST.BAK'
GO
RESTORE HEADERONLY FROM DISK='F:\BACKUP\APS_TEST.BAK'
GO

result:

APS_TESTD_SYS E:\data\APSTESTDdat1.mdf D PRIMARY 786432 35184372080640
APS_MAN1D E:\data\APS_MAN1D.ndf D APS_MAN1D 2686976 35184372080640
APS_IMAN1 E:\index\APS_IMAN1.ndf D APS_IMAN1 1048576 35184372080640
MANTABLES E:\DATA\MANTABLES.ndf D MANTEST 10485760 35184372080640
APS_TEST_LOG E:\tlogs\APSTESTlog.ldf L NULL 11534336 35184372080640

Step 4:I want to restore this to a new database Man_tables or should I restore it to the original database.

RESTORE DATABASE MAN_TABLES
FILEGROUP='MANTABLES'
FROM DISK='F:\BACKUP\APS_TEST.BAK'
WITH FILE=1, RECOVERY,
MOVE 'MANTABLES' TO 'C:\MANRESTORE.DAT'
GO

This is the message I get:

Server: Msg 3032, Level 16, State 2, Line 1
One or more of the options (recovery) are not supported for this statement. Review the documentation for supported options.

Please help. Thanks

Shaan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-03 : 15:58:28

You must apply a transaction log. That is what BOL says that you have to do. So do your restore WITH NORECOVERY, then apply a transaction log and use WITH RECOVERY

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources