Post reply

Attach DB from storage drive

  • June 9, 2008 at 11:44 am

    #154424

    Hi, this will probably sound like a noob question. But I have a server running windows 2003 and I've installed SQL 2005. On the server I have 70gb on the 'C' drive and 500gb on the 'E' or storage drive. I've copied a couple of DB's onto the storage drive and would like to attach them to the SQL instance on the 'C' drive. However, when I go through the management studio object explorer and right click on 'databases' and attach the database .mdf file stored on the 'E' drive I receive the following error:

    the file "E:\MSSQL.1\MSSQL\DATA\Test.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

    Could not open new database 'Test'. CREATE DATABASE is aborted (Microsoft SQL Server, Error 5118)

    Is it even possible to store a database on a drive separate to where SQL is installed?

  • June 9, 2008 at 11:59 am

    #826240

    I'd check the attributes on your E-Drive and the sub-directories. Sounds like compression is turned on at the file-system level.

    😎

  • June 9, 2008 at 12:05 pm

    #826243

    Thanks Lynn, I unchecked my 'compress drive to save disk space' check button in the disk management properties session and the worked. If I store my data bases on a separate drive from my SQL installation which is on the 'c' drive should I leave the administration databases like Master.mdf / msdb.mdf / mssqlsystemresource.mdf on the 'c' drive, or should I store them with the other DB's on my E: drive?

  • June 9, 2008 at 12:12 pm

    #826251

    I am going to give you the standard answer, "It Depends". Some will tell you to move them to their own set of drives, others won't. On our servers, I have them in the default installation location and have not moved them. The drives they are on are mirrored, and SQL Server is the ONLY application running on the servers (except one of our development DW servers, where we have IIS and SharePoint installed).

    I am sure that other experienced DBAs out there will give you other answers.

    😎

  • June 9, 2008 at 12:28 pm

    #826261

    Thanks Lynn, your suggestion sounds like a safe plan.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply