Restore a Database using .mdf and .ldf

Question

Restore a Database using .mdf and .ldf

shaun.finnegan

Newbie

Points: 3
More actions
August 18, 2009 at 5:42 am

#213564

Hi,
One of our customer servers has crashed. they are unable to restore the c:\ drive from backup (this drive included SQL system files) but all SQL datafiles were on d:\ and are still available. SQL Server is no longer on the server. Is there are way to restore / attach the datafiles to another SQL server? these databases were not detached at the the time of the crash. I take it i cant use sp_attach, does anyone know of another method
cheers,
Shaun

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

It should be possible to attach the files. The 2005 replacement for sp_attach is CREATE DATABASE .... FOR ATTACH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Clive Strong SSChampion Points: 11222 More actions · Answer 2

You mentioned "system files". I'm not sure if you meant SQL binaries or system databases?

If you meant your system databases were also on C then you'll need to recreate your logins and permissions as well if you have no backups of the master database.

Not forgetting any jobs which may be lost if you have no msdb...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Also, where are the backups of all these databases?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 738897 More actions · Answer 4

It sounds like you need to:

- install SQL Server

- attach the databases

- rebuild logins if you cannot attach master.

shaun.finnegan Newbie Points: 3 More actions · Answer 5

thanks for the replies, master database data files are on d:\ as well. i meant SQL installation files etc were all lost.

i need to install SQL server 2000 again. Can i just attach master, tempdb, model etc as well.

what i was thinking might be the best if i can attached the client application databases and recreate the login there'll be no need to attach the master etc - does this sound right?

Clive Strong SSChampion Points: 11222 More actions · Answer 6

Sure, if you only have a few logins / databases etc in the master database, then yes, you can attach your client database and quickly add the logins back in manually.

Clive Strong SSChampion Points: 11222 More actions · Answer 7

Forgot to mention, once you have added the login back in, if you try to add the user, you'll likely get a message telling you the user already exists in database X.

You'll need to run the following in the attached database:

sp_change_users_login 'auto_fix', 'UserName'

This will fix the login/user mapping for you and retain the original permissions.

Steve Jones - SSC Editor SSC Guru Points: 738897 More actions · Answer 8

Master is a parameter in the service startup, so what you'd do it make you have a copy of your current files. Install SQL, then change the startup parameter to point to your old master database.

You don't need to worry about tempdb or model (or shouldn't). Never tried attaching msdb, but I'm sure it can be done.

shaun.finnegan Newbie Points: 3 More actions · Answer 9

shaun.finnegan

Newbie

Points: 3

August 18, 2009 at 7:49 am

#1040868

thanks guys, i'll give it a go