Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Lesson learned by Trial and Error

Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path.
Uninstalling went great.
I then installed 2008 R2 and rebooted my machine. I attached my sample databases, and my test database. To my surprise, the Test database was now corrupted!

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

I tried sp_attach_single_file_db to no vail. I tried all methods known by either Google or some of the MVP's I emailed for help. All of them said the only way was to restore the database. It was no big deal, because it is in fact a test database.

Well, I had some time left so I decided to go Trial and Error!

Since I couldn't attach the database file at all, the first step had to be to create a new database named Test with same size. I decided to create the logfile at the same location as the original file. The datafile I placed at the new location.

CREATE DATABASE Test
ON PRIMARY
( NAME = Test,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf',
    SIZE = 1024,
    MAXSIZE = 2048,
    FILEGROWTH = 15% )
LOG ON
( NAME = Test_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_log.ldf',
    SIZE = 128MB,
    MAXSIZE = 2048MB,
    FILEGROWTH = 5MB ) ;
GO

I thought it didn't matter what size the log file was, so I just used a small number, however I thought it was important to have the datafile at the same size as the corrupt one.
I then stopped SQL Server service, replaced the empty datafile with the corrupt datafile and started SQL Server service again. The database Test now was visible in the databases, but of course it was not accesible. A number of different error messages was returned to be depending on how I tried to access the database.

Ad-hoc updates to system tables are not allowed with SQL Server 20008 and later (even if using sp_configure) so I issued

ALTER DATABASE Test SET EMERGENCY

This is the same as the previous "UPDATE SET = -32768" trick.
I then decided to set the database in single user mode with

ALTER
DATABASE Test SET SINGLE_USER

Now the work by checking the database was going to happen! Since it is a test database, I didn't care if there was dataloss, so I went for

DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS)

It took some 2 minutes to complete, and there were errors and warnings. I rerun the CHECKDB command again, and this time where were only informational messages.
After that, I set the database in multi user mode and put the database online again with

ALTER DATABASE Test SET MULTI_USER
ALTER DATABASE Test SET ONLINE

And now I have access to my database again. It also seem there was no dataloss at all.

Print | posted on Wednesday, November 18, 2009 11:46 AM | Filed Under [ SQL Server 2008 Administration ]

Feedback

Gravatar

# re: Lesson learned by Trial and Error

Learned new idea for SQL 2008.
11/19/2009 7:10 AM | Yogi
Gravatar

# re: Lesson learned by Trial and Error

This is really nice and interesting blog.I m glad to know.
11/21/2009 10:34 AM | ditte traslochi milano
Gravatar

# re: Lesson learned by Trial and Error

thanks admin
Are you really cool
11/30/2009 7:41 PM | tarsus
Gravatar

# re: Lesson learned by Trial and Error

This saved my life! Thanks.
9/21/2012 7:12 PM | Kim
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET