• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 26386
  • Last Modified:

How to attach SQL 2000 database (MDF) without a LDF file.

Hi..

I have a big problem, hope that you can help...

I have windows 2003 server with CA brightstor 11.0 (Build 2670)

and is making backup to all my SQL Servers.

so I need to restore a database where the MDF file is 4 GB and the LDF file 111 GB.

So right now I already restore the MDF file of 4 GB. but I cannot restore the LDF file..

So I want to attach this MDF file to a test server that have SQL 2000 with SP4.

so I check in this site how to restore the MDF to SQL but not good luck...

can someone help.. how to attach the MDF file on the query analyzer..

if I miss something that cannot be understand , please let me know...

thanks

Regards.

RTL
0
Realhawk
Asked:
Realhawk
  • 5
  • 2
  • 2
  • +1
1 Solution
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
0
 
TAB8Commented:
If you need to ignore the current LDF file and if your database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

1. Create a new database with the same name and same MDF and LDF files 2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status =  32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.
0
 
RealhawkAuthor Commented:
Thanks TAB8 for your comments.. but I got and error in the step where you said..

Update sysdatabases set status =  32768 where name = "BadDbName"
go

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BadDbName'.

when I ran with ' '  without " " I got this:

update sysdatabases set status = 32768 where name = 'BadDbName'
go

(0 row(s) affected)

If this ok?  Or I have to do other thing?

Regards

RTL
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
RealhawkAuthor Commented:
TAB8

These are the steps that I follow with your document.

1. Create a new database with the same name and same MDF and LDF files
The Databasename is dbSIM

So I created a database called dbSIM with Filename dbSIF_Data and the dbSIM_Log with Filename dbSIF_Log

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

I stopped the sql server and rename the dbSIM.MDF to dbSIMRTL.mdf and the dbSIM_Log.ldf to dbSIM_LogRTL.ldf and then I copied the file that I restored from the backup
to the path where are these files: E:\Microsoft SQL Server\MSSQL\Data
so dbSIM.MDF is over there now (the good one)

3. Start SQL Server

I started SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

I saw dbSIM (Suspect)

Sp_configure "allow updates", 1
go

I got this message:
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

Update sysdatabases set status =  32768 where name = "BadDbName"
go

I got this message:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BadDbName'.

so I change the syntaxis " "  for ' '

update sysdatabases set status = 32768 where name = 'BadDbName'
go

I got this message:
(0 row(s) affected)

Sp_configure "allow updates", 0
go

I got this message:
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

6. Restart sql server. now the database will be in emergency mode

I restart the sql server and the database dbSIM is still showing dbSIM (Suspect) in the Enterprise Manager

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

I type it in the query like this:

DBCC REBUILD_LOG(dbSIM,'E:\Microsoft SQL Server\MSSQL\dbSIM_Log.ldf')

I got this message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8. Execute sp_resetstatus <dbname>

I type it:  sp_resetstatus dbSIM

I got this message:
Prior to updating sysdatabases entry for database 'dbSIM', mode = 0 and status = 16 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.


9. Restart SQL server and see the database is online.

I restarted the sql server and still looking dbSIM (Suspect)

what step do I fail?

Regards..

RTL
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi Realhawk,

Another method ,

create a new file named  "urDatabaseName_log.ldf"   (for creating this , right click-> new text document-> open this document -> save as - within double Quotes give the name )
Now you can attach the urDatabase using either EM or QA
                                                                                                                       



Aneesh R!
0
 
RealhawkAuthor Commented:
Aneesh

I did what you said and I got this error..

Microsoft SQL-DMO (ODBC SQLState: HY000)
Error:823: I/O error 38 (Reached the end of the file.)
detected during read at offset 0000000000000000 in file 'E:\Microsoft SQL Server\MSSQL\Data\dbSIM_Log.ldf'

now what?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ok, Let us try this

create a database with the same name, detach the database, overwrite the mdf file with urs, then reattach it
0
 
RealhawkAuthor Commented:
Here is the Real Steps to make this work..

TAB8 here are the steps...  and PEOPLE, IF YOU HAVE PROBLEMS TO ATTACH MDF FILE WITHOUT LDF FILE HERE ARE THE STEPS THAT WORKS.. IT REALLY WORKS..

These are the steps that I follow to make this work.

1. Create a new database with the same name and same MDF and LDF files
The Databasename is dbSIM

So I created a database called dbSIM with Filename dbSIF_Data and the dbSIM_Log with Filename dbSIF_Log

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

I stopped the sql server and rename the dbSIM.MDF to dbSIMRTL.mdf and the dbSIM_Log.ldf to dbSIM_LogRTL.ldf and then I copied the file that I restored from the backup
to the path where are these files: E:\Microsoft SQL Server\MSSQL\Data
so dbSIM.MDF is over there now (the good one)

3. Start SQL Server

I started SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

I saw dbSIM (Suspect)

Sp_configure "allow updates", 1
go

I got this message:
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

Update sysdatabases set status =  32768 where name = 'dbSIM'
go

I got this message:
(1 row(s) affected)

Sp_configure "allow updates", 0
go

I got this message:
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure with override
GO

I got this message:
The command(s) completed successfully.

6. Restart sql server. now the database will be in emergency mode

I restart the sql server and I select Query Analyzer to connect to the Master Database

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')  -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

I type it in the query like this:

DBCC REBUILD_LOG(dbSIM,'E:\Microsoft SQL Server\MSSQL\dbSIM_Log.ldf')

I got this message:
Warning: The log for database 'dbSIM' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8. Execute sp_resetstatus <dbname>

I type it:  sp_resetstatus dbSIM

I got this message:
Prior to updating sysdatabases entry for database 'dbSIM', mode = 0 and status = 2048 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

9. Restart SQL server and see the database is online.

I restarted the sql server and dbSIM is now online.. in the first time looks like dbSIM (DBO use Only) , so click with the right bottom and select properties and the select the Options tab and deselect "Restrict Access" and click OK.

and the database will look online... is really working!!!!!!  :)

Finally here is a real document to make this nightmare works...

Regards..

Roberto Tamez
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Realhawk - did you even look at the link I provided... ?
0
 
RealhawkAuthor Commented:
Yes leew,

but it was more easy to understand the steps that TAB8 said...

that's why I prefered to document here with an example...

your link I already check, but some steps doesn't apply with me...

thanks anyway for your help..

Regards

RTL
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now