Can't attach database after server crash?

Hi...
I have a database I was working on a few months ago. The server it was on
crashed before I could get backups of it but I managed to get the original
database
file before anything happened to it. When the server was redone, the
usernames were changed a little and I now have a different windows username.
I wouldn't
think this would matter since the sql server uses windows security to sign
in anyways. Either way, I have tried to attach the database and I keep
getting
this error:
Attach database failed for Server 'ANDY\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
The file "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\EternityRecords.mdf" is
compressed but does not reside in a read-only database or filegroup. The
file must be
decompressed.
Could not open new database 'EternityRecords'. CREATE DATABASE is aborted.
(Microsoft SQL
Server, Error: 5118)
Any ideas how to fix this? I suppose I can redo if I had to since there was
only 3 tables in there but it is annoying that I have to redo the whole
thing
anytime something small happens...
> The file "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\EternityRecords.mdf" is
> compressed but does not reside in a read-only database or filegroup. The
> file must be
> decompressed.
NTFS compressed files on writable file groups are not supported, hence the
error.
Try decompressing the file. Navigate to the mdf file using Windows Explorer
and select Properties-->Advanced and uncheck the compress option. Same with
the log file (ldf).
Hope this helps.
Dan Guzman
SQL Server MVP
"Sunfire" <a_borka@.sbcglobal.net> wrote in message
news:uiWHH6$3HHA.4672@.TK2MSFTNGP05.phx.gbl...
> Hi...
> I have a database I was working on a few months ago. The server it was on
> crashed before I could get backups of it but I managed to get the original
> database
> file before anything happened to it. When the server was redone, the
> usernames were changed a little and I now have a different windows
> username. I wouldn't
> think this would matter since the sql server uses windows security to sign
> in anyways. Either way, I have tried to attach the database and I keep
> getting
> this error:
> Attach database failed for Server 'ANDY\SQLEXPRESS'.
> (Microsoft.SqlServer.Express.Smo)
> Additional information:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> The file "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\EternityRecords.mdf" is
> compressed but does not reside in a read-only database or filegroup. The
> file must be
> decompressed.
> Could not open new database 'EternityRecords'. CREATE DATABASE is aborted.
> (Microsoft SQL
> Server, Error: 5118)
> Any ideas how to fix this? I suppose I can redo if I had to since there
> was only 3 tables in there but it is annoying that I have to redo the
> whole thing
> anytime something small happens...
>
|||I will check it out and let you know what happens. You might have saved my
neck...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OZCATAA4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> NTFS compressed files on writable file groups are not supported, hence the
> error.
> Try decompressing the file. Navigate to the mdf file using Windows
> Explorer and select Properties-->Advanced and uncheck the compress option.
> Same with the log file (ldf).
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sunfire" <a_borka@.sbcglobal.net> wrote in message
> news:uiWHH6$3HHA.4672@.TK2MSFTNGP05.phx.gbl...
>
|||Hi... I tried your idea of unchecking the compress this file box in the
database file and its log file. I tried to attach it again and I get the
following message:
Attach database failed for Server 'ANDY\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
One or more files do not match the primary file of the database. If you are
attempting to attach
a database, retry the operation with the correct files. If this is an
existing database, the file may
be corrupted and should be restored from a backup. (Microsoft SQL Server,
Error: 5173)
Does this mean I have to redo my database by hand? I don't have any backup
of it and I have no idea what is wrong now. Any ideas?
"Sunfire" <a_borka@.sbcglobal.net> wrote in message
news:ua7zYRA4HHA.600@.TK2MSFTNGP05.phx.gbl...
>I will check it out and let you know what happens. You might have saved my
>neck...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OZCATAA4HHA.5724@.TK2MSFTNGP05.phx.gbl...
>
|||You can try sp_attach_db and specify the path & file name for *all* the database files (mdf, ldf and
possibly ndf). If you are sure you got the path correct and still get some strange error, you are
most probably in trouble. Attaching is only supported if you did a clean detach first, if you
didn't, well, these things can happen.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sunfire" <a_borka@.sbcglobal.net> wrote in message news:OiwlJjA4HHA.5740@.TK2MSFTNGP03.phx.gbl...
> Hi... I tried your idea of unchecking the compress this file box in the database file and its log
> file. I tried to attach it again and I get the following message:
> Attach database failed for Server 'ANDY\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
> Additional information:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> One or more files do not match the primary file of the database. If you are attempting to attach
> a database, retry the operation with the correct files. If this is an existing database, the file
> may
> be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173)
> Does this mean I have to redo my database by hand? I don't have any backup of it and I have no
> idea what is wrong now. Any ideas?
>
> "Sunfire" <a_borka@.sbcglobal.net> wrote in message news:ua7zYRA4HHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||Guess it's back to the drawing board then...sigh... At least the DB wasn't
very huge...grin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Hx6l7B4HHA.5844@.TK2MSFTNGP02.phx.gbl...
> You can try sp_attach_db and specify the path & file name for *all* the
> database files (mdf, ldf and possibly ndf). If you are sure you got the
> path correct and still get some strange error, you are most probably in
> trouble. Attaching is only supported if you did a clean detach first, if
> you didn't, well, these things can happen.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Sunfire" <a_borka@.sbcglobal.net> wrote in message
> news:OiwlJjA4HHA.5740@.TK2MSFTNGP03.phx.gbl...
>
|||... and you now know to do your backups regularly... ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sunfire" <a_borka@.sbcglobal.net> wrote in message news:%23V$7AlC4HHA.484@.TK2MSFTNGP06.phx.gbl...
> Guess it's back to the drawing board then...sigh... At least the DB wasn't
> very huge...grin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23Hx6l7B4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>