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.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-03-12 : 11:21:47
|
| We have a production Datawarehouse(remote location) lets say P1. This is baked up differentially and restored weekly (locally) lets call it R1.This takes about few hours for the restore. Now, there is another DB which is a normalized/modified version of R1, lets call it D1.After the restore of R1, D1 also has to be restored. This is done monthly.The problem is this process takes more than couple days. Since the differential data is not normalized and is not integral (from R1), the process probably takes so long because of all the checks and then the appending.Question: Is this behaviour normal?Would there be better methods apart from checking the restoreDTS packages?Are there any general guidelines/shortcuts for the restore.In the past I have used the disabling of all the constraints and triggers for faster updates and then enabled them back after the restore.But this was for dev to staging where the stucture was identical.We use SQL2000 Enterprise editionDB size:P1 ~= 20G R1 ~= 30G D1 ~= 60G |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-12 : 11:35:05
|
| When you say restore do you mean restoring a backup?R1 = datawarehouse, D1 = normailised versionR1 = 30G, D1 = 60GDoesn't sound right.I have a feeling you are creating D1 from R1 - if that is the case you will have to look at the processes you are using to do it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-12 : 11:40:16
|
| Yup, D1 has further broken down tables, more indexes and all that.Any ideas as to how the processes can be optimized or any known big red flags/bad practices?TIA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-12 : 12:01:17
|
| It's probably a matter of looking at the code you are using - see if any indexes could be useful or if existing ones are being used and that sort of thing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Datawarehouse back up restored on normalized DB