d52baf8b   
   From: esquel@sommarskog.se   
      
   joshsackett (joshsackett@gmail.com) writes:   
   > 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?   
      
   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@sommarskog.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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)