0

I've absent-mindedly let Windows Server 2003 compress a HDD to try and save some space. That same drive contains an instance of SQL Server 2008. Since compressing, all the Databases have taken themseleves offline.

I've manually expanded the files in the SQL server directory using the "compact" command line tool. However they're still not online in Sql Management Studio.

What do I need to do to bring them back online?

3
  • What does the SQL log say when you try to manually load one of the databases ?
    – Stephane
    Sep 3, 2013 at 9:52
  • In any case, you might need to restore from backup. Since these are usually time-critical, you'd better restore them right now. Why where you doing anyway compressing the hard drive of a production system ?
    – Stephane
    Sep 3, 2013 at 9:53
  • It's just an internal box used for dev. Nothing critical.
    – Ben Ford
    Sep 3, 2013 at 11:05

2 Answers 2

0

Copy the .mdf and .ndf files to a non compressed disk and re-attach them as a different database name.

EXEC sp_attach_db @dbname = 'WHATEVERNAME', @filename1 = 'E:\PRODATA1\data1.mdf', @filename2 = 'E:\PRODATA2\data2.ndf', @filename3 = 'E:\PRODATA3\data3.ndf', @filename4 = 'E:\PROLOG1\log.ldf'

SQL Server 2008 has its own built-in data compression system. Check this article: http://blogs.msdn.com/b/sanchan/archive/2006/06/04/617585.aspx

0

Incredibly easy fix it turns out.

When I ran the compact /U originally I didn't notice that it failed when hitting files that were in use. So I just Stopped the SQL server instance. Re-ran "compact /U" in the Data directory for SQL server. Then just restarted the SQL service. All's fine now.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .