March 4, 2009 at 1:35 pm
Looking for a way to copy (nightly) a large database from prod to dev, staging, QA, etc.... We are having a hard time justifying all the space taken up in the nonprod environments.
We have a massive table (250g), that does not need to be brought to the other environments. Does anyone know of an easy way of doing this? I was thinking about creating another file group that only had this table. Is there a way to backup\restore without a specific filegroup? Detach\Attach perhaps?
It's taking hours to truncate and shrink the database in the other environments. Plus to do a restore we need the same space as required in prod.
Any help\idea's would be great!!!
March 5, 2009 at 12:44 am
Backup can be done for specific file groups. Check BackUp Database in BOL for file group back up for more details.
Example
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:\SQLServerBackups\SalesFiles.bck'
GO
-Roy
March 5, 2009 at 4:57 am
OK, so if I have 4 filegroups and only wanted to backup\restore 3 I could do that?
What about with a detach\attach? In some cases we are using 3Par SAN "snap" to replicate the drives that the DB reside.
March 5, 2009 at 8:26 am
You can create the database on the dev environment and export all the tables but that one on a nighty basis. Be sure your transformation deletes data on the destination tables. Keep in mind FK constraints (if you have any) could possibly cause issues if your not moving the table.
-- You can't be late until you show up.
March 5, 2009 at 8:28 am
Moving the data is not really an option. We're talking 500g that would need to be transported over the wire.
Differrent datacenters
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply