Attach database file failed

Question

Attach database file failed

VastSQL

SSC-Dedicated

Points: 36097
More actions
January 17, 2013 at 2:49 am

#269370

Hi Experts,
I tried moving one of our user DB using
1.mode database offline..moved files run alter database modify files and made database online..it didnt took old .ldf file instead created a new file in old path.
2.detach\attach method. It threw error and failed to attach. then successfully added the file using sp_attach_single_file_db but the problem is my old 21gb ldf file is not using now.
How can i move the ldf file successfully attach the old ldf file

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

If you attached just the data file and had SQL rebuild the log, that old log file is now useless.

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Ratheesh.K.Nair (1/17/2013)
Hi Experts,
I tried moving one of our user DB using
1.mode database offline..moved files run alter database modify files and made database online..it didnt took old .ldf file instead created a new file in old path.

Oh dear, you too have fallen fowl of moving database files under SQL Server. Keep an eye out for my SSC article coming soon on how to carry out this process successfully 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

Thanks Gail and Perry.

What i have to do now?? I followed http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx this article which caused all this issues.. How can i move the .ldf file now to another drive??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If the DB was brought online and a new log file created, the old one is completely useless and cannot be used.

Correct your alter database statements (I guess you didn't specify move for the log file), take the DB offline, move the new log file there (the old one will be useless) and bring the DB online.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

Thanks Gail,

i used the command

ALTER DATABASE ABC MODIFY FILE ( NAME = ABC_log FILENAME = 'H:\Log\SQL8\ABC_log' )

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Well, if you really can't wait for my guide to be published perform the following;

get details of the current paths\filenames using this query
select name, physical_name from sys.master_files where database_id = DB_ID('yourDB')
For each file you wish to move build a MODIFY FILE command. For the log file for instance use
ALTER DATABASE [yourDB] MODIFY FILE(name=yourDB_Log,
physical_name = 'new drive:\ new path\ new path\yourdb_data.ldf')
issue the ALTER DATABASE statement
take the database offline
copy the file to the new location
bring the database online
delete the original file from the original location

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

Thanks Perry,

So first we have to run the ALTER database MODIFY FILE and then only we have to take database offline right?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

That's the order i prefer to do it in. Set the paths and confirm that sql server has updated the system catalogs, you will receive a meesage indicating that the new paths have been accepted and will be used next time the database restarts 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 9

Perry Whittle (1/17/2013)
That's the order i prefer to do it in. Set the paths and confirm that sql server has updated the system catalogs, you will receive a meesage indicating that the new paths have been accepted and will be used next time the database restarts 😉

Did everything on that order by failed.

SQL error log is showing this.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

Ratheesh.K.Nair (1/17/2013)
Perry Whittle (1/17/2013)
That's the order i prefer to do it in. Set the paths and confirm that sql server has updated the system catalogs, you will receive a meesage indicating that the new paths have been accepted and will be used next time the database restarts 😉
Did everything on that order by failed.
SQL error log is showing this.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.)

either you havent moved the file to the new directory or you have given it a different name. After taking the database offline you have to actually go into windows explorer and copy and paste the file to the new directory, SQL Server does not do this for you!!

Also, ensure the new directory has sufficient ACLs to allow the SQL server service to access the files.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Mani Dev Right there with Babe Points: 737 More actions · Answer 11

Make sure the path exists for those files which are to be moved.

it should work.