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

Question

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?

Answer 1

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.

Answer 2

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