October 24, 2001 at 3:11 pm
I am using SQL7SP2 on NT4SP5.
I have a database where one of the tables should be strictly read only as the data in it is static except for a monthly load. There are over 80 million rows currently in this table. The table and it's indexes are in their own filegroup - say called "filegroup_2". There is also a 3rd filegroup for historical data ("filegroup_3"). Currently these filegroups are not set to read only (I'll get to that point once I can backup the PRIMARY filegroup). The transaction log on the database is currently set to truncate on checkpoint.
Here is my dilemma - I want to be able to backup the primary filegroup on a daily basis and not backup "filegroup_2" or "filegroup_3". I am unable to backup individual filegroups unless I enable the transaction log for the whole DB. So, I tried enabling the transaction log and then backing up only the primary filegroup without "filegroup_2" or "filegroup_3" and I get an error.
Here is the code I am trying to run:
USE master
EXEC sp_dboption 'prod_database', 'trunc. log on chkpt.', 'FALSE'
go
which results in:
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
then:
BACKUP DATABASE prod_database
FILE = 'prod_Data',
FILEGROUP = 'PRIMARY'
TO DAILY_BACKUP
GO
which results in:
Server: Msg 3029, Level 16, State 1, Line 1
Database file 'filegroup_2' is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3029, Level 16, State 1, Line 1
Database file 'filegroup_3' is subject to logical recovery and must be among the files to be backed up as part of the file or filegroup backup.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Can anyone give me an idea about how I am to go about this? I do not want to backup the historical data (which is about 5 GB, or the data in filegroup_2, which is about 80GB) every day. I just want to backup the tables and indexes in the PRIMARY filegroup, which is about 1 GB worth of stuff.
Should I have put these tables in different databases instead of different filegroups?
Related question - If I set filegroup_2 and filegroup_3 to be read only and I have auto_update_statistics turned on is there going to be a problem? When I load new data into the tables in these filegroups, I will turn the filgroups back to readwrite, load the data, update statistics and then turn the filegroups back to readonly. One issue I see is that to change the filegroups from read_only to readwrite and back I need to have exclusive use of the WHOLE database, not just the filegroup. This is what makes me think I should have used separate databases instead of filegroups. Any comments or suggestions? Does anyone see something I am missing here?
Thanks for any help you can offer.
Mindy Curnutt
October 24, 2001 at 6:51 pm
Hey Mindy,
It does seem that separate db's would solve the problem. I rarely use filegroups, I'll try to find time tomorrow to experiment a little. Steve, you got any ideas?
Andy
October 24, 2001 at 7:02 pm
off the top of my head, nothing. Can you post the ddl for the db. I'll try some experiments.
Steve Jones
October 25, 2001 at 8:36 am
Heres the DDL for the DB, you will probably have to lower the size of filegroup_1 as it is huge to hold that 80M row table.
CREATE DATABASE [prod_data] ON
(NAME = N'prod_data_Data',
FILENAME = N'F:\MSSQL7\data\prod_data_Data.MDF' ,
SIZE = 1635,
FILEGROWTH = 100)
LOG ON
(NAME = N'prod_data_Log',
FILENAME = N'F:\MSSQL7\data\prod_data_Log.LDF' ,
SIZE = 2187,
FILEGROWTH = 500)
GO
ALTER DATABASE [prod_data] ADD FILEGROUP [filegroup_1]
GO
ALTER DATABASE [prod_data] ADD FILE
(NAME = N'prod_data_filegroup_1',
FILENAME = N'F:\MSSQL7\data\prod_data_filegroup_1_Data.NDF' ,
SIZE = 89028,
FILEGROWTH = 500)
TO FILEGROUP [filegroup_1]
GO
ALTER DATABASE [prod_data] ADD FILEGROUP [filegroup_2]
GO
ALTER DATABASE [prod_data] ADD FILE
(NAME = N'prod_data_filegroup_2',
FILENAME = N'F:\MSSQL7\data\prod_data_filegroup_2_Data.NDF' ,
SIZE = 876,
FILEGROWTH = 100)
TO FILEGROUP [filegroup_2]
GO
exec sp_dboption N'prod_data', N'autoclose', N'false'
GO
exec sp_dboption N'prod_data', N'bulkcopy', N'true'
GO
exec sp_dboption N'prod_data', N'trunc. log', N'true'
GO
exec sp_dboption N'prod_data', N'torn page detection', N'false'
GO
exec sp_dboption N'prod_data', N'read only', N'false'
GO
exec sp_dboption N'prod_data', N'dbo use', N'false'
GO
exec sp_dboption N'prod_data', N'single', N'false'
GO
exec sp_dboption N'prod_data', N'autoshrink', N'true'
GO
exec sp_dboption N'prod_data', N'ANSI null default', N'false'
GO
exec sp_dboption N'prod_data', N'recursive triggers', N'false'
GO
exec sp_dboption N'prod_data', N'ANSI nulls', N'false'
GO
exec sp_dboption N'prod_data', N'concat null yields null', N'false'
GO
exec sp_dboption N'prod_data', N'cursor close on commit', N'false'
GO
exec sp_dboption N'prod_data', N'default to local cursor', N'false'
GO
exec sp_dboption N'prod_data', N'quoted identifier', N'false'
GO
exec sp_dboption N'prod_data', N'ANSI warnings', N'false'
GO
exec sp_dboption N'prod_data', N'auto create statistics', N'true'
GO
exec sp_dboption N'prod_data', N'auto update statistics', N'true'
GO
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply