| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-06-12 : 17:48:02
|
| I currently have a nightly process that backs up a database, restores it with a different name, then scrubs out some of the data and backs up the scrubbed database. The problem with this is that the database has gotten too large for the development environments so I need to take a subset of the data. Deleting data from the tables and using SHRINKFILE takes too long (an hour or two), even if I only shrink by 100 MB at a time. I tried following what some people suggested and creating an SSIS package with the Transfer SQL Server Objects task, but it fails because there are a couple views that reference a different database on the server. I've set everything in the task to True and nothing helps. What can I do? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-12 : 18:03:11
|
| how many tables worth of data are we talking about here?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-12 : 19:20:47
|
| Around 25 right now, why? I don't want to have to update the job every time the schema changes if that's what you're getting at. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-12 : 20:28:13
|
| It's not just space that's the issue, it's time as well. Adding storage is a last resort. Isn't there anything else I can do? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-12 : 20:41:05
|
| That seems reasonable. I'll try that, thanks. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-13 : 09:57:29
|
| Tara's approach sounds reasonable. I was thinking some sort of replication,subscription thing<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-06-13 : 15:57:19
|
| Wait, maybe that doesn't help. If I have a task that copies over the objects that have external dependencies first, how do I tell the Transfer Objects task to then copy all the other objects without specifying each object? The goal is to not have to modify the job every time the schema is changed. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-13 : 17:12:03
|
| you can use sys views to dump to a tableList then you process uses that. this makes it dynamic<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
copy all objects to shrink and scrub