SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Attach a database without LDF
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sebastianknight8
Starting Member

1 Posts

Posted - 08/06/2009 :  11:38:58  Show Profile  Reply with Quote
If you don't need the original log file, try to attach DB via SSMS in the normal way, point to DB, under Database details, Remove the log file SSMS created, click OK. The appropriate log file will be created.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/06/2009 :  14:47:08  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by sebastianknight8

If you don't need the original log file, try to attach DB via SSMS in the normal way, point to DB, under Database details, Remove the log file SSMS created, click OK. The appropriate log file will be created.



That will only work if the database was shut down cleanly. If it was not, the rebuilding of the log will fail

Management Studio behind the scenes uses the Create Database for Attach_rebuild_log command, so there's no difference between using the management studio gui or a T-SQL command.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

seelan
Starting Member

Australia
2 Posts

Posted - 08/06/2010 :  18:42:08  Show Profile  Reply with Quote
quote:
Originally posted by Borik

Ok, that what i did to get it up and running, Steps are From getting Db out of [Suspect] mode due to corrupt Log file, can be applied to
DB attaching without Log File...

1. Set DB in Emergency Mode. ALTER DATABASE [dbName] SET EMERGENCY
2. Script Create DB, this should simplify the process if you have multiple files, groups, etc...
3. In a Generated Script Remove Log Entry
4. Add "For Attach_Rebuild_Log"
5. Rename Log files to .OLD, check in few locations like Defulat locations, Data file locations...
6. Detach DB, ignore the error, it should be gone from Dabases list, if you refresh... EXEC master.dbo.sp_detach_db @dbname = N'dbName'
7. Run Script

p.s. Posting here as it comes up on Google Search, i tough it might be helpful

Go to Top of Page

seelan
Starting Member

Australia
2 Posts

Posted - 08/06/2010 :  18:44:52  Show Profile  Reply with Quote
quote:
Originally posted by Borik

Ok, that what i did to get it up and running, Steps are From getting Db out of [Suspect] mode due to corrupt Log file, can be applied to
DB attaching without Log File...

1. Set DB in Emergency Mode. ALTER DATABASE [dbName] SET EMERGENCY
2. Script Create DB, this should simplify the process if you have multiple files, groups, etc...
3. In a Generated Script Remove Log Entry
4. Add "For Attach_Rebuild_Log"
5. Rename Log files to .OLD, check in few locations like Defulat locations, Data file locations...
6. Detach DB, ignore the error, it should be gone from Dabases list, if you refresh... EXEC master.dbo.sp_detach_db @dbname = N'dbName'
7. Run Script

p.s. Posting here as it comes up on Google Search, i tough it might be helpful



When everything else failed, this worked for me thanks a million
Go to Top of Page

dekj
Starting Member

USA
4 Posts

Posted - 02/01/2011 :  18:53:38  Show Profile  Reply with Quote
I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me. As it turns out, SQL Server will not act on any database without a log file. Period. Upon googling this situation, I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008, the version I was running on. I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.

I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.

1) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours

3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

4) In SQL Server Management Studio, right-click the POSProd database, and delete it.

5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.

6) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

7) Delete the new MDF (and NDF) files, but leave the LDF log file.

8) Rename the old MDF (and NDF) files back to their original names

9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.

11) Open a new query in SQL Server. Alter the POSProd database to emergency mode

alter database POSProd set emergency

12) Set the database to single user mode. Without this, the checkdb command won’t run.

ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT

13) Run dbcc checkdb

dbcc checkdb ('POSProd',repair_allow_data_loss)

Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.

14) Alter the database back to multi user mode

ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT

15) Set the database online.

alter database POSProd set online

16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.

17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.

Voila!! The database (in my case) was completely back. All the tables, every row of data.

Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.



dekj
Go to Top of Page

dekj
Starting Member

USA
4 Posts

Posted - 02/01/2011 :  18:54:22  Show Profile  Reply with Quote
I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me. As it turns out, SQL Server will not act on any database without a log file. Period. Upon googling this situation, I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008, the version I was running on. I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.

I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.

1) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours

3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

4) In SQL Server Management Studio, right-click the POSProd database, and delete it.

5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.

6) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

7) Delete the new MDF (and NDF) files, but leave the LDF log file.

8) Rename the old MDF (and NDF) files back to their original names

9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.

11) Open a new query in SQL Server. Alter the POSProd database to emergency mode

alter database POSProd set emergency

12) Set the database to single user mode. Without this, the checkdb command won’t run.

ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT

13) Run dbcc checkdb

dbcc checkdb ('POSProd',repair_allow_data_loss)

Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.

14) Alter the database back to multi user mode

ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT

15) Set the database online.

alter database POSProd set online

16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.

17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.

Voila!! The database (in my case) was completely back. All the tables, every row of data.

Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.



dekj
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/02/2011 :  01:06:35  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by dekj

I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.


Really? Which forums were you posting on?

As one of the people that tends to spend a hell of a lot of time helping people out of messes like this (here and on other forums), I take offence at that statement.

quote:
dbcc checkdb ('POSProd',repair_allow_data_loss)


Do note that in general this WILL lose data. Hence the name. If you got through with no data loss, you're lucky, but that is an exception, not the norm.

Oh, and the fix that you identified is blogged about in several places. Mine here - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/, Paul Randal's here - http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx, many others that I don't have time right now to go and find.

p.s. There's no such thing as SQL Server 2003.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

sikkid
Starting Member

USA
1 Posts

Posted - 11/04/2012 :  22:46:22  Show Profile  Reply with Quote
Wow!! I am so happy right now. The steps you provided worked out perfect. I had a 4gb database that I needed to attach. The checkdb only took about 10 minutes. So if anyone else has this issue just follow the steps. It was very easy to follow along. Thank you so much!!


quote:
Originally posted by dekj

I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me. As it turns out, SQL Server will not act on any database without a log file. Period. Upon googling this situation, I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008, the version I was running on. I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.

I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.

1) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours

3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

4) In SQL Server Management Studio, right-click the POSProd database, and delete it.

5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.

6) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

7) Delete the new MDF (and NDF) files, but leave the LDF log file.

8) Rename the old MDF (and NDF) files back to their original names

9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.

11) Open a new query in SQL Server. Alter the POSProd database to emergency mode

alter database POSProd set emergency

12) Set the database to single user mode. Without this, the checkdb command won’t run.

ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT

13) Run dbcc checkdb

dbcc checkdb ('POSProd',repair_allow_data_loss)

Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.

14) Alter the database back to multi user mode

ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT

15) Set the database online.

alter database POSProd set online

16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.

17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.

Voila!! The database (in my case) was completely back. All the tables, every row of data.

Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.



dekj

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/05/2012 :  04:14:47  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Read through those step and the last step is glaringly missing.
18) Back up the DAMN DATABASE!

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000