How to copy production DB into New Developmant Database?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Development » How to copy production DB into New...

Add to briefcase

16 posts, Page 1 of 2

1 2 »»

How to copy production DB into New Developmant Database?

Expand / Collapse

Author

Message

Posted Thursday, November 01, 2012 6:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:58 AM
Points: 107, Visits: 416

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 01, 2012 8:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

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? Wink

Wink

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1380111

Posted Saturday, November 03, 2012 8:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:58 AM
Points: 107, Visits: 416

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 03, 2012 11:27 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1380732

Posted Sunday, November 04, 2012 11:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:58 AM
Points: 107, Visits: 416

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 05, 2012 4:00 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1380966

Posted Tuesday, November 06, 2012 12:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:58 AM
Points: 107, Visits: 416

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 07, 2012 1:09 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1381813

Posted Wednesday, November 07, 2012 11:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 1,162, Visits: 3,333

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.

"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47

Post #1382108

Posted Tuesday, November 13, 2012 12:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:58 AM
Points: 107, Visits: 416

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

16 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse