Hello,
I am still trying to get my head around this one. I have been working
on a new version of a website and decided I wanted to make a backup of
the old database file before I tried running any scripts against it.
Since the production website is currently down, I decided to detach
the database, copy the file, and then reattach it. Instead of using
scripts, I used MS SQL Server Management Studio to handle the task.
After I ran the detach command on the database (with the default
options selected), I went to the directory where all of the database
files are stored on the server and the file doesn't exist!!
So, I tried to back up the database file because I didn't have a copy,
and now I don't even have the original. I did a complete Windows file
search on my entire network to try to locate the file, but it is
gone. Well, it isn't the end of the world in my case because I can go
to a prior backup and just move forward, but I really would like to
know what happened so it doesn't happen again.
For starters, is there any way I can query a system table to determine
what the file location was of the database I detached? Second, is
there some "memory only" mode that a database can be put in so when it
is detached it will disappear completely?
TIAHi
It is really strange . I did just at least ten testes to detach the database
via SSMS and it worked just fine
I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
another non-produiction database?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||Sounds weird. No chance SSMS to drop a database when you detach it. There is
only "drop connection" option which contains "drop" action and it doesn't
drop the database.
You said you did a complete seach on your HDD(s) but I guess it must be
around somewhere on your HDD. Maybe you forgot it's name or something?
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> It is really strange . I did just at least ten testes to detach the database
> via SSMS and it worked just fine
> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
> another non-produiction database?
>
I tried creating a new sample database on the same server and
detaching it, but the files remained. So to your answer your
question, no I cannot repeat the problem.
I also know for sure I used the detach and not the delete command on
the previous database because the dialogs are different.
Is there any SQL command you know of that I can use to determine what
exact file location was detached?|||On Aug 30, 12:24 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There= is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem =D6nsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
Its probably where all your user databases are located isn't it?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458888.053113.228460@.x35g2000prf.googlegroups.com...
>
> On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> It is really strange . I did just at least ten testes to detach the
>> database
>> via SSMS and it worked just fine
>> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem
>> for
>> another non-produiction database?
> I tried creating a new sample database on the same server and
> detaching it, but the files remained. So to your answer your
> question, no I cannot repeat the problem.
> I also know for sure I used the detach and not the delete command on
> the previous database because the dialogs are different.
> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
>|||Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
check the file location.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188459431.752325.247190@.x35g2000prf.googlegroups.com...
On Aug 30, 12:24 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem Önsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||On Aug 30, 1:19 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
> check the file location.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
Actually all I have is a database that I created on another computer
and then used DTS to copy tables with data over to it. This was done
before I upgraded to SQL 2005. I don't technically have a "backup" in
the SQL Server sense of the word.|||Do you have an old backup of the master database on the computer where the database existed? If so,
you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
file, but that might be good enough...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...
> On Aug 30, 1:19 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
>> check the file location.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Actually all I have is a database that I created on another computer
> and then used DTS to copy tables with data over to it. This was done
> before I upgraded to SQL 2005. I don't technically have a "backup" in
> the SQL Server sense of the word.
>|||No, I don't have any backups of the master database. This server has
been offline for almost a year. I was just in the process of backing
up the data in the primary database on the server by making a copy of
the file. I plan to set up backups going forward but I didn't have
any before (which is why I decided to detach the database in the first
place).
The file either disappeared when I ran the command to detach it or it
didn't exist before that. If it didn't exist that is another
inexplicable problem - I know I left it in place. Not to mention, I
didn't get any sql errors that the file was missing or anything like
that.
On Aug 30, 3:08 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have an old backup of the master database on the computer where the database existed? If so,
> you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
> file, but that might be good enough...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...|||I figured out the problem. The production database file was prefixed
by the word "Test", the same as the test database. They were in 2
different directories, but they were all on the same server. I
thought that the production database was gone, but it was just named
"Test".
At some point I must have copied the Test database and attached it as
production. This makes sense because still now I am having issues
moving tables from one database to another.
Well, I am glad that I don't have to revert to an older copy and lose
some data. I will definitely back up master and set up some backup
jobs now, too.|||backup...backup..backup...;-)
Make it a best practice to always backup the master database anytime you do
something on the server even if the server is an old one. You'll never know
when it will become handy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188520731.793321.109640@.q3g2000prf.googlegroups.com...
>I figured out the problem. The production database file was prefixed
> by the word "Test", the same as the test database. They were in 2
> different directories, but they were all on the same server. I
> thought that the production database was gone, but it was just named
> "Test".
> At some point I must have copied the Test database and attached it as
> production. This makes sense because still now I am having issues
> moving tables from one database to another.
> Well, I am glad that I don't have to revert to an older copy and lose
> some data. I will definitely back up master and set up some backup
> jobs now, too.
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment