4

This is my error I am facing

TITLE: Microsoft.SqlServer.Smo

Set offline failed for Database 'Go3D_Retailer

------------------------------ ADDITIONAL INFORMATION:

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

Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ftrow_Go3D_catalog.ndf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". Database 'Go3D_Retailer' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120)

Background to this error I've been trying to move my destination logshipping database to another physical server for analysis purposes. Because I do not have active directory set up, I had to hack my process by using the same username/password for both the source and destination servers to get the process to work.

Following that, I used this guy's solution to move the destination database to another server. However, this error occurs when I try to bring the database back online.

I don't have an E drive on my server and I have no idea why it's trying to open a file from E drive. I have over a 100gb left on my hard disk so it's definitely not a space issue. This sounds like a bug... Any ideas?

I'm running SQL Server 2008 Enterprise edition on Windows Server 2008 R2 64bit

Nai
  • 743
  • 1
  • 6
  • 24

1 Answers1

2

it looks like the original database had more than one data file (error msg shows file with extension of .ndf). if you are trying to move a database and doing an attach then you will need to attach all of the data files in order for it to be brought online.

SQLRockstar
  • 713
  • 6
  • 10
  • Hey I was just reading your book yesterday! Anyways, I'm so fed up with this entire process and hack. When you mean original database, do you mean the newly created one? The newly created database only has the .mdf and .ndf files. But still, this doesn't explain why it's trying to open from E: drive? – Nai May 14 '10 at 09:07
  • Nai, You stated that you are trying to move your destination logshipping database to another physical server, and the link you provided talks about attaching a database. My remark was focused on making certain that you have all of the data files. quick question: does the current destination logshipping database have multiple data files, and are they on an E: drive? – SQLRockstar May 14 '10 at 10:50
  • the current destination logshipping database has.mdf and .ldf and .ndf files and they are not on E: Drive. They have a different file name as well from the error message. Just to be clear I am trying to move/copy/transfer the existing destination database onto another server and not change the destination of my logshipping. The link is a hack in transferring the db due to the database in read only mode – Nai May 14 '10 at 18:33
  • OK, that is very interesting. what gets returned if you run teh following? RESTORE FILELISTONLY FROM 'your backup file path and name' GO – SQLRockstar May 14 '10 at 19:31