Storing tables in different file groups?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

Database Design and Application Architecture

Storing tables in different file groups??

Author

Topic

CraigL77
Starting Member

4 Posts

Posted - 2009-10-22 : 18:48:58

SQL2000 Enterprise Edition here...

I have a situation where we have a 600GB database in production. In development we only need the database structure and a few tables in full. There is about 300GB of data that we don't care about for development, but currently we just backup the whole database and ship it over to another box. Obviously this takes up tons of space when you consider we have the database copied 2 or 3 times in DEV and at least 2 times in QA.

I am looking for a minimal impact solution that will allow me to backup the database but have it not include those tables we don't care about (logging tables).

I have messed around with storing those unneeded tables in a different filegroup, but I cant get a restore to work when I just back up the PRIMARY filegroup. The database always says its in the middle of a restore.

Other than copying the data/objects off individually to a blank database I cant think of any other solutions that would work on SQL 2000.

Does anyone have any other approaches that would work here?

Here are the scripts I have tried in regards to filegroups:

CREATE DATABASE MOVETABLETOFILEGROUP
GO
USE MOVETABLETOFILEGROUP
GO
--DROP TABLE xyz
CREATE TABLE xyz( ID int IDENTITY(1,1) , v varchar(500), x varchar(500)) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX PK_ID ON xyz (id) ON [PRIMARY]

DECLARE @i int
SET @i = 0
WHILE @i < 10000
BEGIN
INSERT INTO xyz SELECT REPLICATE('a', 500), REPLICATE( 'b', 500)
SET @i = @i + 1
END
GO

ALTER DATABASE [MoveTableToFileGroup] ADD FILEGROUP [SECONDARY]
ALTER DATABASE [MoveTableToFileGroup] ADD FILE ( NAME = N'Test2', FILENAME = N'c:\Test2.ndf' , SIZE = 1024KB , FILEGROWTH = 10%) TO FILEGROUP [SECONDARY]
GO
CREATE UNIQUE CLUSTERED INDEX PK_ID ON xyz (id) WITH DROP_EXISTING ON [SECONDARY]
DBCC SHRINKFILE(1)
GO
BACKUP DATABASE MoveTableToFileGroup
FILEGROUP = 'PRIMARY'
TO DISK = 'c:\Primary.bak'
WITH FORMAT,NO_LOG,COMPRESSION
GO
RESTORE DATABASE [MoveTableToFileGroup2]
FILE = N'MoveTableToFileGroup'
FROM DISK = N'c:\Primary.bak'
WITH FILE = 1,
MOVE N'MoveTableToFileGroup' TO N'c:\MoveTableToFileGroup2.mdf',
MOVE N'MoveTableToFileGroup_log' TO N'c:\MoveTableToFileGroup_log2.ldf',
NOUNLOAD, REPLACE, STATS = 10

GO
USE MoveTableToFileGroup2
GO

Results:

Msg 927, Level 14, State 2, Line 1
Database 'MoveTableToFileGroup2' cannot be opened. It is in the middle of a restore.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-22 : 22:15:09

You should look into Red Gate's SQL Backup tool which not only gives you backup compression but also gives you backup/restore of individual tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

CraigL77
Starting Member

4 Posts

Posted - 2009-10-23 : 12:58:32

I was kinda looking for something that would backup everything *but* a few tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources