Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to copy production DB into New Developmant Database? Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 6:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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
Post #1380095
Posted Thursday, November 1, 2012 8:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380111
Posted Saturday, November 3, 2012 8:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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,
Post #1380710
Posted Saturday, November 3, 2012 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380732
Posted Sunday, November 4, 2012 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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,
Post #1380805
Posted Monday, November 5, 2012 4:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #1380966
Posted Tuesday, November 6, 2012 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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,
Post #1381684
Posted Wednesday, November 7, 2012 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #1381813
Posted Wednesday, November 7, 2012 11:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 1,606, Visits: 4,599
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.
Post #1382108
Posted Tuesday, November 13, 2012 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:28 PM
Points: 182, Visits: 650
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,
Post #1384245
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse