How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
--
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> > Hi all
> >
> > I am having some disk space issue, and I notice that I have some tables
> > with
> > a lot of GB of unused space.
> >
> > Is it a way to claim and release that unused space?
> >
> > To give you a better picture, I have just one table with below information
> > Rows = 131977895
> > Reserved =146270344 KB
> > Index_Size = 2767616 KB
> > Data = 70372568 KB
> > Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> >
> >
> > This table is reindexed every sunday (yesterday was the last reindex) and
> > it
> > does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> > get these results:
> > DBCC SHOWCONTIG scanning 'Sales' table...
> > Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> > TABLE level scan performed.
> > - Pages Scanned........................: 8796978
> > - Extents Scanned.......................: 1101956
> > - Extent Switches.......................: 1101956
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> > - Logical Scan Fragmentation ..............: 0.04%
> > - Extent Scan Fragmentation ...............: 9.80%
> > - Avg. Bytes Free per Page................: 647.0
> > - Avg. Page Density (full)................: 92.01%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> > Could someone give me an idea on how to release the 70 GB of unused space?
> >
> > Thanks a lot
> >
> > Juan
> >
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
Juan
Do you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:110
1957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables wi
th
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and
it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen
|||Karen,
1) Reinstall SQL Server and place the data files in the same directory
2)You can use the option 'Attach Database' In Sql EnterPrise manager to attach your log Files....Without restoring the backup (Backup the system after attaching)
3)Open the enterprise manager - Click on your Sql Server group... Click the server ->Right Click on the Databases folder You can get the Attach Database
4) Browse the your Data Files (.mdf) file... and then click on verify to see everything is fine
5) Then click on OK... Then you must be able to run your system as before
Also... You can delete .ldf file (as the depending up on the transactions this might take whole lot of GB from your harddisk..) and then attach your .mdf file..
Thanks
Ramesh...

Quote:

Originally posted by Mark Allison
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is f
or.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:
> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> > If a disk containing the log file is lost, but the main database files are
> > OK,
> > what do you have to do to fix it?
> > All data before the last checkpoint should be in there.
> >
>
>|||There are a number of conditions that has to be met in order for SQL server to be able to just
"create a log file". IF these are not met, attach will not work and you are in for a support call,
restore or similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:
>> Try Johns suggestion first. If that does not work have a look here:
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
>> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>> > If a disk containing the log file is lost, but the main database files are
>> > OK,
>> > what do you have to do to fix it?
>> > All data before the last checkpoint should be in there.
>> >
>>

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.
Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John
|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>
|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:

> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>
>
|||There are a number of conditions that has to be met in order for SQL server to be able to just
"create a log file". IF these are not met, attach will not work and you are in for a support call,
restore or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...[vbcol=seagreen]
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:

> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>
>|||There are a number of conditions that has to be met in order for SQL server
to be able to just
"create a log file". IF these are not met, attach will not work and you are
in for a support call,
restore or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...[vbcol=seagreen]
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:
>

How to recover a corrupted backup file?

(Posting to the right group)
I backed up a database (SQL 2000) from EM but because some disk problems the
file became corrupted. Aparently part of the problem was the transaction log
did was not truncated before (it is twice the size of the data)
Is there any tool (freebie) that allows me to recover data from the
corrupted dump file? I can open it with notepad and tested it with demo
MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
there is a lot of work in there.
Thank you for any input
The fact that the transaction log is twice the size of the data doesn't
necessary mean that it is corrupted. This is normal if you are using the
Full Recovery Mode and that you never backuped the log file. For shrinking
the log file, you must first back-it up (and not just only backup the data
file).
SQL Log Rescue from Red-Gate is a good tool to recover data from the log
file.
For the data file, I don't know.
It's not a good idea to make a backup only on the hard drive, without any
external copy.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Julio" <Julio@.discussions.microsoft.com> wrote in message
news:BD52AE00-E4F2-496A-BE41-855994DFBBA5@.microsoft.com...
> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems
> the
> file became corrupted. Aparently part of the problem was the transaction
> log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>
|||Thank you for the info.
The issue is that database became corrupted upon restore (I know, all was
done wrong), but I see data is still there on the dump file. I was looking
for a tool able to check the dump file and try to fix it (I'm working on a
copy of it) or some trick to force it to restore and repair it.
"Julio" wrote:

> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems the
> file became corrupted. Aparently part of the problem was the transaction log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>

How to recover a corrupted backup file?

I backed up a database (SQL 2000) from EM but because some disk problems the
file became corrupted. Aparently part of the problem was the transaction log
did was not truncated before (it is twice the size of the data)
Is there any tool (freebie) that allows me to recover data from the
corrupted dump file? I can open it with notepad and tested it with demo
MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
there is a lot of work in there.
Thank you for any input
Hi
Open a case with Microsoft Customer Support Services. They may be able to
help you.
http://support.microsoft.com
Regards
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julio" <Julio@.discussions.microsoft.com> wrote in message
news:063ED50F-75B6-454E-A20D-7604F1C9415C@.microsoft.com...
>I backed up a database (SQL 2000) from EM but because some disk problems
>the
> file became corrupted. Aparently part of the problem was the transaction
> log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
|||Done.
They do not recover damaged backup file. Just recover some existing
Databases with recoverable issues.
If someone has any suggestions, welcome.
Julio
"Michael Epprecht [MSFT]" wrote:

> Hi
> Open a case with Microsoft Customer Support Services. They may be able to
> help you.
> http://support.microsoft.com
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Julio" <Julio@.discussions.microsoft.com> wrote in message
> news:063ED50F-75B6-454E-A20D-7604F1C9415C@.microsoft.com...
>
>

How to recover a corrupted backup file?

(Posting to the right group)
I backed up a database (SQL 2000) from EM but because some disk problems the
file became corrupted. Aparently part of the problem was the transaction lo
g
did was not truncated before (it is twice the size of the data)
Is there any tool (freebie) that allows me to recover data from the
corrupted dump file? I can open it with notepad and tested it with demo
MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
there is a lot of work in there.
Thank you for any inputThe fact that the transaction log is twice the size of the data doesn't
necessary mean that it is corrupted. This is normal if you are using the
Full Recovery Mode and that you never backuped the log file. For shrinking
the log file, you must first back-it up (and not just only backup the data
file).
SQL Log Rescue from Red-Gate is a good tool to recover data from the log
file.
For the data file, I don't know.
It's not a good idea to make a backup only on the hard drive, without any
external copy.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Julio" <Julio@.discussions.microsoft.com> wrote in message
news:BD52AE00-E4F2-496A-BE41-855994DFBBA5@.microsoft.com...
> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems
> the
> file became corrupted. Aparently part of the problem was the transaction
> log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>|||Thank you for the info.
The issue is that database became corrupted upon restore (I know, all was
done wrong), but I see data is still there on the dump file. I was looking
for a tool able to check the dump file and try to fix it (I'm working on a
copy of it) or some trick to force it to restore and repair it.
"Julio" wrote:

> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems t
he
> file became corrupted. Aparently part of the problem was the transaction
log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>

How to recover a corrupted backup file?

(Posting to the right group)
I backed up a database (SQL 2000) from EM but because some disk problems the
file became corrupted. Aparently part of the problem was the transaction log
did was not truncated before (it is twice the size of the data)
Is there any tool (freebie) that allows me to recover data from the
corrupted dump file? I can open it with notepad and tested it with demo
MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
there is a lot of work in there.
Thank you for any inputThe fact that the transaction log is twice the size of the data doesn't
necessary mean that it is corrupted. This is normal if you are using the
Full Recovery Mode and that you never backuped the log file. For shrinking
the log file, you must first back-it up (and not just only backup the data
file).
SQL Log Rescue from Red-Gate is a good tool to recover data from the log
file.
For the data file, I don't know.
It's not a good idea to make a backup only on the hard drive, without any
external copy.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Julio" <Julio@.discussions.microsoft.com> wrote in message
news:BD52AE00-E4F2-496A-BE41-855994DFBBA5@.microsoft.com...
> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems
> the
> file became corrupted. Aparently part of the problem was the transaction
> log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>|||Thank you for the info.
The issue is that database became corrupted upon restore (I know, all was
done wrong), but I see data is still there on the dump file. I was looking
for a tool able to check the dump file and try to fix it (I'm working on a
copy of it) or some trick to force it to restore and repair it.
"Julio" wrote:
> (Posting to the right group)
> I backed up a database (SQL 2000) from EM but because some disk problems the
> file became corrupted. Aparently part of the problem was the transaction log
> did was not truncated before (it is twice the size of the data)
> Is there any tool (freebie) that allows me to recover data from the
> corrupted dump file? I can open it with notepad and tested it with demo
> MSSQLRecovery 2.2 and there is data there. I do need to recover the db as
> there is a lot of work in there.
> Thank you for any input
>