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.

Connect To MS SQL Server 2005

Backup MS SQL Server 2005 DataBase

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.