CODEWITHSUNDEEP
sql-server-2008
Kjensen
Kjensen

Reputation: 12374

Quickly copying a production database to development environment (SQL Server)

Often I need to pull the production database of some project to my local sql server to add features, test out stuff etc.

Today my procedure is to create a backup on the production server, somehow get that to my local machine - and then create a new database locally and restore the backup on top of that.

It is a pain, and takes more time than I like - and I would like to think, there must be a better way.

I have access via SQL Server Management Studio to the production database - isn't there an easier way, that requires fewer manual steps?

How do you do it?

Upvotes: 8

Views: 9368

Answers (4)

paul stanton
paul stanton

Reputation: 964

A new option is to clone the database. Red Gate SQL Clone is one solution, and Windocks provides SQL Server containers with built-in DB cloning support. Full disclosure, I work for Windocks.

Upvotes: 0

Nick
Nick

Reputation: 5696

Can't think of a quicker way using SQL Sever Management Studio. I'd recommend SQL Compare from Red Gate for synchronising the schema, SQL Data Compare can sync the data, but it's not quick for large databases over the internet.

Upvotes: 3

Rodrigo
Rodrigo

Reputation: 4395

  • You can use SSIS and copy objects between these environments, assuming you have a direct connection to the production server.
  • Another way, it's a must to make regular backups of the production databases, a simple maintenance plan can make a full backup at night, by example. If this is the case here, just request an early backup to the administrators and then mount it into your environment.
  • One more way, because production data disclosure can involve legal issues, you can just extract the database schema and then use testing data to make any development. This is also the fastest way to get a database.

Upvotes: 1

dmateev
dmateev

Reputation: 64

Backup and Restore is slow for my databases, so what I do is: 1. Detach production database 2. Copy files to my dev machine 3. Attach database to dev server.

but no body should work on the production database.

My backup procedure is similar - I am detaching the database and archive the files, it is faster than backing up.

Upvotes: -2

Related Questions