Sunday, March 11, 2012

detached database disappears?

Hi All:
Running SQL2005 sp1 on Windows Server 2003 R2.
I have Management Studio installed locally on the server. I connected to the
local instance, backed up a database with no problems, and then detached the
database, and now the physical files are gone? not in the "data" folder, not
anywhere...I still have the backup file, but I'm a little freaked out by
this.
Has anyone experience this? I'm logged in as the administrator so I don't
think the files are hidden from view (all the other databases are in the
data folder)
> I have Management Studio installed locally on the server. I connected to
> the local instance, backed up a database with no problems, and then
> detached the database, and now the physical files are gone? not in the
> "data" folder, not anywhere...I still have the backup file, but I'm a
> little freaked out by this.
> Has anyone experience this?
No, I've never seen this. Are you sure you searched all of the drives,
since often we don't place user databases under the program files structure,
but rather on a separate disk altogether.
|||>> I have Management Studio installed locally on the server. I connected to
> No, I've never seen this. Are you sure you searched all of the drives,
> since often we don't place user databases under the program files
> structure, but rather on a separate disk altogether.
Yeah, there's only one drive/partition on the system, and I've searched
everywhere...it's g-o-n-e.
I performed a full dB backup as well as TLog backup before detaching it. I'm
having trouble restoring from the backup now.
First, I tried to restore by typing in the name of the dB in the "to
database" and attaching the backup file. The backup shows the name of the dB
in the backup set, but when I select the "full database backup" and click
OK, I get a strange error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
over the existing "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
to identify an alternate location. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
BUT the database name referenced in the error is NOT the database I'm trying
to restore!
So, I then created a new database with the same name as the one I detached,
and selected "restore" and went through the same process, and I then get a
different error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The backup set holds a backup of a database other than the existing
'EMS_Ultimate' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
3154)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
EMS_Ultimate is the dB I'm trying to restore! And that's the name of the dB
in the backup file...now I'm really nervous!
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||
> Sounds to me like the MDF file is still in the Data folder.
> And the error message references the name of the *file* no the name of the
> database. They do not have to be the same.
>
OK, this is very bizarre...but when I clicked on the "options" in the
"restore database" window, all the settings seemed to be from a previous
restore I did months and months ago on a different database...I don't
understand why those settings would be preserved, when on the first panel
("general") I'm inputting completely different information and selecting a
completely different backup file, but I guess that's one of the mysteries of
the SQL2005.
The errors below were occuring because the MDF and LDF files existed for the
database specified in the options pane...not for the one I was trying to
restore. When I modified the settings in the "options" pane it worked
properly...oh well...at least I have the data back...I was worried the wrong
database was in the backup file.
Thanks for bearing with me!

>
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
>

No comments:

Post a Comment