Hi all,
I have a 260GB database with a single read/write table taking up
200GB. I create a weekly FULL backups and nightly DIFF backups with
TRAN backups occurring every 15 minutes. Part of my job is to restore
the FULL backup to various points in our environment: for Developers,
for QA staff, as source data for our ETL system, etc. None of these
restore points require that 200GB table.

I had the idea to move the 200GB table (let's call it LargeTable) into
its own filegroup. My thought process is that I can now backup the
PRIMARY filegroup and the LARGETABLE filegroup separately and only
have to move the PRIMARY filegroup backup around my network. However,
if I restore just the PRIMARY filegroup I cannot execute the RECOVER
command without it failing due to there being missing TRAN backups. Do
I have to move around all my nightly DIFF and TRAN backups too?

Can anyone here recommend another solution?

Thanks in advance.

joshsackett (joshsackett (AT) gmail (DOT) com) writes:
SQL Server will under no cicrumstances let you use a database that
it cannot guarantee that it is transactionally consistent. It is
OK if a table is not restored, as long as you don't reference it.

When you say that the 200 GB table is not needed, do you mean that
there is no need to refer to the table at all in the other environments,
or do you mean that in those environments, you prefer the table to be
empty?

Here is a script that shows that you can do what you ask for, when taken
by the letter. In the script I use the COPY_ONLY option. This option is
not needed, but it is good practice to use this option when you take
an out-of-band backup to copy data to another environment.

CREATE DATABASE joshtest ON
(NAME = 'joshtest', FILENAME = 'C:\temp\joshtest.mdf'),
FILEGROUP largetable
(NAME = 'largetbl', FILENAME = 'C:\temp\joshtest_large.ndf')
LOG ON (NAME = 'joshtest_log', FILENAME = 'C:\temp\joshtest.ldf')
go
ALTER DATABASE joshtest SET RECOVERY FULL
go
USE joshtest
go
CREATE TABLE smalltable (a int NOT NULL)
INSERT smalltable(a) VALUES (88899)
go
CREATE TABLE largetable (a int NOT NULL,
filler char(7000) NOT NULL DEFAULT ' ')
ON largetable
go
INSERT largetable(a)
SELECT object_id FROM sys.objects
go
INSERT smalltable(a) VALUES (9001)
go
BACKUP DATABASE joshtest TO DISK = 'c:\temp\test.bak' WITH INIT
go
INSERT smalltable(a) VALUES (900165)
INSERT largetable(a)
SELECT -object_id FROM sys.objects
INSERT smalltable(a) VALUES (-900165)
go
BACKUP DATABASE joshtest
FILEGROUP = 'PRIMARY'
TO DISK = 'c:\temp\test2.bak' WITH INIT, COPY_ONLY
go
USE tempdb
go
RESTORE DATABASE joshcopy FROM DISK = 'c:\temp\test2.bak'
WITH MOVE 'joshtest' TO 'C:\temp\joshcopy.mdf',
MOVE 'joshtest_log' TO 'C:\temp\joshcopy.log',
REPLACE, RECOVERY
go
USE joshcopy
go
SELECT * FROM smalltable
go
SELECT * FROM largetable
go
USE tempdb
go
DROP DATABASE joshtest
DROP DATABASE joshcopy





--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks! I will get right on this.

You could do a partial backup/restore. You have to have the tables you don't want to restore in both a separate file and separate filegroup. Just put the tables you want to restore in one file/filegroup, the ones you don't want to restore in a separate filegroup.

http://mssql.meetholland.com/message/30161.aspx

Bender