Recovering dropped SQL database

I am in a big trouble ,

accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.

Please help me , how can i recover the database ....

thanks in advance

If you don′t have a backup of the data, I will have to tell you, that there is no UNDO button for that. Sorry.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Recovering a DB using sp_attach_db

Hi, All!
I'am trying to recover a database using sp_attach_db. I just have the .MDF
file. I tried this command, and receive this result:
EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
Server\MSSQL\Data\Contax.mdf'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'contax'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
I don't have the LDF file, but I suppose that SQL Server would create a new
one. Besides, the physical path in the error message has two slashes (after
"Logs" directory), and I found it weird...
Could somebody help me?
Thanks a lot.
MarcosTry using sp_attach_single_file_db ... for the syntax look at BOL
Thanks
GYK
"Marcos Federicce" wrote:

> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a ne
w
> one. Besides, the physical path in the error message has two slashes (afte
r
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos|||GYK,
I already tried sp_attach_single_file_db, but I got the same error message :
)
Thanks anyway
Marcos
"GYK" wrote:
[vbcol=seagreen]
> Try using sp_attach_single_file_db ... for the syntax look at BOL
> Thanks
> GYK
> "Marcos Federicce" wrote:
>|||> I don't have the LDF file, but I suppose that SQL Server would create a new">
> one.
It can, but only under certain circumstances, like db being detached first,
only having one log file
etc. If those are not met, you need luck to get it to work. Apparently, it d
oesn't for you so I
recommend using your most recent backup(s) to get the database back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marcos Federicce" <MarcosFedericce@.discussions.microsoft.com> wrote in mess
age
news:EB863B21-0C15-4684-B985-CB0ABDD3DC93@.microsoft.com...
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a ne
w
> one. Besides, the physical path in the error message has two slashes (afte
r
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos

Recovering a DB using sp_attach_db

Hi, All!
I'am trying to recover a database using sp_attach_db. I just have the .MDF
file. I tried this command, and receive this result:
EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
Server\MSSQL\Data\Contax.mdf'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'contax'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
I don't have the LDF file, but I suppose that SQL Server would create a new
one. Besides, the physical path in the error message has two slashes (after
"Logs" directory), and I found it weird...
Could somebody help me?
Thanks a lot.
Marcos
Try using sp_attach_single_file_db ... for the syntax look at BOL
Thanks
GYK
"Marcos Federicce" wrote:

> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos
|||GYK,
I already tried sp_attach_single_file_db, but I got the same error message
Thanks anyway
Marcos
"GYK" wrote:
[vbcol=seagreen]
> Try using sp_attach_single_file_db ... for the syntax look at BOL
> Thanks
> GYK
> "Marcos Federicce" wrote:
|||> I don't have the LDF file, but I suppose that SQL Server would create a new
> one.
It can, but only under certain circumstances, like db being detached first, only having one log file
etc. If those are not met, you need luck to get it to work. Apparently, it doesn't for you so I
recommend using your most recent backup(s) to get the database back.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marcos Federicce" <MarcosFedericce@.discussions.microsoft.com> wrote in message
news:EB863B21-0C15-4684-B985-CB0ABDD3DC93@.microsoft.com...
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos

Recovering a DB using sp_attach_db

Hi, All!
I'am trying to recover a database using sp_attach_db. I just have the .MDF
file. I tried this command, and receive this result:
EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
Server\MSSQL\Data\Contax.mdf'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'contax'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
I don't have the LDF file, but I suppose that SQL Server would create a new
one. Besides, the physical path in the error message has two slashes (after
"Logs" directory), and I found it weird...
Could somebody help me?
Thanks a lot.
MarcosTry using sp_attach_single_file_db ... for the syntax look at BOL
Thanks
GYK
"Marcos Federicce" wrote:
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos|||GYK,
I already tried sp_attach_single_file_db, but I got the same error message :)
Thanks anyway
Marcos
"GYK" wrote:
> Try using sp_attach_single_file_db ... for the syntax look at BOL
> Thanks
> GYK
> "Marcos Federicce" wrote:
> > Hi, All!
> >
> > I'am trying to recover a database using sp_attach_db. I just have the .MDF
> > file. I tried this command, and receive this result:
> >
> > EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> > Server\MSSQL\Data\Contax.mdf'
> >
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'contax'. CREATE DATABASE is aborted.
> > Device activation error. The physical file name
> > 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> >
> > I don't have the LDF file, but I suppose that SQL Server would create a new
> > one. Besides, the physical path in the error message has two slashes (after
> > "Logs" directory), and I found it weird...
> >
> > Could somebody help me?
> >
> > Thanks a lot.
> >
> > Marcos|||> I don't have the LDF file, but I suppose that SQL Server would create a new
> one.
It can, but only under certain circumstances, like db being detached first, only having one log file
etc. If those are not met, you need luck to get it to work. Apparently, it doesn't for you so I
recommend using your most recent backup(s) to get the database back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marcos Federicce" <MarcosFedericce@.discussions.microsoft.com> wrote in message
news:EB863B21-0C15-4684-B985-CB0ABDD3DC93@.microsoft.com...
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcossql

Recover the data

Hi,
My appln runs on a remote server. A few minutes ago i accidently ran a
'Truncate table' command. Is there anyway to recover it thru Query Analyzer.
The DB Recovery model is SIMPLE and i have the dbOwner permission.
Thanking in Advance
LaraNo. Restore from the latest database backup or re-load the data. You can try
any of the log reading
tools (I've listed some on my links page), but the data is most probably not
in the log anymore
because of simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lara" <lara169@.gmail.com> wrote in message news:%23Pp%23vE$9FHA.3952@.TK2MSFTNGP09.phx.gbl.
.
> Hi,
> My appln runs on a remote server. A few minutes ago i accidently ran a 'Tr
uncate table' command.
> Is there anyway to recover it thru Query Analyzer. The DB Recovery model i
s SIMPLE and i have the
> dbOwner permission.
> Thanking in Advance
> Lara
>|||Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqaa3U$9FHA.1996@.TK2MSFTNGP10.phx.gbl...
> No. Restore from the latest database backup or re-load the data. You can
> try any of the log reading tools (I've listed some on my links page), but
> the data is most probably not in the log anymore because of simple
> recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Lara" <lara169@.gmail.com> wrote in message
> news:%23Pp%23vE$9FHA.3952@.TK2MSFTNGP09.phx.gbl...
>

Recover SQL Database from suspect status

Hi I'm trying to recover database from a suspect status but when I run this
command:
sp_resetstatus webc
sql return me this message:
Prior to updating sysdatabases entry for database 'webc', mode = 0 and
status = 1073741840 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
What is it= What can I do to recover DB?
Thanks.
--
--
Filippo MacchiHi
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
Have you tried to restart SQL Server? Aren't you still available to see your
data?
It seems you have to set your database in emergency mode
update sysdatabases set status=32768 where name='your name'
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> Hi I'm trying to recover database from a suspect status but when I run
this
> command:
> sp_resetstatus webc
> sql return me this message:
> Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> status = 1073741840 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
> What is it= What can I do to recover DB?
> Thanks.
> --
> --
> Filippo Macchi
>|||Hi,
Stop and start the SQL server and try accessing the webc database
use webc
go
select * from sysobjects
If it still gives the error then go thru the below informations:-
Details:-
Suspect database may be due to below reasons.
1. MDF or LDF files may be used during the SQL Server service startup
2. LDF file might be corrupt or immediate power shutdown caused the LDF to
corrupt
3. MDF file - Page allocations issue
For the point 1.
Just Run sp_resetstatus <dbname> and restart SQL server (This you have done
already)
For the point 2. ( LDF file might be corrupt or immediate power shutdown
caused the LDF to corrupt)
a. Start SQL Server in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = 'webc'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode.
After this database will be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
For point 3. Very critical error , try executing DBCC CHECKDB with
REPAIR_REBUILD option. If the problem is not rectified try
with restore from Backup or contact Microsoft support.
Thanks
Hari
MCDBA
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> Hi I'm trying to recover database from a suspect status but when I run
this
> command:
> sp_resetstatus webc
> sql return me this message:
> Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> status = 1073741840 (status suspect_bit = 0).
> No row in sysdatabases was updated because mode and status are already
> correctly reset. No error and no changes made.
> What is it= What can I do to recover DB?
> Thanks.
> --
> --
> Filippo Macchi
>|||I run the command and received this message, is it correct?:
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
"Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> Hi
> > No row in sysdatabases was updated because mode and status are already
> > correctly reset. No error and no changes made.
> Have you tried to restart SQL Server? Aren't you still available to see
your
> data?
> It seems you have to set your database in emergency mode
> update sysdatabases set status=32768 where name='your name'
>
> "Azkaban" <azkaban74@.libero.it> wrote in message
> news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > Hi I'm trying to recover database from a suspect status but when I run
> this
> > command:
> >
> > sp_resetstatus webc
> >
> > sql return me this message:
> >
> > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > status = 1073741840 (status suspect_bit = 0).
> > No row in sysdatabases was updated because mode and status are already
> > correctly reset. No error and no changes made.
> >
> > What is it= What can I do to recover DB?
> >
> > Thanks.
> >
> > --
> > --
> > Filippo Macchi
> >
> >
>|||Hi
Sp_configure "allow updates", 1
go
Reconfigure with override
go
Update sysdatabases set status = 32768 where name = 'yourname'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
go
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uyJxIXWQEHA.904@.TK2MSFTNGP12.phx.gbl...
> I run the command and received this message, is it correct?:
> Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
administrator
> must reconfigure SQL Server to allow this.
>
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> > Hi
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > Have you tried to restart SQL Server? Aren't you still available to see
> your
> > data?
> >
> > It seems you have to set your database in emergency mode
> > update sysdatabases set status=32768 where name='your name'
> >
> >
> >
> > "Azkaban" <azkaban74@.libero.it> wrote in message
> > news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi I'm trying to recover database from a suspect status but when I run
> > this
> > > command:
> > >
> > > sp_resetstatus webc
> > >
> > > sql return me this message:
> > >
> > > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > > status = 1073741840 (status suspect_bit = 0).
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > >
> > > What is it= What can I do to recover DB?
> > >
> > > Thanks.
> > >
> > > --
> > > --
> > > Filippo Macchi
> > >
> > >
> >
> >
>|||Hi,
Can you go thru the steps specified by me in the previous post. That
contains the detailed information on recovering from the suspect status.
Thanks
Hari
MCDBA
"Azkaban" <azkaban74@.libero.it> wrote in message
news:uyJxIXWQEHA.904@.TK2MSFTNGP12.phx.gbl...
> I run the command and received this message, is it correct?:
> Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
administrator
> must reconfigure SQL Server to allow this.
>
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uWWEfRWQEHA.3660@.tk2msftngp13.phx.gbl...
> > Hi
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > Have you tried to restart SQL Server? Aren't you still available to see
> your
> > data?
> >
> > It seems you have to set your database in emergency mode
> > update sysdatabases set status=32768 where name='your name'
> >
> >
> >
> > "Azkaban" <azkaban74@.libero.it> wrote in message
> > news:uZL0MLWQEHA.556@.tk2msftngp13.phx.gbl...
> > > Hi I'm trying to recover database from a suspect status but when I run
> > this
> > > command:
> > >
> > > sp_resetstatus webc
> > >
> > > sql return me this message:
> > >
> > > Prior to updating sysdatabases entry for database 'webc', mode = 0 and
> > > status = 1073741840 (status suspect_bit = 0).
> > > No row in sysdatabases was updated because mode and status are already
> > > correctly reset. No error and no changes made.
> > >
> > > What is it= What can I do to recover DB?
> > >
> > > Thanks.
> > >
> > > --
> > > --
> > > Filippo Macchi
> > >
> > >
> >
> >
>

Recover db from .mdf and .ldf files in sql server7.0

Hello,
Have you tried the command sp_attach_single_file_db ?
This will attach you datafile, but not your log file as it
seems the log file is currupt, so you may have some data
loss.
J

>--Original Message--
>Hi,
>how to recover a user database in sql server 7.0 if I
only have the .mdf file and the .ldf file but no backup
file? Backup file of master database exists.
>Found a utiliy tool called "MSSQLRecovery" on the net
that recreates the database script out of the .mdf-file,
so far so good. But I need the information in the .ldf
file too.. How to get the information in the .ldf file?
>Thanks.
>.
>Yes,
I have tried sp_attach_single_file_db, but as you say I will get some data l
oss with this method.
My problem is the log file never seems to been backuped, so I guess I will g
et quite a lot of data loss.
Thanks,
Siri|||That really depends upon how you have programmed your
application. Depending upon the last commit statement or
chekpoint you may of lost a lot less data than you think.
I strongly doubt if it will weeks, days or hours of lost
information, personally I think it will be minutes.
Try attaching anyway then seeing if there any timestap
fields in you app, when the last one was. That should give
you an indication.
J

>--Original Message--
>Yes,
>I have tried sp_attach_single_file_db, but as you say I
will get some data loss with this method.
>My problem is the log file never seems to been backuped,
so I guess I will get quite a lot of data loss.
>Thanks,
>Siri
>.
>

Recover db from .mdf and .ldf files in sql server7.0

Hello,
Have you tried the command sp_attach_single_file_db ?
This will attach you datafile, but not your log file as it
seems the log file is currupt, so you may have some data
loss.
J

>--Original Message--
>Hi,
>how to recover a user database in sql server 7.0 if I
only have the .mdf file and the .ldf file but no backup
file? Backup file of master database exists.
>Found a utiliy tool called "MSSQLRecovery" on the net
that recreates the database script out of the .mdf-file,
so far so good. But I need the information in the .ldf
file too.. How to get the information in the .ldf file?
>Thanks.
>.
>
Yes,
I have tried sp_attach_single_file_db, but as you say I will get some data loss with this method.
My problem is the log file never seems to been backuped, so I guess I will get quite a lot of data loss.
Thanks,
Siri
|||That really depends upon how you have programmed your
application. Depending upon the last commit statement or
chekpoint you may of lost a lot less data than you think.
I strongly doubt if it will weeks, days or hours of lost
information, personally I think it will be minutes.
Try attaching anyway then seeing if there any timestap
fields in you app, when the last one was. That should give
you an indication.
J

>--Original Message--
>Yes,
>I have tried sp_attach_single_file_db, but as you say I
will get some data loss with this method.
>My problem is the log file never seems to been backuped,
so I guess I will get quite a lot of data loss.
>Thanks,
>Siri
>.
>

recording query

Hello,

How can I record certain type of querys ? I would like to record
delete command sent to a specific database, and using a specific
login account.

This "query capture" should run in background because I dont know
the exact time someone will delete a record.

Im using SQL-Server 7.0

Thank you,

Eduardo.eakinto@.buscape.com.br (Eduardo) wrote in message news:<693f7309.0404071351.dc027a@.posting.google.com>...
> Hello,
> How can I record certain type of querys ? I would like to record
> delete command sent to a specific database, and using a specific
> login account.
> This "query capture" should run in background because I dont know
> the exact time someone will delete a record.
> Im using SQL-Server 7.0
> Thank you,
> Eduardo.

Profiler would be the first place to look, or perhaps a trace.

Simon