You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@subversion.apache.org by Ruslan Sivak <rs...@istandfor.com> on 2006/04/27 16:48:24 UTC
subversion to back up SQL server
I'm thinking of using subversion to back up sql server. Since SQL server
full backups should differ little from backup to backup, I was hoping that
subversion can store the full backups as deltas.
In practice, however, this doesn't seem to be happening. I just did 2 full
backups about 10 mins apart. The resulting backup file was 1.5gb. I
checked in the first file, and 10 mins later overwrote it and checked in the
second file. The revs are approximately 1.2gb each. That's 1.2gb for the
first import and 1.2gb for the change.
Does subversion not work well with files larger then a certain limit?
Should I be using some other utility instead?
Russ
Re: subversion to back up SQL server
Posted by Ryan Schmidt <su...@ryandesign.com>.
On Apr 27, 2006, at 18:48, Ruslan Sivak wrote:
> I’m thinking of using subversion to back up sql server… Since SQL
> server full backups should differ little from backup to backup, I
> was hoping that subversion can store the full backups as deltas.
>
> In practice, however, this doesn’t seem to be happening. I just
> did 2 full backups about 10 mins apart. The resulting backup file
> was 1.5gb. I checked in the first file, and 10 mins later
> overwrote it and checked in the second file. The revs are
> approximately 1.2gb each. That’s 1.2gb for the first import and
> 1.2gb for the change.
>
> Does subversion not work well with files larger then a certain
> limit? Should I be using some other utility instead?
I don't know "SQL server". I use MySQL. I know that its database
files are binary, but that you can create a text dump of the
databases' contents. Are you trying to check text dumps into
Subversion or the original binaries? I would expect the latter not to
work very well in terms of disk space usage, and the former to work
very well. To make it easiest on you to see differences and be able
to restore them, I'd recommend dumping each database to a separate
file, or even each table.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by Scott Lamb <sl...@slamb.org>.
On Apr 27, 2006, at 9:48 AM, Ruslan Sivak wrote:
> I’m thinking of using subversion to back up sql server… Since SQL
> server full backups should differ little from backup to backup, I
> was hoping that subversion can store the full backups as deltas.
Even if you succeed in getting the per-revision disk usage down to a
reasonable level, you'll eventually find that your backups have
become huge. There's no easy way to get rid of old revisions in
Subversion. It's just not designed for this type of use.
> Should I be using some other utility instead?
rdiff-backup is designed to make reliable backups and keep deltas,
then eventually get rid of them. It's a better fit for what you want
to do, and I highly recommend it.
You likely will encounter this same problem, though - compression
makes deltas ineffective, so you'll want to find a way to turn off
whatever compression MS SQL Server is using.
--
Scott Lamb <http://www.slamb.org/>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by "David M. Jones" <dm...@ams.org>.
> From: "Ruslan Sivak" <rs...@istandfor.com>
> Cc: <us...@subversion.tigris.org>
> Date: Thu, 27 Apr 2006 13:21:06 -0400
> Thread-Index: AcZqHokyylnkAQypQFOllrzhtAxf4AAAD5bA
>
> The whole point is that I would like to keep a history of possibly older
> then 2 weeks, and not use a lot of space to do it.
>
> I understand that if you reorg or something, I might end up with a complete
> db backup again. But I did the backup a few mins apart... there shouldn't
> have been that many changes... Why did it backup the complete file again?
When a new version of a file is committed, Subversion doesn't always
store it as a delta against the most recent version of a file. It
uses a more complicated scheme of "skip-deltas" to minimize the number
of deltas that have to be applied to to recreate a given version of
the file. This is described in the file notes/skip-deltas in the
Subversion source distribution. My guess is that this is what you've
encountered.
Cheers,
David.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by Kalin KOZHUHAROV <ka...@thinrope.net>.
Rupert Martin wrote:
> I'm presently not aware of any way of turning off compression of backups,
> nor of an off-the-shelf DTS package that can backup an antire database to
> text files. Though I'm sure one can fairly easily be written.
OK, I *had* to start reading about SS2005 and other M$ technologies a few
days ago, so I am a n00b here, this was just a random idea ;-)
> Instead of trying to diff a SQL backup, what happens if you detach the
> database and commit that? Whilst the backup may differ each time, surely
> the MDF itself won't change that much? I understand that this is only
> feasible if you have a time window where you can take the database offline,
> so it's not ideal to live use, but for development purposes it should be
> acceptable.
That is a cool idea, you can try it for sure. Even if the DB in question is
mission critical, make a copy (replication) and set that off-line while doing
backup.
> PS. This is my first post the users group - have I done it properly?
Well, for a first post it is good, I've seen times worse cases :-)
> PPS. What's a top-post?
See http://en.wikipedia.org/wiki/Toppost
Kalin.
--
|[ ~~~~~~~~~~~~~~~~~~~~~~ ]|
+-> http://ThinRope.net/ <-+
|[ ______________________ ]|
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
RE: subversion to back up SQL server
Posted by Rupert Martin <ru...@ntlworld.com>.
I'm presently not aware of any way of turning off compression of backups,
nor of an off-the-shelf DTS package that can backup an antire database to
text files. Though I'm sure one can fairly easily be written.
Instead of trying to diff a SQL backup, what happens if you detach the
database and commit that? Whilst the backup may differ each time, surely
the MDF itself won't change that much? I understand that this is only
feasible if you have a time window where you can take the database offline,
so it's not ideal to live use, but for development purposes it should be
acceptable.
Rupert.
PS. This is my first post the users group - have I done it properly?
PPS. What's a top-post?
-----Original Message-----
From: Kalin KOZHUHAROV [mailto:kalin@thinrope.net]
Sent: 28 April 2006 15:27
To: Ruslan Sivak
Cc: 'Andy Levy'; users@subversion.tigris.org
Subject: Re: subversion to back up SQL server
Please do *NOT* top-post.
[failed attempt to fix top posted content, deleting]
Ruslan Sivak wrote:
> The whole point is that I would like to keep a history of possibly
> older then 2 weeks, and not use a lot of space to do it.
Doesn't SQL Server have incremental backup? Something like a state of the
database at a certain point (snapshot?) + transaction log till present.
> I understand that if you reorg or something, I might end up with a
> complete db backup again. But I did the backup a few mins apart...
> there shouldn't have been that many changes... Why did it backup the
> complete file again?
I bet this backup file is compressed with something. Depending on the
algorithm in use, a single bit change may have impact on a very large block.
Try it with a text file (a.txt) and a compressed (a.txt.bz2) copy.
Copy a.txt to b.txt
Edit b.txt, changing a few characters
Compress b.txt into b.txt.bz2
Compare the changes between a.txt and b.txt Do the same with a.txt.bz2 and
b.txt.bz2
Can you turn compression off when backing up SS2005?
Any DTS service that can fully backup a SS2005 database in text files?
Hmm, any binary diff tool out there? Something like hexeditor but with diff?
Or I need to write one that hexdumps the files and compare them with normal
diff?
Kalin.
--
|[ ~~~~~~~~~~~~~~~~~~~~~~ ]|
+-> http://ThinRope.net/ <-+
|[ ______________________ ]|
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/326 - Release Date: 27/04/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/326 - Release Date: 27/04/2006
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by Kalin KOZHUHAROV <ka...@thinrope.net>.
Please do *NOT* top-post.
[failed attempt to fix top posted content, deleting]
Ruslan Sivak wrote:
> The whole point is that I would like to keep a history of possibly
> older then 2 weeks, and not use a lot of space to do it.
Doesn't SQL Server have incremental backup? Something like a state of
the database at a certain point (snapshot?) + transaction log till
present.
> I understand that if you reorg or something, I might end up with a
> complete db backup again. But I did the backup a few mins apart...
> there shouldn't have been that many changes... Why did it backup the
> complete file again?
I bet this backup file is compressed with something. Depending on the
algorithm in use, a single bit change may have impact on a very large
block.
Try it with a text file (a.txt) and a compressed (a.txt.bz2) copy.
Copy a.txt to b.txt
Edit b.txt, changing a few characters
Compress b.txt into b.txt.bz2
Compare the changes between a.txt and b.txt
Do the same with a.txt.bz2 and b.txt.bz2
Can you turn compression off when backing up SS2005?
Any DTS service that can fully backup a SS2005 database in text files?
Hmm, any binary diff tool out there? Something like hexeditor but with
diff?
Or I need to write one that hexdumps the files and compare them
with normal diff?
Kalin.
--
|[ ~~~~~~~~~~~~~~~~~~~~~~ ]|
+-> http://ThinRope.net/ <-+
|[ ______________________ ]|
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
RE: subversion to back up SQL server
Posted by Ruslan Sivak <rs...@istandfor.com>.
The whole point is that I would like to keep a history of possibly older
then 2 weeks, and not use a lot of space to do it.
I understand that if you reorg or something, I might end up with a complete
db backup again. But I did the backup a few mins apart... there shouldn't
have been that many changes... Why did it backup the complete file again?
-----Original Message-----
From: Andy Levy [mailto:andy.levy@gmail.com]
Sent: Thursday, April 27, 2006 1:12 PM
To: Ruslan Sivak
Cc: users@subversion.tigris.org
Subject: Re: subversion to back up SQL server
On 4/27/06, Ruslan Sivak <rs...@istandfor.com> wrote:
> I might research the sql server built in facilities a little more, but the
> reason I want to use subversion is so that I can have an on-disk backup
with
> history. I would like to be able to pull up a backup from 2 weeks ago,
and
> not have to wait for tape to arrive...
You can do that with the built-in backup. Might need an extra script
to blast any backup older than 2 weeks, but that's not hard to write.
> With proper diffing, subversion should only be storing the changes in the
> file, as opposed to the whole file, so in the end, the repository should
> remain small.
Unless you do a reorg, make a table definition change, re-index the
table, shrink the database, etc.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by Andy Levy <an...@gmail.com>.
On 4/27/06, Ruslan Sivak <rs...@istandfor.com> wrote:
> I might research the sql server built in facilities a little more, but the
> reason I want to use subversion is so that I can have an on-disk backup with
> history. I would like to be able to pull up a backup from 2 weeks ago, and
> not have to wait for tape to arrive...
You can do that with the built-in backup. Might need an extra script
to blast any backup older than 2 weeks, but that's not hard to write.
> With proper diffing, subversion should only be storing the changes in the
> file, as opposed to the whole file, so in the end, the repository should
> remain small.
Unless you do a reorg, make a table definition change, re-index the
table, shrink the database, etc.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
RE: subversion to back up SQL server
Posted by Ruslan Sivak <rs...@istandfor.com>.
I might research the sql server built in facilities a little more, but the
reason I want to use subversion is so that I can have an on-disk backup with
history. I would like to be able to pull up a backup from 2 weeks ago, and
not have to wait for tape to arrive...
With proper diffing, subversion should only be storing the changes in the
file, as opposed to the whole file, so in the end, the repository should
remain small.
Russ
-----Original Message-----
From: Andy Levy [mailto:andy.levy@gmail.com]
Sent: Thursday, April 27, 2006 1:02 PM
To: Ruslan Sivak
Cc: users@subversion.tigris.org
Subject: Re: subversion to back up SQL server
On 4/27/06, Ruslan Sivak <rs...@istandfor.com> wrote:
>
> I'm thinking of using subversion to back up sql server. Since SQL server
> full backups should differ little from backup to backup, I was hoping that
> subversion can store the full backups as deltas.
>
> In practice, however, this doesn't seem to be happening. I just did 2
full
> backups about 10 mins apart. The resulting backup file was 1.5gb. I
> checked in the first file, and 10 mins later overwrote it and checked in
the
> second file. The revs are approximately 1.2gb each. That's 1.2gb for the
> first import and 1.2gb for the change.
>
> Does subversion not work well with files larger then a certain limit?
> Should I be using some other utility instead?
SVN does do binary diffing. Not knowing the internal format of SQL
Server files, they may not lend themselves well to doing this or
compression, however.
This really doesn't sound like an appropriate use of SVN in the first
place, though. Unless your backup strategy needs to be able to see
what changed from snapshot to snapshot, you'd be much better off using
the SQL Server built-in backup facilities in conjunction with a
"regular" backup package writing to tape.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org
Re: subversion to back up SQL server
Posted by Andy Levy <an...@gmail.com>.
On 4/27/06, Ruslan Sivak <rs...@istandfor.com> wrote:
>
> I'm thinking of using subversion to back up sql server… Since SQL server
> full backups should differ little from backup to backup, I was hoping that
> subversion can store the full backups as deltas.
>
> In practice, however, this doesn't seem to be happening. I just did 2 full
> backups about 10 mins apart. The resulting backup file was 1.5gb. I
> checked in the first file, and 10 mins later overwrote it and checked in the
> second file. The revs are approximately 1.2gb each. That's 1.2gb for the
> first import and 1.2gb for the change.
>
> Does subversion not work well with files larger then a certain limit?
> Should I be using some other utility instead?
SVN does do binary diffing. Not knowing the internal format of SQL
Server files, they may not lend themselves well to doing this or
compression, however.
This really doesn't sound like an appropriate use of SVN in the first
place, though. Unless your backup strategy needs to be able to see
what changed from snapshot to snapshot, you'd be much better off using
the SQL Server built-in backup facilities in conjunction with a
"regular" backup package writing to tape.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
For additional commands, e-mail: users-help@subversion.tigris.org