March 8, 2016 at 3:49 am
Hi Professionals
We have a schadule task that I have to 1- Backtup of main database 2-Update Critical data 3-Delete Old Records And last 4- Copy to Local server and restore it For developers.
Because according to our bussiness , developers should have last schema and 3 month Read Data .
But this task is very time consuming.
Particular delete records and disable cinstraints is out of my tolerance.
I need a method that do this with more speed.
Please Help me.
Thank you
March 8, 2016 at 7:21 am
I don't know of a sure way to speed that up. We used to do the same thing in this order:
1. Restore production backup to a staging server
2. Clean the data (we didn't have to delete, but we had massive updates to mask private data)
3. Shrink the database (it's not production, it's OK to shrink stuff now)
4. Backup the clean, shrunk database (this was faster because of the updates and reduction in data)
5. Restore that database to development environments
The only thing that sped up any part of the process was the choice to shrink the database. The restore operation then allocates a smaller database, not the full production one, making things quite a bit faster. All this was automated. It served two purposes. One, we tested our production backup through the restore process. Two, we got a database we could distribute to development and testing.
There were no other shortcuts that I knew about to shave down this process. The other approach we sometimes used was to have a blank database and then a data load process from a maintained data set. That was faster than our process above, but the data wasn't as accurate as "real" production data. Plus, the process of maintaining the data load was time consuming.
The trade-offs were between an easy to maintain process that was slow but provided us with more accurate data distributions or, a difficult to maintain process that was very fast but had less accurate data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2016 at 12:46 am
hi again
Thank you "Grant Fritchey"
But I encounter with some limitations:
1- our main server is on the internet and local server obviously is on local network. Then After all tasks (which you mentioned) I should copy a compressed backup from internet to local network which without deleting old data is about 80 GB And take me about 5 hours to copy. It is very long time. And If we forget the time , I dont have enough disk space to restore complete dataabse . because we have 7 or 8 branches in parallel and some times I have to restore in 7 times.
>>>>>>>>>>> Then First Result is : Only New data should be transfered. (Last 3 month)
2- When I want to delete data Some another problems appear. 1- Foreign keys 2- Huge time to delete records . Then I should Change Constraints to NOCHECK then start to delete data. But it is approximately impossible. Because only for one table that has about 15 gigabyte delete records befor last 3 month took me about 2 hours and finaly I was forced to Cancel it. :crazy:
>>>>>>>>>>> Then Sedcond Result is : Delete records is very very time consuming and impossible
3- Recently I thought that Truncate 10 Huge table after that copy 3 last moth data from the same table of main database .
>>>>>>>>>>> Third result : truncate is impossible when you have FK even if you nochecked it.
4- in continue the 3th : I deleteded all FK for 10 huge tables. Then Truncate them . After that I Startted to copy data from main tables.
...
5- I still dont complete step 4 . And I am sure I encounter with recreate FK with defencies data !!!! :crying:
after all I backed up ,copy And restore to destination.
Then is there any solution for example with SSIS and ... I dont know to increate speed and decrease risk of this solution .
Thank you
March 9, 2016 at 4:21 am
The issue is a combination of bandwidth and data size. There's no "fast" way to delete some of the data from a table. If it can use the primary keys as part of the delete process, that can speed things up somewhat, but if you're deleting a substantial portion of the table, it's going to have to scan it, which is going to be slow. However, since you're probably not using the PK, but instead are looking up by date, it's about attempting to tune the queries in question. As to the bandwidth, I'd suggest finding a tool that breaks up the file and streaming it down in multiple streams. You'll see better performance that way. One example is azcopy if you're working with Azure.
The problem is, you can't get away from processing the data. It's going to take time, depending on how the processing occurs. There is no magic bullet. You could look to SSIS to only pull parts of the data, but that's going to put a lot of stress on the production system, possibly including blocking other processes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2016 at 9:20 am
I have implemented pretty much the same solution as Grant mentions with my current employer. To help with your bandwidth issue do you have the option of creating a temporary staging instance with your cloud provider?
If so restoring the backup to a staging instance there, performing the masking \ shrinking \ deletion etc, then backup, bring the backup down to your local network and restore.
If your using AWS for example as your cloud provider all of these actions can be scripted and with a multitude of API's to help that along.
MCITP SQL 2005, MCSA SQL 2012
March 9, 2016 at 11:19 am
There may be a way to improve the process but it would require intimate knowledge of database to accomplish and would take significant effort. I was tasked with a similar requirement to create a training database cut of our operational database in Afghanistan. This database was approximately 170 GB in size with multiple years of data. The task was to create a cut with a variable amount of data, depending on need it could be 6 months to 12 months of data.
The first automated cut of the original manual process (which took several days) dropped the time to about 7 hours. The second cut dropped the process to under 45 minutes.
I am telling you this as it can be done. It took me several weeks to create the stored procedure to accomplish this task, and a couple more weeks upon returning home to tweak it to address several additional requirements.
It would be difficult to try and accomplish this task on a forum like this. The big question here, is this something your employer is tasking you to accomplish or are you just trying to find a way to improve the process. The difference may be in the willingness to pay to help you get the task done or not.
March 11, 2016 at 11:04 pm
Thank you all
And dear Lynn Pettis :
The big question here, is this something your employer is tasking you to accomplish or are you just trying to find a way to improve the process.
After last database transition and restore to local servers, we lose all free space on this server while we need to restore another database for a new branch.
Then this is a limitation that employer dont want to solve it with buying new devices and asked my directly to improve this Process.
March 12, 2016 at 7:41 am
MotivateMan1394 (3/11/2016)
Thank you allAnd dear Lynn Pettis :
The big question here, is this something your employer is tasking you to accomplish or are you just trying to find a way to improve the process.
After last database transition and restore to local servers, we lose all free space on this server while we need to restore another database for a new branch.
Then this is a limitation that employer dont want to solve it with buying new devices and asked my directly to improve this Process.
While I agree with Lynn that it's possible to write a procedure to do the work, he's also right in that it would take a lot of work to write it. However, if your server doesn't even have the capacity to do the work, then even the perfect solution won't be able to run. The server must have the capacity to do the work.
March 14, 2016 at 9:22 am
Ed Wagner (3/12/2016)
MotivateMan1394 (3/11/2016)
Thank you allAnd dear Lynn Pettis :
The big question here, is this something your employer is tasking you to accomplish or are you just trying to find a way to improve the process.
After last database transition and restore to local servers, we lose all free space on this server while we need to restore another database for a new branch.
Then this is a limitation that employer dont want to solve it with buying new devices and asked my directly to improve this Process.
While I agree with Lynn that it's possible to write a procedure to do the work, he's also right in that it would take a lot of work to write it. However, if your server doesn't even have the capacity to do the work, then even the perfect solution won't be able to run. The server must have the capacity to do the work.
You should also be aware that I was working 12+ hour days 7 days a week while in Afghanistan. With that, having intimate knowledge of the database, we are looking at 6 to 8 weeks minimum to write a procedure you are looking to create. I say minimum as there are things my procedure doesn't worry about since there are features and data types not used in the database that would add additional complexity to the procedure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply