Moving SQL Express or MSDE Databases


Many applications use Microsoft MSDE or SQL Express instances to store application specific data. The default file location is usually somewhere on the C: drive, which may seem harmless until they start consuming valuable disk space. It is possible to move the databases without reinstalling the applications using this method:

  • Install SQL Management Studio Express (or use SQL Management Studio if you have it)
  • Locate the physical files associated with the database by Right Clicking the database in SQL Management Studio and choosing properties. Note the database and log paths may be different.
  • Stop any services and websites associated with the database
  • Right Click the database and choose “Detach”
  • Move the database and associated log file to a new location (.MDF and .LDF)
  • In SQL Management Studio, right click Databases and choose “Attach” and choose the database file (.MDF)
  • Restart the services and websites stopped above

Because the application itself communicates with the database engine, moving the location of the data files doesn’t require any reconfiguration of the application itself. I have used this method to successfully move WSUS, SCE, BackupExec and WSS databases. This method works for all versions of SQL not just MSDE or SQL Express.

Note that it is a good idea to keep Databases and Logs on independent sets of disks for performance and recoverability. It is also a good idea to ensure busy databases are not stored on the system disk.

Download SQL Management Studio Express

Advertisements