SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to copy production DB into New Developmant Database?


How to copy production DB into New Developmant Database?

Author
Message
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Hi,
We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server.
Could you please show me the steps, how can i set up test server?


Thanks,
Pora
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85867 Visits: 41091
poratips (11/1/2012)
Hi,
We have Sql server 2008 R2 and i would like to set up my new test server and copy production DB to new test Server.
Could you please show me the steps, how can i set up test server?


Thanks,
Pora


Simply restore a full backup from the production database. You are taking regular backups, right? ;-)

The only problem with taking copies of a production database, no matter the method, is that you might not want Developers to see sensitive parts of the data and you certainly don't want to make a copy (no matter the method) if you have things like SSN stored in clear text (you really need to fix that in production, first).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Thanks so much for your prompt response.
We are taking back up - differential back up daily and during week end we take full back up.
We have 4-6 DB on one server and couple of them kind of Data Warehousing (Dimensional and Fact Table).
We don't have that much sensitive data and we have only couple developers.
Could you please let me know that i am taking correct steps?

1) Will install Sql server 2008 R2 on new server
2) Copy latest back up files for all the databases into new server
3) Restored one by one database backup into new server
4) Collect login script from source server and run into target server to handle orphan logins
5) Run DBCC check command to verify the restore

I am planning use SSMS, will be ok, right?

Regards,
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85867 Visits: 41091
poratips (11/3/2012)
Thanks so much for your prompt response.
We are taking back up - differential back up daily and during week end we take full back up.
We have 4-6 DB on one server and couple of them kind of Data Warehousing (Dimensional and Fact Table).
We don't have that much sensitive data and we have only couple developers.
Could you please let me know that i am taking correct steps?

1) Will install Sql server 2008 R2 on new server
2) Copy latest back up files for all the databases into new server
3) Restored one by one database backup into new server
4) Collect login script from source server and run into target server to handle orphan logins
5) Run DBCC check command to verify the restore

I am planning use SSMS, will be ok, right?

Regards,


That sounds about right. You might run into the occasional problem with a SID for a user or login being out of sync but that's the exception rather than the rule in light of your Step 4.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Thanks Jeff.
When i do again Database Refresh on a By Weekly or Monthly, so i need to follow the same steps or is it any other approach or method? I need to refreh regualarly all the Databases (We have more than one server and each server contains different Database including Datawarehousing Database which contains Fact and Dim tables.


Thanks,
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5238 Visits: 4076
poratips (11/4/2012)
Thanks Jeff.
When i do again Database Refresh on a By Weekly or Monthly, so i need to follow the same steps or is it any other approach or method?
Atleast you need to fix orphan logins plus data masking (so that developers cant see the sensitive data like SSN no or email addresses)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
Thanks So much.

I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.

Thanks,
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5238 Visits: 4076
poratips (11/6/2012)
Thanks So much.

I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.

Thanks,
use different and new thread for this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12259 Visits: 10648
poratips (11/6/2012)
Thanks So much.

I have another situation that my developer wants some tables from Prodction DB server to their desktop m/c.
We have already sql express installed and Database and tables ae already there but no data.
In this Database, we need some of the tables with for specific date and dept.
What will be the best way to handle this?
My problem is that i can't use Task - Copy wizard from prod server as i need some tables I need to copy data full and some of the tables i need to restrict by Date but this Date will last thre months and
I have Date column in all those tables which i need jsut last three months data.

Thanks,

If the developers need subsets of records from specific tables, then BCP out to files in native format, and the developers can then BCP the files into tables on their local instance. Insure that the column sequence and datatypes in development are exactly the same as what's in production, and you won't have to screw with format files, making this a breeze.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
poratips
poratips
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 653
I will be building brand new test enviornments ans need to restrict user for security purpose so need to create Group for each Role
anad assign user to each Role Group, i am planning to group in Active directory, what will be the best way to do?
Once i install sql server, resotored DBs then howcan i collect the login script and run into new Db to avoid Orphan user issue?
If you give some feedback, appreciated!

Thanks,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search