Move / Backup / Restore a database on a Single User SQL2005 Express system

[Product_Support]

Issue

Databases often have to be moved around or upgraded, especially on engineers laptops where they may do offline engineering. What is the best tool to use for this?

Product Line

Andover Continuum

Environment

• Continuum Single User (Standalone) Version 1.82 and above with SQL Express (2005) Stand alone DB
• SQL Server Management Studio Express

Cause

The WinSQL software as used with previous Continuum versions with MSDE (2000) does not operate with SQL2005 and another method of accessing the database is required.

Resolution

1. After installing Cyberstation with a blank database and verifying correct operation, the next thing to install is the “SQL Server Management Studio Express” software a free Microsoft download, see link below and use the 32 bit version:
   http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796
   This will provide the same user interface as provided on a full SQL2005 installation as used on a LAN system. For Continuum V2.0 and above SQL2012 Express is used, both 32 and 64 bit versions of this are available from the following Micosoft download: http://www.microsoft.com/en-us/download/details.aspx?id=35579
2. There are 2 methods to transfer your database from one system to another, using a. Backup/ Restore or b. Detach/ Attach, both options are covered below. Alternatively you may already have one or other formats on backup media.
   To use Backup or Detach you will also need to install SQL Server Management Studio Express as per 1. above on the original computer.
   1. Backup Method - you will need to perform a full backup and then restore of the ContinuumDB on the original computer:
      Using SQLE Management Studio, Right-click on ContinuumDB -> Tasks -> Backup, add a destination where you want to save the .BAK file, Click OK.
   2. Detach Method – Detach (or stop the SQL) to allow the files to be copied on the original computer:
      Using SQLE Management Studio, Right-click on ContinuumDB -> Tasks -> Detach, Click OK.
3. On your new system, make sure you have already created a new ContinuumDB using Database Initialization. If not, do this now - this creates the Database, SQL jobs, stored procedures, adds Andover97 as an admin and a lot of behind the scenes security settings. Stop Cyberstation if it is running.
   If you are using the Detach / Attach method b. then:
   Using SQLE Management Studio on the new computer, Right-click on ContinuumDB -> Tasks -> Detach, Click OK.
4. Copy the backed up files to the Data directory your new server.
   It is advisable to take a copy of the blank database first.
   Path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
5. Using SQL Management Studio, now restore or attach the backed up database depending on which method is being used.
   1. Restore Method - Restore the backup over the top of the ContinuumDB on your new system:
      Right click on ContinuumDB -> Tasks -> Restore -> Database. Select "From device:" and add your Database file, making sure to tick the Restore checkbox to select the source. Next, go to the Options page and select "Overwrite the existing database" and press the "OK" button.
   2. Attach Method - Using SQLE Management Studio on the new computer, Right-click on Databases -> Attach, Press “Add” and select the ContinuumDev.mdf file, Press OK. The .mdf and .ldf files will be shown, press OK. The ContinuumDB will appear in the tree.
6. Change the db owner with the below query using the sp_changedbowner stored procedure. Select "New Query" and copy the query text shown below. Run the query.
   

   use ContinuumDB
   go
   sp_changedbowner 'Andover97'
   go

7. Run Database Initialization and update the newly restored database.
8. Using Database Initialization from each Cyberstation, initialize your Workstations into the new Database.
9. Run Cyberstation on all Workstations.