Recovery of dropped data that was never back up

Hi

I am trying to recover a set of tables with data that was dropped. This data was not backed up. Please advice, is there any way this data can be recovered.

With SQL, the way to go back in time is to restore backups. You really can't recover dropped tables without any backups.

Depending on how valuable and critical the information is (which begs the obvious question of why it wasn't backed up if it was valuable), you may be able to do some forensic data mining on the files themselves. The data is not actually overwritten, just marked as free mujch as the filesystem does. So as long as the space has not been reused, there may still be information out there. SQL has no utilities for this, however.

You can either examine the raw files with a hex editor, or someone may know of a 3rd-party utility to make it slightly easier. None of this is of course supported.

Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
Regards,
Prashant C
Oops, no backups!!!
Then its gone...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Prashant C" wrote:

> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C
|||Unless some Widows utility can recovery the deleted database files. If so, you could try
sp_attach_db for the files, but that is not guaranteed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...[vbcol=seagreen]
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
sql

Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
--
Regards,
Prashant COops, no backups!!!
Then its gone...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Prashant C" wrote:

> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C|||Unless some Widows utility can recovery the deleted database files. If so, y
ou could try
sp_attach_db for the files, but that is not guaranteed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...[vbcol=seagreen]
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
>

Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
--
Regards,
Prashant COops, no backups!!!
Then its gone...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Prashant C" wrote:
> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C|||Unless some Widows utility can recovery the deleted database files. If so, you could try
sp_attach_db for the files, but that is not guaranteed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
>> By mistake I deleted the my application database, and I dont have Back up
>> also...
>> is there any way to recover it...
>>
>> Please help me.. its very urgent
>> --
>> Regards,
>> Prashant C

Recovery Model VS Back Up Types

Does anyone have a lead on where I can find out what Back Up Type to use with
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.Rich,
For a quick overview, check
http://www.databasejournal.com/features/mssql/article.php/2232371.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.|||Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the database
> will not be restored correctly.|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.

Recovery Model VS Back Up Types

Does anyone have a lead on where I can find out what Back Up Type to use with
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.
Rich,
For a quick overview, check
http://www.databasejournal.com/featu...e.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.
|||Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the database
> will not be restored correctly.
|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas

"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.

Recovery Model VS Back Up Types

Does anyone have a lead on where I can find out what Back Up Type to use wit
h
which Recovery Model? Something like "standard combinations" or a matrix
that has both elements but how each interacts with each other?
If I pick a Recovery Model and do not perform the correct Back, the database
will not be restored correctly.Rich,
For a quick overview, check
http://www.databasejournal.com/feat...le.php/2232371.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F62E5D6-9DD0-4160-98BB-A4F3ED174CEB@.microsoft.com...
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.|||Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use w
ith
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the databa
se
> will not be restored correctly.|||Although I agree, a little more refinement for the BULK LOGGED Recovery
Model:
Although you can not restore to a point-in-time, you can back up and restore
transaction logs, where you can restore to any point-in-time prior to the
Bulk Logged information or to the end of any transaction log backup after
the Bulk Logged operation in between FULL and/or Differential backups.
So, if you'd like point-in-time recovery, perform at least a Differential
Backup just after a Bulk Logged operation.
Sincerely,
Anthony Thomas
"Mark Allison" <mark@.no.tinned.meat.mvps.org> wrote in message
news:e7euNGQCFHA.2032@.tk2msftngp13.phx.gbl...
Very brief overview:
Simple
--
Full and differential backups only.
Cannot restore to a point in time, only since last backup.
Bulk-logged
--
Similar to Full, except minimally-logged operations are allowed.
Cannot recover to a point in time if a minimally logged operation has
occurred. Good for large batch processes with bcp, or select..into.
Full
--
Everything is logged including bulk loads. Bulk load operations will be
slower in full, than bulk-logged.
Can restore to a point in time if the transaction log can be backed up
after the failure. Easy to switch to bulk-logged mode.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rich wrote:
> Does anyone have a lead on where I can find out what Back Up Type to use
with
> which Recovery Model? Something like "standard combinations" or a matrix
> that has both elements but how each interacts with each other?
> If I pick a Recovery Model and do not perform the correct Back, the
database
> will not be restored correctly.

Recovery model setup using Tsql

hi,
i need to change my recovery model to SIMPLE but using TSQL and once
installationn is finished i need to revert back to FULL
can anyone Help.
--
SunnyHi
ALTER DATABASE test SET RECOVERY FULL
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B1DD8BA0-1BE1-434C-9A12-6E3F043E1FE6@.microsoft.com...
> hi,
> i need to change my recovery model to SIMPLE but using TSQL and once
> installationn is finished i need to revert back to FULL
> can anyone Help.
> --
> Sunny|||ALTER DATABASE dbname SET RECOVERY SIMPLE
...
ALTER DATABASE dbname SET RECOVERY FULL
Note that transaction log backups now takes are useless (SQL Server should g
ive an error message but
it doesn't, fixed in 2005), until you first do a database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B1DD8BA0-1BE1-434C-9A12-6E3F043E1FE6@.microsoft.com...
> hi,
> i need to change my recovery model to SIMPLE but using TSQL and once
> installationn is finished i need to revert back to FULL
> can anyone Help.
> --
> Sunnysql

recovery mode

we back our network up using veritas backup exec. the
backup fails; it wants me to change the recovery mode from
simple to full. Will this cause problems on my SQL server?You should read in Books Online about backup and recovery models. This is a too big topic to handle
in a newsgroup post. Most probably it will be OK, but if you don't backup log often enough, it might
become quite large.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?|||Hi ,
It wont create any issues in your SQL Server, Only thing is
take a backup of your Transaction logs based on the transaction volume.
Otherwise since FULL recovary model logs all transaction happening in the
database, which will cause your Log file to grow like any thing.
Thanks
Hari
MCDBA
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?|||To add to the other responses, note that you'll need to perform a full
database backup after changing the recovery model from SIMPLE to FULL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?sql

recovering database plz help

My server crashed i had full back up my .BAK file for every database and i reinstalled a fresh verion of sql server 2000 enterprise and when i comme in enterprise manger to restor the master its telling me i need to be in single user mode what do i do.Change it to single user mode. use sp_dboption.|||sp_dboption pertains to user databases, not to MASTER.

BOL for SQLSERVR command line:

-m

Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode. The CHECKPOINT mechanism, which guarantees that completed transactions are regularly written from the disk cache to the database device, is not started. (Typically, this option is used if you experience problems with system databases that require repair.)sql

Recovering data from truncated table.

I have accidentally truncated a table, and would like to get the data back.
Here are the parameters:
1. The database is using the full recovery model
2. A full backup has never been done.
3. A transaction log backup has never been done.
Since the log was never truncated, theoretically all the data is still in
the log file, but can't be retrieved normally since a full backup is
unavailable.
I tried the apexSQL Log program. Here are the results:
1. It reports that it discovered about 2500 out of about 45000 rows in a
detached copy of the log file. 2500 is the number of rows inserted after th
e
truncation.
2. When you try to use the "Recover Truncated Data" option, it errors out,
reporting that "file not found"
Any ideas on what I should try next?Enkidu,
Might try calling Microsoft Product Support and see if they can work some
magic.
In the future however, I'd recommend systematic full and t-log backups for
the database. You might also consider issuing possible "damaging" admin
statements in a user-defined transaction.
HTH
Jerry
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after
> the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors
> out,
> reporting that "file not found"
> Any ideas on what I should try next?|||Jerry,
Thanks for the advice. The data was performance data we were collecting
that actually truncated during the creation of a DTS package.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uCpb9LcyFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Enkidu,
> Might try calling Microsoft Product Support and see if they can work some
> magic.
> In the future however, I'd recommend systematic full and t-log backups for
> the database. You might also consider issuing possible "damaging" admin
> statements in a user-defined transaction.
> HTH
> Jerry|||> Since the log was never truncated, theoretically all the data is still in
> the log file
Nope. Since you never did a database backup, the database is in "log auto-tr
uncate mode"
(essentially the same as simple recovery mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after
the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors out
,
> reporting that "file not found"
> Any ideas on what I should try next?

Recovering data from truncated table.

I have accidentally truncated a table, and would like to get the data back.
Here are the parameters:
1. The database is using the full recovery model
2. A full backup has never been done.
3. A transaction log backup has never been done.
Since the log was never truncated, theoretically all the data is still in
the log file, but can't be retrieved normally since a full backup is
unavailable.
I tried the apexSQL Log program. Here are the results:
1. It reports that it discovered about 2500 out of about 45000 rows in a
detached copy of the log file. 2500 is the number of rows inserted after the
truncation.
2. When you try to use the "Recover Truncated Data" option, it errors out,
reporting that "file not found"
Any ideas on what I should try next?
Enkidu,
Might try calling Microsoft Product Support and see if they can work some
magic.
In the future however, I'd recommend systematic full and t-log backups for
the database. You might also consider issuing possible "damaging" admin
statements in a user-defined transaction.
HTH
Jerry
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after
> the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors
> out,
> reporting that "file not found"
> Any ideas on what I should try next?
|||Jerry,
Thanks for the advice. The data was performance data we were collecting
that actually truncated during the creation of a DTS package.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uCpb9LcyFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Enkidu,
> Might try calling Microsoft Product Support and see if they can work some
> magic.
> In the future however, I'd recommend systematic full and t-log backups for
> the database. You might also consider issuing possible "damaging" admin
> statements in a user-defined transaction.
> HTH
> Jerry
|||> Since the log was never truncated, theoretically all the data is still in
> the log file
Nope. Since you never did a database backup, the database is in "log auto-truncate mode"
(essentially the same as simple recovery mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors out,
> reporting that "file not found"
> Any ideas on what I should try next?

Recovering data from truncated table.

I have accidentally truncated a table, and would like to get the data back.
Here are the parameters:
1. The database is using the full recovery model
2. A full backup has never been done.
3. A transaction log backup has never been done.
Since the log was never truncated, theoretically all the data is still in
the log file, but can't be retrieved normally since a full backup is
unavailable.
I tried the apexSQL Log program. Here are the results:
1. It reports that it discovered about 2500 out of about 45000 rows in a
detached copy of the log file. 2500 is the number of rows inserted after the
truncation.
2. When you try to use the "Recover Truncated Data" option, it errors out,
reporting that "file not found"
Any ideas on what I should try next?Enkidu,
Might try calling Microsoft Product Support and see if they can work some
magic.
In the future however, I'd recommend systematic full and t-log backups for
the database. You might also consider issuing possible "damaging" admin
statements in a user-defined transaction.
HTH
Jerry
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after
> the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors
> out,
> reporting that "file not found"
> Any ideas on what I should try next?|||Jerry,
Thanks for the advice. The data was performance data we were collecting
that actually truncated during the creation of a DTS package.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uCpb9LcyFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Enkidu,
> Might try calling Microsoft Product Support and see if they can work some
> magic.
> In the future however, I'd recommend systematic full and t-log backups for
> the database. You might also consider issuing possible "damaging" admin
> statements in a user-defined transaction.
> HTH
> Jerry|||> Since the log was never truncated, theoretically all the data is still in
> the log file
Nope. Since you never did a database backup, the database is in "log auto-truncate mode"
(essentially the same as simple recovery mode).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors out,
> reporting that "file not found"
> Any ideas on what I should try next?

Recovering a deleted database.

I accidentally deleted at SQL 2005 database. Is there any way I can get it back? No, there is no backup.

unfortunately the answer is no. here is the significance of proper backup policy. we understand it only when the emergency comes. If you don't have backup there is no way to get it back. here onward pse schedule a periodic backup to be on safer side

Madhu

|||

YES!

But, unfortunately, it requires you to recreate it all manually, including typing in the data again. Bummer!

Recover System Stored Procedure

I mistakenly deleted the system stored procedure 'sp_columns' from my MSDE. Any idea about how to recover/reinstall it back without reinstalling the entire MSDE? Please help. Thanks.

you could restore your master database if you've a recent copy. Alternatively, the following script will recreate it. You'll need to either run it via a GUI tool, or else use osql from Dos.

use master
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/* Procedure for 8.0 server */
CREATE PROCEDURE sp_columns (
@.table_name nvarchar(384),
@.table_owner nvarchar(384) = null,
@.table_qualifier sysname = null,
@.column_name nvarchar(384) = null,
@.ODBCVer int = 2)
AS
DECLARE @.full_table_name nvarchar(769)
DECLARE @.table_id int

if @.ODBCVer <> 3
select @.ODBCVer = 2
if @.column_name is null /* If column name not supplied, match all */
select @.column_name = '%'
if @.table_qualifier is not null
begin
if db_name() <> @.table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @.table_name is null
begin /* If table name not supplied, match all */
select @.table_name = '%'
end
if @.table_owner is null
begin /* If unqualified table name */
SELECT @.full_table_name = quotename(@.table_name)
end
else
begin /* Qualified table name */
if @.table_owner = ''
begin /* If empty owner name */
SELECT @.full_table_name = quotename(@.table_owner)
end
else
begin
SELECT @.full_table_name = quotename(@.table_owner) +
'.' + quotename(@.table_name)
end
end

/* Get Object ID */
SELECT @.table_id = object_id(@.full_table_name)
if ((isnull(charindex('%', @.full_table_name),0) = 0) and
(isnull(charindex('[', @.table_name),0) = 0) and
(isnull(charindex('[', @.table_owner),0) = 0) and
(isnull(charindex('_', @.full_table_name),0) = 0) and
@.table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @.table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */

if @.table_owner is null /* If owner not supplied, match all */
select @.table_owner = '%'

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @.table_name
AND user_name(o.uid) like @.table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 2, 3, 17
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Cathal, thanks a lot!

recover sql db with only data file

I accidentally deleted transaction log file on my test sql server, I can't get my database back now.
Does anybody know how to recover a database with only datafile? (.mdf and ndf)
Thanks in advanceThe only option left is to use DBCC REBUILD_LOG and when the transaction log for a database is lost or damaged. In such a case, it is very likely there are unapplied transactions lost with the log, and the database is therefore inconsistent. Also, the event that causes the loss of the transaction log, such as a hardware failure, may have damaged the database.

KBA -Q232518 refers the same.|||Thank you so much for your quick reply. I'll try it.

Originally posted by Satya
The only option left is to use DBCC REBUILD_LOG and when the transaction log for a database is lost or damaged. In such a case, it is very likely there are unapplied transactions lost with the log, and the database is therefore inconsistent. Also, the event that causes the loss of the transaction log, such as a hardware failure, may have damaged the database.

KBA -Q232518 refers the same.sql

Recover Lost data files?

Hi
When we issue a DBCC SHRINKFILE with empty option, the data file
is removed and the space is alloted back to the OS.
Same way when we issue a an ALTER DATABASE to remove a database,
all the data files and transaction log files are dropped.
But can these files be recovered again by using any of the disk
recovery tools?

AchillesDBCC SHRINKFILE doesn't remove a data file it just removes unused space in a
file. ALTER DATABASE doesn't remove databases, although it can be used to
remove files (but only when they are empty of data). Since neither of these
statements actually removes any data from your database there isn't really
anything to recover. I doubt it would be feasible to restore a database to a
prior state using disk recovery tools and I definitely wouldn't recommend
you try as the likely result would be a corrupt database.

If you think you have lost some data for some reason then restoring from a
backup is your best course of action. But as I said, neither of the
statements you have mentioned should cause you to lose data even if you use
them in error.

Hope this helps.

--
David Portas
SQL Server MVP
--

Recover data from Tables Sql2000

Good Morning,
We have a Win2003 server with SQL2000 running on it. We have an Access Order
Entry application front end with the back end on a database. I was updating
some product pricing in our database and I accidentally DELETED about 360
products. I imported a table with new pricing, and I was updating the prices
in our database with the new prices from an excel spreadsheet.
What is the fastest way I can get back those products? Can I do a roll back
with the trans. log?
Or should I use a backup copy of the database? My automatic maintenance runs
twice a week, so I have backup of the database and logs. But I don't want to
restore the whole database, maybe just one table in the database. Thanks!
Merry Christmas!
Anthony...
Although you can't restore a single table, you can restore the entire
database to a different database name and apply your log backups, if needed.
This will allow you to extract the lost data for insertion into your live
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Anthony Smith" <anthony@.peconet.com> wrote in message
news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> We have a Win2003 server with SQL2000 running on it. We have an Access
> Order
> Entry application front end with the back end on a database. I was
> updating
> some product pricing in our database and I accidentally DELETED about 360
> products. I imported a table with new pricing, and I was updating the
> prices
> in our database with the new prices from an excel spreadsheet.
> What is the fastest way I can get back those products? Can I do a roll
> back
> with the trans. log?
> Or should I use a backup copy of the database? My automatic maintenance
> runs
> twice a week, so I have backup of the database and logs. But I don't want
> to
> restore the whole database, maybe just one table in the database. Thanks!
> Merry Christmas!
> Anthony...
>
>
|||Thanks for your help. I did it and it work. I'm a SQL novice so I haven't
done much with SQL logs other than back them up.
Have a Merry Christmas!
Anthony...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHjywKP6EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Although you can't restore a single table, you can restore the entire
> database to a different database name and apply your log backups, if
> needed. This will allow you to extract the lost data for insertion into
> your live database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
>
|||That's better than most novices; at least you know they exist and have the
sense to back them up.
Sincerely,
Anthony Thomas

"Anthony Smith" <anthony@.peconet.com> wrote in message
news:eMsKduP6EHA.1596@.tk2msftngp13.phx.gbl...
Thanks for your help. I did it and it work. I'm a SQL novice so I haven't
done much with SQL logs other than back them up.
Have a Merry Christmas!
Anthony...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHjywKP6EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Although you can't restore a single table, you can restore the entire
> database to a different database name and apply your log backups, if
> needed. This will allow you to extract the lost data for insertion into
> your live database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
>

Recover data from Tables Sql2000

Good Morning,
We have a Win2003 server with SQL2000 running on it. We have an Access Order
Entry application front end with the back end on a database. I was updating
some product pricing in our database and I accidentally DELETED about 360
products. I imported a table with new pricing, and I was updating the prices
in our database with the new prices from an excel spreadsheet.
What is the fastest way I can get back those products? Can I do a roll back
with the trans. log?
Or should I use a backup copy of the database? My automatic maintenance runs
twice a week, so I have backup of the database and logs. But I don't want to
restore the whole database, maybe just one table in the database. Thanks!
Merry Christmas!
Anthony...Although you can't restore a single table, you can restore the entire
database to a different database name and apply your log backups, if needed.
This will allow you to extract the lost data for insertion into your live
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Anthony Smith" <anthony@.peconet.com> wrote in message
news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> We have a Win2003 server with SQL2000 running on it. We have an Access
> Order
> Entry application front end with the back end on a database. I was
> updating
> some product pricing in our database and I accidentally DELETED about 360
> products. I imported a table with new pricing, and I was updating the
> prices
> in our database with the new prices from an excel spreadsheet.
> What is the fastest way I can get back those products? Can I do a roll
> back
> with the trans. log?
> Or should I use a backup copy of the database? My automatic maintenance
> runs
> twice a week, so I have backup of the database and logs. But I don't want
> to
> restore the whole database, maybe just one table in the database. Thanks!
> Merry Christmas!
> Anthony...
>
>|||Thanks for your help. I did it and it work. I'm a SQL novice so I haven't
done much with SQL logs other than back them up.
Have a Merry Christmas!
Anthony...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHjywKP6EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Although you can't restore a single table, you can restore the entire
> database to a different database name and apply your log backups, if
> needed. This will allow you to extract the lost data for insertion into
> your live database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
>> Good Morning,
>> We have a Win2003 server with SQL2000 running on it. We have an Access
>> Order
>> Entry application front end with the back end on a database. I was
>> updating
>> some product pricing in our database and I accidentally DELETED about 360
>> products. I imported a table with new pricing, and I was updating the
>> prices
>> in our database with the new prices from an excel spreadsheet.
>> What is the fastest way I can get back those products? Can I do a roll
>> back
>> with the trans. log?
>> Or should I use a backup copy of the database? My automatic maintenance
>> runs
>> twice a week, so I have backup of the database and logs. But I don't want
>> to
>> restore the whole database, maybe just one table in the database.
>> Thanks!
>> Merry Christmas!
>> Anthony...
>>
>|||That's better than most novices; at least you know they exist and have the
sense to back them up.
Sincerely,
Anthony Thomas
"Anthony Smith" <anthony@.peconet.com> wrote in message
news:eMsKduP6EHA.1596@.tk2msftngp13.phx.gbl...
Thanks for your help. I did it and it work. I'm a SQL novice so I haven't
done much with SQL logs other than back them up.
Have a Merry Christmas!
Anthony...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OHjywKP6EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Although you can't restore a single table, you can restore the entire
> database to a different database name and apply your log backups, if
> needed. This will allow you to extract the lost data for insertion into
> your live database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:upQJhAP6EHA.2016@.TK2MSFTNGP15.phx.gbl...
>> Good Morning,
>> We have a Win2003 server with SQL2000 running on it. We have an Access
>> Order
>> Entry application front end with the back end on a database. I was
>> updating
>> some product pricing in our database and I accidentally DELETED about 360
>> products. I imported a table with new pricing, and I was updating the
>> prices
>> in our database with the new prices from an excel spreadsheet.
>> What is the fastest way I can get back those products? Can I do a roll
>> back
>> with the trans. log?
>> Or should I use a backup copy of the database? My automatic maintenance
>> runs
>> twice a week, so I have backup of the database and logs. But I don't want
>> to
>> restore the whole database, maybe just one table in the database.
>> Thanks!
>> Merry Christmas!
>> Anthony...
>>
>

recover a stored procedure

If I can't connect to my database and my test server requires a reinstall of SQL Server 2005, how can I get my one stored proc out of there and back it up if I can't even connect to my database? Is there a file that is created that may have this? I know it's probably stored in master or something but not sure if it can be recovered.Before reinstalling the SQL Server, make a copy of the database files (.MDF and .LDF) in which you created your stored proc. When the new instance is installed, attach the DB and retrieve your stored proc from there.