Damaged "modellog.ldf" file

[TenRC]

My customer is running MSSQL 7.0 (Standard I think). I am a controls engineer with limited experience with SQL. In an effort to repair a badly damaged drive, I lost the file "modellog.ldf". The "model.mdf" file is OK. System is several years old and OEM did not ship CD to customer.

Does anyone have a copy of this file I can use? Will replacing this file take care of the problem? I have a friend that had a copy of the enterprise edition but after copying the file and trying to start the server, the server said the file was suspect.

Thanks for any help you can offer.
Sincerly,

Steve

 

[mrdenny]

Do they have a backup of the model database?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[TenRC]

No. I do not. I need the file modellog.ldf. I do have the file model.mdf.

 

[SQLBill]

How did you lose the .ldf file? Did you detach the database and then delete it? Did you delete it when the services were shutdown? Did you delete it when the database was active?

Normally, the log file isn't really necessary to get the database back up....you could use Query Analyzer and run:

EXEC sp_attach_single_file_db @dbname = 'model', @physname = '<path to model.mdf>'

Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Posting advice: FAQ481-4875

 

[TenRC]

Thanks Bill,

I have reveiwed your post. The log file was lost during an attempt to repair the drive. The server was not running at the time.

I believe I read that sp_attach_single_file_db should only be ran if the database was detached which it was not.

I still think the easiest way to resolve this issue would be if I can get hold of the file from someone's cd.

-Steve

 

[SQLBill]

It's not on a CD...that's the point. The log file is created when the database is first created. After that it is updated when a transaction is made to the database (the .mdf file). The ONLY thing in the log file is the transactions. When you do full backups and transaction log backups or have the database in SIMPLE recovery mode, the log file eventually gets overwritten with the new transactions. So even if you could 'copy' it from the CD, it wouldn't work since it doesn't have the transactions that go with the data file.

-SQLBill

Posting advice: FAQ481-4875

 

[TenRC]

Please correct me if I am wrong. Your information conflicts with what I have already been told.

I believe Modellog IS included on the CD and there is no reason for it to ever be changed.

When you start the server, SQL uses the model database to create some sort of temp database which is required to build the actual database.

I am sure there is a misunderstanding somewhere but I don't know where. I have been told by two programmers that all I need to do is get a copy of the Model files from someone.

 

[SQLBill]

There could be....yes, the system databases are on the CD. However, the model db can be changed. It works as a template to create the TempDb and all user dbs.

You can try to install it from the disk, but I don't think it will work as it's no longer part of the original install. I think you would have to reinstall SQL Server. Again, I might be wrong......

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

To use the model log file from the CD you would need the data file for model as well as the internal numbering would have changes from the time the CD was pressed until now.

You'll then need to bring the SQL Server up in single user mode by running "sqlservr.exe -m -c" in the binn folder of your SQL Server install (c:\Program Files\Microsoft SQL Server\MSSQL\binn\ by default). It's working if the data normally put in the log file is scrolling by.

backup the master database.
Enable trace flag 3608 using:

DBCC TRACEON 3608

Detach the model database using sp_detach_db.

Rename the mdf file for the model database, and copy in the mdf and ldf from the CD.

Turn off trace flag 3608 using:

DBCC TRACEOFF 3608

Reattach the database using sp_attach_db

You may need to resetup the security chain by running this.

USE model
Go 
EXEC sp_changedbowner 'sa' 
Go

Stop the SQL Server by pressing CTRL C in the command window that is running SQL Server. Restart SQL Server normally by starting the service.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief