| Author |
Topic |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
 Posted - 2007-10-02 : 17:07:19
|
| how do you design a dev box?how do you replicate from prod to dev?Ideas will be appreacited.do you get a bak file and restore it?DTS ?linkservers????=============================http://www.sqlserverstudy.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 17:26:50
|
| I use backup/restore method. I've got a job in place that does this on a weekly basis for our development team.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2007-10-02 : 18:50:23
|
| but the my prod DB is 33GB. IT will talke very long to transfer the file thru the network |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-02 : 19:00:46
|
You can utilize Quest's Litespeed product.
Future guru in the making. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 19:02:02
|
quote:
Originally posted by gongxia649
but the my prod DB is 33GB. IT will talke very long to transfer the file thru the network
Why do you switch userids?You should setup the job so that it compresses the backup file that way only the smaller file will need to be copied. Typically you can get a SQL backup file to be only 10% of the original file size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-03 : 00:00:00
|
| 1- Do i have to transfer this file manually thru the network? or is there any application to do it automatically.2- what about if there are users on the DB. I won't be able to restore it.=============================http://www.sqlserverstudy.com |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-10-03 : 01:21:27
|
| You can copy the Backup file and paste it there in the dev server, from there you can restore it as a new database.VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 01:28:44
|
| 33GB copied across WAN is a big file. "paste it there" is probably going to be a very slow operation!! |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 02:11:18
|
quote:
Originally posted by funketekun
1- Do i have to transfer this file manually thru the network? or is there any application to do it automatically.2- what about if there are users on the DB. I won't be able to restore it.
1. You could set a job to transfer it automatically if you wanted. that would take some extra effort on your part to code something like that.2. kick the users out of the database and then perform the restore. that is a pretty common task.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 04:48:19
|
| 2. Set the target database to Single User and do the restore.For an example see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-03 : 08:43:11
|
| How will you transfer the 33GB file over the network? How do you people do it?=============================http://www.sqlserverstudy.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 08:45:23
|
| I do it like Tara described. That's only 3GB to transfer. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-03 : 09:27:43
|
| What I've done in the past is set up a .bat file to call an SP that does the backup(s). Then after that, in the .bat file I zip the backups with pkzip, then copy them to the destination server and then unzip them there and restore from there. Either way you can write a script or manually zip the backup. It should only be 3-4 gb once you zip it. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-03 : 09:32:30
|
Also, I know that in many case you can get better compression using the .RAR format, although it takes longer to compress.
Future guru in the making. |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-03 : 10:01:03
|
| I don't want compression or anything like that...i want to know how to transfer the 33GB file.. is there any app that does it automatically or do i have to wake up at 4am everynight and do it manually?=============================http://www.sqlserverstudy.com |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-03 : 10:25:03
|
quote:
Originally posted by funketekun
I don't want compression or anything like that...i want to know how to transfer the 33GB file.. is there any app that does it automatically or do i have to wake up at 4am everynight and do it manually?=============================http://www.sqlserverstudy.com
What we were saying is that you should compress the file so that it is not 33 GB and thus it will transfer faster. You can create a job that calls a batch file to do it automatically. Look up dos scripting and creating batch files if you need to know how to do that part. Then you will just need to use a bit of creative independent thought to finish the task.
Future guru in the making. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-10-03 : 11:03:05
|
quote:
Originally posted by funketekun
I don't want compression or anything like that...i want to know how to transfer the 33GB file.. is there any app that does it automatically or do i have to wake up at 4am everynight and do it manually?=============================http://www.sqlserverstudy.com
Why don't you want to compress the file? Do you understand why they are suggesting that you do it? Why don't you make a little list of the pros and cons of compressing the file before transferring and see if it is worth it....I'll give you a start:Pros of compressing file:* 33GB file becomes only 3GB, transfers 10 times as fast, takes up 10 times less spaceCons of compressing file:* You must first compress the file before the transfer, and then uncompress the file after the transferBased on that, what do you think you should do?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-10-03 : 11:49:59
|
| you could also use litespeed (or similar) backup product that supports inline compression during backup. that would save you a step.of course you would need to buy the software and have it installed on both prod and dev systems. i'm guessing that would be a problem becuase it would involve some extra work on your part. -ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-03 : 12:07:00
|
| For the database that I mentioned that gets copied to development weekly, it is 40+GB in size. We use SQL Litespeed to compress the backups in that environment. I then have a job that runs WinRAR (well rar.exe for coding purposes) to compress the file down further plus include the other small dependent databases that it needs into one file. This all occurs on the source server. We've then got a process that copies this rar file from the source to the destination. On the destination, I have a job that uncompresses the files, restores the backups, unorphan the logins, sets security, changes the recovery models to SIMPLE, and finally shrinks down one specific LDF file to prevent disk threshold alerts from firing (low on disk space in dev).Oh and no I won't be posting my code for this. There aren't that many lines of code to do all of this work, so it shouldn't be so hard for you to write it. I do everything inside stored procedures and make use of xp_cmdshell. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-03 : 12:20:41
|
quote:
Originally posted by tkizer
For the database that I mentioned that gets copied to development weekly, it is 40+GB in size. We use SQL Litespeed to compress the backups in that environment. I then have a job that runs WinRAR (well rar.exe for coding purposes) to compress the file down further plus include the other small dependent databases that it needs into one file. This all occurs on the source server. We've then got a process that copies this rar file from the source to the destination. On the destination, I have a job that uncompresses the files, restores the backups, unorphan the logins, sets security, changes the recovery models to SIMPLE, and finally shrinks down one specific LDF file to prevent disk threshold alerts from firing (low on disk space in dev).Oh and no I won't be posting my code for this. There aren't that many lines of code to do all of this work, so it shouldn't be so hard for you to write it. I do everything inside stored procedures and make use of xp_cmdshell. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
yeah this is what i was looking for...thanks.=============================http://www.sqlserverstudy.com |
|
|
|
|
replicate prod to dev