Attaching single data file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Attaching single data file

Author

Topic

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2007-04-09 : 15:52:18

Hi everyone,
we had a power spike over the weekend and it looks like one of our databases went bad. It appears the log file got corrupted (probably during replication) and now it's passing the message "The LSN (9481791:16:19) passed to log scan in database 'myDatabase' is invalid." So what I'm trying to do now is use the system stored procedure 'sp_attach_single_file_db'. My syntax is:

sp_attach_single_file_db @dbname = 'myDatabase', 
   @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\myDatabase_Data.mdf'

According to BOL, the procedure builds a new log file for you. However, when I run this, it returns the original message I mentioned above. I also tried moving the current ldf file out of the directory and when I run it again, it returns:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'myDatabase'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\myDatabase.ldf' may be incorrect.
Anyone know what's going on?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-09 : 16:08:40

It works only when the db is closed properly, you may need restore from good backup in this case.

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2007-04-09 : 16:09:52

quote:
Originally posted by rmiao

It works only when the db is closed properly, you may need restore from good backup in this case.

That's not what I've read though. The mdf file is good but the ldf is bad. So I thought I could rebuild the ldf using this.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-09 : 16:18:53

Rebuilding log is different process, here are steps:

Steps to recover db without log file:

--1. Create Database

USE master
GO
CREATE DATABASE mydatabase
ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)
LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),
( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)
GO
--2. Add data
use mydatabase
go
create table x123(id int)
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223
insert into x123 select 111223

--3. Detach Database
use master
go

sp_detach_db mydatabase

--4. Accidental Deletetion.
Delete mydatabase1.ldf and delete mydatabase2.ldf

--5. Try to attach mydatabase.mdf
sp_attach_db 'mydatabase','c:\mydatabase.mdf'

Error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'mydatabase'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'c:\mydatabase1.ldf' may be incorrect.
Device activation error. The physical file name 'c:\mydatabase2.ldf' may be incorrect.

--6. Rename c:\mydatabase.mdf to MydatabaseXXXXXX.mdf

--7. Create database mydatabase

USE master
GO
CREATE DATABASE mydatabase
ON PRIMARY ( NAME = mydatabase_data,FILENAME = 'c:\mydatabase.mdf',SIZE = 2MB,MAXSIZE = 10,FILEGROWTH = 1)
LOG ON ( NAME = mydatabase_Log1,FILENAME = 'c:\mydatabase1.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1),
( NAME = mydatabase_Log2,FILENAME = 'c:\mydatabase2.ldf',SIZE = 1MB,MAXSIZE = 10, FILEGROWTH = 1)
GO

--8 Stop SQL Server

--9 Delete mydatabase.mdf

--10 rename MydatabaseXXXXXX.mdf to mydatabase.mdf

--11 Start SQL Server service

--12 run the following

use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

--13
update sysdatabases set status = 32768 where name = 'Mydatabase'
go
checkpoint
go
shutdown with nowait
go

--14. delete mydatabase1.ldf and mydatabase2.ldf

--15. run this query

dbcc traceon(3604)

--16. rebuild Log

dbcc rebuild_log('Mydatabase','c:\Mydatabase1.ldf')

--17.
update sysdatabases set status = 0 where name = 'mydatabase'

--18. restart sql server
--19. run the following query
use mydatabase
go
dbcc checkdb
go
dbcc checkalloc
go
backup database mydatabase to disk = 'c:\mydatabase.bak'
go
select * from x123
go

Kristen
Test

22859 Posts

Posted - 2007-04-10 : 01:44:44

Do you need "checkalloc" as well as "checkdb"?

"backup database mydatabase ..."

Kristen

brahianblade11
Starting Member

1 Post

Posted - 2011-11-28 : 18:55:50

this topic was help full for me. Thanks rmiao. I do all the step and all work property. tanks..........

Brahian Pena

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources