Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Operational db without an .ldf file and no useful backups on SQL Serve


Best Answer OrnSveinsson , 23 March 2016 - 08:17 AM

You will have data loss but it can be done. 1) Detach database and move your mdf to save location. 2) Create new databse of same name, same files, same file location and same file size. 3) Stop SQL... Go to the full post »


  • Please log in to reply

#1
DonaldLyons86

DonaldLyons86

    New Member

  • Member
  • Pip
  • 2 posts

No, the question isn't stupid but the "admin" sure is.... BUT, if you can help, please do! The problem is how to recover a .mdf file into an operational db without an .ldf file and no useful backups on SQL Server 2005. See, I told you - STUPID!!

Now, I've seen plenty of suggestions for SQL 2K, but not 2005. Single file attach - no go. Detach wasn't proper. DBCC REBUILD_LOG, where most SQL 2K solutions end up - can't get there on 2005. At least not in any method I've tried. Access to those system tables not allowed (no "ad-hoc queries"). 3rd party recovery tools? Anything....?

(This isn't a big db, ~ 8GB, but it is rather important AND urgent).


Many thanks!


  • 1

Advertisements


#2
OrnSveinsson

OrnSveinsson

    New Member

  • Member
  • Pip
  • 2 posts
✓  Best Answer

You will have data loss but it can be done.
1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE

From programs you can try SQL Server Recovery Toolbox if you want. There is a demo version, so you can check it will help you or not. http://www.oemailrec...sql_repair.html


  • 1

#3
OrnSveinsson

OrnSveinsson

    New Member

  • Member
  • Pip
  • 2 posts

May be this article will give you more information.

http://www.techrepublic.com/forums/questions/it-seems-our-sql-database-is-corrupted/


  • 1

#4
allen58441

allen58441

    New Member

  • Member
  • Pip
  • 1 posts

Hi,DonaldLyons86

 

When a MDF database file is corrupt, you can first use the SQL Server build-in command DBCC CheckDB and CheckTable to repair it, see:

http://technet.micro...y/ms176064.aspx

and

http://technet.micro...v=sql.110).aspx

to get more information about the commands. This is the recommended solution which you should try first.

If the corruption is severe and the above commands do not work, then you may try some third party tools to scan and recover the data from your corrupted MDF and NDF database files. Below is a list of SQL recovery tools:

https://www.datanume...ery/compare.htm

The list seems to rather comprehensive that including nearly all the popular tools in the market. And all the tools provide a free demo so you can try before you purchase.

Hope this will help. Good luck!

 
  • 1

#5
DonaldLyons86

DonaldLyons86

    New Member

  • Topic Starter
  • Member
  • Pip
  • 2 posts
Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!

  • 0

#6
lucasdonovan1987

lucasdonovan1987

    New Member

  • Member
  • Pip
  • 2 posts

The manual procedure for fixing SQL Database related errors has been mentioned in this article on Easy and Free way to do SQL Database Recovery

 

I hope this helps.


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP