Read-only filegroup restore. - 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)

Read-only filegroup restore.

Author

Topic

dn014030
Starting Member

3 Posts

Posted - 2003-02-12 : 17:05:58

Does anyone know how to backup a read-only filegroup and restore it on another database without the recovery of transaction logs ? I attempted this and was unsuccessful.

BACKUP DATABASE CIR
FILE = 'CIR_data8',
FILEGROUP = 'fg_PLS'
TO DISK = '\\SQLDUMP01\SQLDUMP\SQLPRO01\CIR_fg2.bak'
** This one ran OK !!

RESTORE DATABASE CIR1
FILE = 'CIR_data8',
FILEGROUP = 'fg_PLS'
FROM DISK = '\\SQLDUMP01\SQLDUMP\SQLPRO01\CIR_fg2.bak'
WITH RECOVERY

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.

Here's what Microsoft has on their BookOnline.

"Microsoft® SQL Server™ 2000 allows filegroups to be marked as read-only. Any existing filegroup, except the primary filegroup, can be marked as read-only. A filegroup marked read-only cannot be modified in any way.

Place tables that must not be modified, such as historical data, on filegroups, and then mark the filegroup as read-only. This prevents accidental updates. The read-only filegroup can then be backed up and restored on another instance of SQL Server without concern for recovery of transaction logs."

Thanks in advance.

David.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-12 : 17:47:50

You can not use the WITH RECOVERY option when restoring files or filegroups. I verified this by looking at BOL. BOL also says about restoring files or filegroups that you must also apply a transaction log.

Edited by - tduggan on 02/12/2003 17:48:45

dn014030
Starting Member

3 Posts

Posted - 2003-02-12 : 19:36:08

Per BOL, you should be able to with read-only filegroups. But it didn't show how.

"Microsoft® SQL Server™ 2000 allows filegroups to be marked as read-only. ...
The read-only filegroup can then be backed up and restored on another instance of SQL Server without concern for recovery of transaction logs."

David.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources