Problem
You have a production database that you need to write SQL reports against. You are an experienced IT professional, and you know that writing SQL against the production data is not advisable. As a result you have decided to take a copy of your MS SQL Server 2005 production database and stand it up in a development environment.
Solution
- Download MSSQL management studio express at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796
- In MSSQL management studio, configure a new SQL server connection, you will need an IP address, username, and password to enter in the following dialog box :
- Available databases will then appear in the left hand tree view.
- ‘right-click’ on the database in question and drill down to Tasks -> Backup
- In the ‘Destination’ area, select a path –Local- to the SQL server in question (sample dialog pasted here)
- The produced .bak file will be importable to almost any more recent version of MSSQL.
You may also create a ‘scripted backup (similar to a MySQL dump) by proceeding from the second step:
- right click on the database in question and drill down to Script database as -> CREATE to -> File
- and select a file local to the remote workstation.
Depending on your database size, a backup may take quite some time.