Database Backups & Compression

Hi All

I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.

My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.

Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.

Any suggestions? Thanks in advance guys n gals :-)Log shipping IS a full copy, in the truest sense of the word. It is not a monolithic file, but that is a feature instead of a problem in my opinion.

-PatP|||okay for a moment let;
machine A = live, machine B = warm standby server on a remote site

I believed that when restoring log shipments to machine B, there are inherent problems, due to the nighly backups that take place on machine A.

When the log shipping from machine A continues after it has performed a nightly backup, the transcation log entries that were processed and removed during the backup will not be included in the next log shipment to machine B, thus losing a porion of transactions during that given period. Therefore, to successfully restore on machine B, the monolithic database backup file from machine A would be required initially to restore the database and then applying any log shipments that have been shipped after that night's backup.

Is that incorrect...anybody?

The problem is getting that monolithic file to copy accross the network in a given timeframe - it's too big to achieve but 'they' are insisting that it is done.|||Is that incorrect...anybody?


Bzzzzzzzz ... thank you for playing ... you will get your consolation prize as you head backstage :)

When databases are in full recovery mode, the backup does not mark the transaction log for re-use. That only occurs after the transaction log backup.

Prove it to yourself and the PTB (powers that be) by setting up log shipping with a copy of Northwind on the target box ... that way you aren't fighting the database size issue. GO thru the full backup, and tran backup cycles, then make database mods and see if any are lost (hint: they won't be).

I have, in the past, restored a backup from three months prior and then brought it current by applying log backups from that point forward, even across weekly full backups (storage team issues ... grrrrrrrrrrr!).

And to make the uneducated happy, you could even slowly copy the full backup across the wire to the failover machine weekly.|||The database backup will not truncate the log, so the subsequent log backups will contain all the log entries.

I have not done this with MS SQL backups yet but
To speedup the transfer of your 72 GB database backup;
Consider using rsync (http://www.google.com/search?num=100&hl=en&q=rsync&meta=)
Also consider using an rsyncable gzip (http://www.google.com/search?num=100&hl=en&q=rsyncable+gzip&meta=) to compress the file
At minimum compression it should reduce it to 14 GB at acceptable speed (assuming you don't have images inside your database)
And will allow rsync to only copy the portion inside the gzip file that changed.
You would probably see that only 1.4 GB of the 14 GB is actually transferred across the line (10 times faster).

PS. I can understand why they want full backups. You only need a problem with one log backup (missing or damaged file) and you won't be able to recover past that point. A full nightly backup and 15 min log backups make sense to me.

Database "Suspect"

For whatever reason, I have a database marked as "Suspect". I didn't find
any clue from the error log. The drive has plenty of free spaces. I don't
have the backup files. Any suggestion to recover?
Thanks a lot,
LX
Hi
Tibor Karaszi has very good article about this issue.
http://www.karaszi.com/sqlserver/default.asp
"FLX" <nospam@.hotmail.com> wrote in message
news:Olmb3%23pjEHA.3876@.TK2MSFTNGP15.phx.gbl...
> For whatever reason, I have a database marked as "Suspect". I didn't find
> any clue from the error log. The drive has plenty of free spaces. I don't
> have the backup files. Any suggestion to recover?
> Thanks a lot,
> LX
>
>
>
>

Database "Suspect"

For whatever reason, I have a database marked as "Suspect". I didn't find
any clue from the error log. The drive has plenty of free spaces. I don't
have the backup files. Any suggestion to recover?
Thanks a lot,
LXHi
Tibor Karaszi has very good article about this issue.
http://www.karaszi.com/sqlserver/default.asp
"FLX" <nospam@.hotmail.com> wrote in message
news:Olmb3%23pjEHA.3876@.TK2MSFTNGP15.phx.gbl...
> For whatever reason, I have a database marked as "Suspect". I didn't find
> any clue from the error log. The drive has plenty of free spaces. I don't
> have the backup files. Any suggestion to recover?
> Thanks a lot,
> LX
>
>
>
>|||Try dettach + attaching the database...
>--Original Message--
>For whatever reason, I have a database marked
as "Suspect". I didn't find
>any clue from the error log. The drive has plenty of
free spaces. I don't
>have the backup files. Any suggestion to recover?
>Thanks a lot,
>LX
>
>
>
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0023_01C48E73.F0CAC3E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detaching a suspect database can lead to big problems, especially since =you cannot attach a corrupt database. If the database is suspect =because of recovery failures, and you try to attach it again, the attach =command will fail.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"..." <anonymous@.discussions.microsoft.com> wrote in message =news:08f701c48ea2$fbcb5730$a401280a@.phx.gbl...
Try dettach + attaching the database...
>--Original Message--
>For whatever reason, I have a database marked as "Suspect". I didn't find
>any clue from the error log. The drive has plenty of free spaces. I don't
>have the backup files. Any suggestion to recover?
>
>Thanks a lot,
>LX
>
>
>
>
>
>
>
>
>.
>
--=_NextPart_000_0023_01C48E73.F0CAC3E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Detaching a suspect database can lead to big =problems, especially since you cannot attach a corrupt database. If the =database is suspect because of recovery failures, and you try to attach it again, =the attach command will fail.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"..." wrote in message news:08f701c48ea2$fb=cb5730$a401280a@.phx.gbl...Try dettach + attaching the database...>--Original Message-->For whatever reason, I have a database marked =as "Suspect". I didn't find>any clue from the error log. The drive =has plenty of free spaces. I don't>have the backup files. =Any suggestion to recover?>>Thanks a =lot,>LX>>>>>>>>>.>

---

--=_NextPart_000_0023_01C48E73.F0CAC3E0--|||This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C48E96.A5005EC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Exactly! That is my problem now. Here is error message:
I/O error (torn page) detected during read at offset 0x0000002979e000 in =file "c:\XXXXXX"
What should I do to fix it?
Thanks in advance,
LX
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in =message news:eUfSe6qjEHA.704@.TK2MSFTNGP12.phx.gbl...
Detaching a suspect database can lead to big problems, especially =since you cannot attach a corrupt database. If the database is suspect =because of recovery failures, and you try to attach it again, the attach =command will fail.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"..." <anonymous@.discussions.microsoft.com> wrote in message =news:08f701c48ea2$fbcb5730$a401280a@.phx.gbl...
Try dettach + attaching the database...
>--Original Message--
>For whatever reason, I have a database marked as "Suspect". I didn't find
>any clue from the error log. The drive has plenty of free spaces. I don't
>have the backup files. Any suggestion to recover?
>
>Thanks a lot,
>LX
>
>
>
>
>
>
>
>
>.
>
--=_NextPart_000_000A_01C48E96.A5005EC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Exactly! That is my problem now. Here =is error message:
I/O error (torn page) detected during =read at offset 0x0000002979e000 in file "c:\XXXXXX"
What should I do to fix =it?
Thanks in advance,
LX
"Ryan Stonecipher [MSFT]" wrote in message news:eUfSe6qjEHA.704@.T=K2MSFTNGP12.phx.gbl...
Detaching a suspect database can lead to big =problems, especially since you cannot attach a corrupt database. If the =database is suspect because of recovery failures, and you try to attach it =again, the attach command will fail.

Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"..." wrote in message news:08f701c48ea2$fb=cb5730$a401280a@.phx.gbl...Try dettach + attaching the database...>--Original Message-->For whatever reason, I have a database marked =as "Suspect". I didn't find>any clue from the error log. The =drive has plenty of free spaces. I don't>have the backup =files. Any suggestion to recover?>>Thanks a =lot,>LX>>>>>>>>>.>

---

--=_NextPart_000_000A_01C48E96.A5005EC0--|||As I remember, the repair will remove the torn page, and all that depends on it. I.e., you never
know how much data you will lose. I strongly encourage you to either restore from a healthy backup,
or open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FLX" <nospam@.hotmail.com> wrote in message news:%23rI67AsjEHA.556@.tk2msftngp13.phx.gbl...
Exactly! That is my problem now. Here is error message:
I/O error (torn page) detected during read at offset 0x0000002979e000 in file "c:\XXXXXX"
What should I do to fix it?
Thanks in advance,
LX
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in message
news:eUfSe6qjEHA.704@.TK2MSFTNGP12.phx.gbl...
Detaching a suspect database can lead to big problems, especially since you cannot attach a
corrupt database. If the database is suspect because of recovery failures, and you try to attach it
again, the attach command will fail.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"..." <anonymous@.discussions.microsoft.com> wrote in message
news:08f701c48ea2$fbcb5730$a401280a@.phx.gbl...
Try dettach + attaching the database...
>--Original Message--
>For whatever reason, I have a database marked
as "Suspect". I didn't find
>any clue from the error log. The drive has plenty of
free spaces. I don't
>have the backup files. Any suggestion to recover?
>
>Thanks a lot,
>LX
>
>
>
>
>
>
>
>
>.
>

Database "Suspect"

For whatever reason, I have a database marked as "Suspect". I didn't find
any clue from the error log. The drive has plenty of free spaces. I don't
have the backup files. Any suggestion to recover?
Thanks a lot,
LXHi
Tibor Karaszi has very good article about this issue.
http://www.karaszi.com/sqlserver/default.asp
"FLX" <nospam@.hotmail.com> wrote in message
news:Olmb3%23pjEHA.3876@.TK2MSFTNGP15.phx.gbl...
> For whatever reason, I have a database marked as "Suspect". I didn't find
> any clue from the error log. The drive has plenty of free spaces. I don't
> have the backup files. Any suggestion to recover?
> Thanks a lot,
> LX
>
>
>
>