Database Attach Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database Attach Problem

Author

Topic

hpchris
Starting Member

1 Post

Posted - 2010-10-05 : 18:37:10

Hi there everyone - let me start by saying I am a complete newb with SQL, so my apologies in advance if my question turns out to be a really dumb one.

I have recently been asked to move a database from one SQL 2005 server to another. Since both servers are virtual the process for moving the files was to detach the database on the first server, then shut it down and set up the second server with a new disk point to the virtual disk files of the first server. It was done this way mostly because the database is 700+ GB in size and the second server didn't have that kind of space. So the new disks are connected to the second server ok - can see all the files no problem. But when I try to attach the database I get the following error.

Attach database failed for server "servername". (MicrosoftSqlServer.Smo)

Additional Information
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft SqlServer.ConnectionInfo)

Unable to open the physical file "<filepath\Imaging_log.ldf". Operating system error 2: "2(The System cannot find the file specified.)". (Microsoft Sql Server, Error: 5120)

Now obviously on the face of it the message seems to be saying that there is no LDF file. And there isn't. But there wasn't one. There was an LDF file before the detach but after the detach there was no longer one and the NDF files had changed, so I assumed that the LDF has somehow been rolled in to the database by the detach process - does that sound right? Before the detach there was 1 MDF, 14 NDFs and 1 LDF - after there was 1 MDF, 15 NDFs and NO LDF

Since the detach seems to have done this I assume that it is normal.

I googled about a bit and found instructions for attaching the database and rebuilding the LDF. Following the instructions, I tried this ...

CREATE DATABASE ImagingFile
ON
(filename = N'E:\MSSQL\Data\ImagingFile00.mdf'),
(filename = N'E:\MSSQL\Data\ImagingFile01.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile02.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile03.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile04.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile05.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile06.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile07.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile08.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile09.ndf'),
(filename = N'E:\MSSQL\Data\ImagingFile10.ndf'),
(filename = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ImagingFile11.ndf'),
(filename = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ImagingFile12.ndf'),
(filename = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ImagingFile13.ndf'),
(filename = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ImagingFile14.ndf'),
(filename = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ImagingFile15.ndf')
for ATTACH_REBUILD_LOG
GO

However this gets the error ...

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'ImagingFile'. CREATE DATABASE is aborted.
Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'ImagingFile'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

But rebuilding the log is what I am trying to do with this command.

Can anyone suggest anything else I might try here?

many thanks
Chris

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-10-05 : 18:59:06

This is not normal - the detach should not have changed any files. The detach also would not have created a new data file and added it.

What I would do is check the latest backup I had to see what files were recorded in that backup. The log file would also be recorded in the backup.

The error message gives you the path to the log file, do you have that drive/path available on the new server?

To validate what files you should have, you can run: RESTORE FILELISTONLY FROM DISK = 'your backup file';

Jeff

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 20:16:57

Show your sp_attach_db statement. There certainly was an .ldf. Either you (or someone else) inadvertantly deleted/renamed it after detaching, or you forgot to note where it was b4 trying to re-attach.

For future reference, the very first thing you do before detaching a database is to select * from sysfiles and make sure to save the output. This will show you the locations of the database files.

EDIT: It seems quite likely that all you missed was the location of the file in the attach statement.

And DON'T use the GUI for this. Use T-SQL commands.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 20:23:50

Also know that the file extension can be anything. Nothing enforces mdf, ndf, ldf.

As jeffw8713 said, the restore filelistonly will show u what the files are/were

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-06 : 02:11:31

quote:
Originally posted by hpchris

Now obviously on the face of it the message seems to be saying that there is no LDF file.

Correct, and you need the ldf to be able to attach.

quote:
There was an LDF file before the detach but after the detach there was no longer one and the NDF files had changed, so I assumed that the LDF has somehow been rolled in to the database by the detach process - does that sound right?

No. The detach would simply have removed the reference to the database in SQL. It does not do anything else to the files. If the ldf is missing, it was deleted or moved.

The log is a separate file and separate type of file to the mdf. There's no 'rolling up' that's possible, they are both needed for DB operations.

Also check permissions. It may be there but you didn't have rights to it.

quote:
But rebuilding the log is what I am trying to do with this command.

Means the database wasn't shut down cleanly. You NEED that ldf. While it is possible to hack a database back in without it, it can result in transactional and even physical inconsistencies in the file. It is not something you want to do.

As an aside, got a backup you can restore?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources