Copy SQL Server 2008 DB from production to development server

jtwoods4
jtwoods4 used Ask the Experts™
on
I need to make an exact duplicate of my production SQL Server 2008 datbase and put it on my development server. I cannot detach the database and copy the files because that would cause a production outage. How do I do this? I have heard the word SSIS (Sql Server Integration Services). Is this what I use? Not sure how to approach this. I am a developer and not a DB Admin.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm guessing you just want a snapshot of your existing database to move to the dev server and don't need a realtime updated copy of the data.

if this is the case, it would be simple enough to do a full backup of the database, then take the resulting BAK file and restore that to the new server.

The dev server would then have a snapshot of the production database.

Commented:
also .. .take a look at Replication .. (Snapshot Replication in this case)
This would give you the ability to easily update the dev server with real data with a couple of clicks. It's very easy to set up. The link below will help out:
 
http://www.databasejournal.com/features/mssql/article.php/1458491/Setting-Up-Snapshot-Replication-A-Step-by-step-Guide.htm 
For development I am confident that daily database restores will be more than sufficient.

Your production server should run full backups daily. Usually after businnes hours. Get read access to that location and script restores for the latest backups. There are some tricks you can use to remap the SQL user account SID to existing SQL accounts.
You can use Backup and Restore or use the Data Export Wizard, right click on the database, Select Tasks, Select Export Data

Author

Commented:
Thank you everyone

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial