Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Backup to Multiple Directories?

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:01:48
Has anyone ever had a need to backup to multiple directories? I am building a general purpose stored procedure to do various types of backups, and I was wondering if the need to do this is common enough to make it worth the effort.

Example of backup to multiple directories:
backup database [MyBigDatabase]
to
	disk	= 'D:\BACKUP\MyBigDatabase_db_200709131454_001.BAK' ,
	disk	= 'E:\BACKUP\MyBigDatabase_db_200709131454_002.BAK' ,
	disk	= 'F:\BACKUP\MyBigDatabase_db_200709131454_003.BAK' ,
	disk	= 'G:\BACKUP\MyBigDatabase_db_200709131454_004.BAK' ,
	disk	= 'H:\BACKUP\MyBigDatabase_db_200709131454_005.BAK'
with
	init,
	stats = 5




CODO ERGO SUM

X002548
Not Just a Number

15586 Posts
Posted - 2007-09-13 : 15:04:16
How would it know how to distribute the data?

Are they physically attached drives?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2007-09-13 : 15:06:01
does it creates copies or divides the backup file?
have't tried this... if it replicates, is it possible to specify mixed device and disk?

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-13 : 15:13:28
I assume what you are describing is a split-backup? rather than redundant copy to multiple locations?

I have a need to Backup to file(s), optionally ZIP them, and then Copy them to some alternative location.

Needs to be robust - so system needs to be copy files that failed to copy at an earlier backup.

perhaps:

1) Backup to x:\MyFolder
2) Move all files in x:\ToCopyFolder, which exist in y:\OtherFolder, to x:\MyDoneFolder
3) Move all files in x:\MyFolder to x:\ToCopyFolder
4) Copy all files in x:\ToCopyFolder to y:\OtherFolder

The MOVE is designed to fail for any files that are "open" - e.g. still being written to.

Having the ability at step-3 to CRC the files to know that they did actually copy OK would be good. Failing that a DIR should reveal that they are the same size, otherwise copy again please!

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2007-09-13 : 15:19:08
I know I'm not yet drunk but had a bout of dizziness there Kristen

--------------------
keeping it simple...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:23:44
I am not talking about a redundant backup to multiple files, but a split backup spread across multiple files

SQL Server can to this type of backup OK. I don't know how it goes about distributing the data, but it does do it OK, and restores from multiple files work fine. Basically, if you specify 5 files, you end up with 5 files of equal size, but one fifth the size of a single backup.

I am actually doing backups to multiple files in a production system, but the files are all in the same directory. The reason I am doing that is because the backups are going to a compressed directory. Backing up to compressed files slows a backup way down due to the compression. I discovered that backing up to multiple compressed files is much faster, probably because you have multiple threads writing to disk.

I'm not really asking if it is possible. I just want to know if the need to backup to multiple directories is common enough to be worth the effort to program it into my procedure.





CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-13 : 15:24:38
> I know I'm not yet drunk...

never thought i'd read this from you, Jen

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-13 : 15:26:13
> I'm not really asking if it is possible. I just want to know if the need to backup to multiple directories is common enough to
> be worth the effort to program it into my procedure.

i'd say not really.

but the coolness factor for this is way high

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2007-09-13 : 15:27:23
I think there is a need MVJ. Like what you said, writes and file size

Is the restore consistent as well? Does it divide the files equally?

--------------------
keeping it simple...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:28:35
quote:
Originally posted by Kristen

I assume what you are describing is a split-backup? rather than redundant copy to multiple locations?

I have a need to Backup to file(s), optionally ZIP them, and then Copy them to some alternative location.

Needs to be robust - so system needs to be copy files that failed to copy at an earlier backup.

perhaps:

1) Backup to x:\MyFolder
2) Move all files in x:\ToCopyFolder, which exist in y:\OtherFolder, to x:\MyDoneFolder
3) Move all files in x:\MyFolder to x:\ToCopyFolder
4) Copy all files in x:\ToCopyFolder to y:\OtherFolder

The MOVE is designed to fail for any files that are "open" - e.g. still being written to.

Having the ability at step-3 to CRC the files to know that they did actually copy OK would be good. Failing that a DIR should reveal that they are the same size, otherwise copy again please!

Kristen


Are you using ROBOCOPY to do the file copies? It has a lot of features that make what you describe easy, including automatic retries and restartable copies.



CODO ERGO SUM
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2007-09-13 : 15:30:55
I am but it still fails... maybe more test is needed

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-13 : 15:31:00
"I discovered that backing up to multiple compressed files is much faster"

Well I never! We backup to Compressed Folders too, so I'll look into that, thanks.

Is 5-threads the optimum, in your findings?

"Is the restore consistent as well? Does it divide the files equally?"

I HAVE been drinking, and I an DEFINITELY feeling dizzy at that!

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2007-09-13 : 15:33:24
LOL... just wondering at the quality of restore
although I will definitely try it out when I go back to work

--------------------
keeping it simple...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:33:56
quote:
Originally posted by spirit1

> I'm not really asking if it is possible. I just want to know if the need to backup to multiple directories is common enough to
> be worth the effort to program it into my procedure.

i'd say not really.

but the coolness factor for this is way high

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp


The "coolness factor" may be very high, but so is the effort. The real effort is not in the backup itself, but the cleanup of old files.

I already have a proc that does backups to multiple files, just not to multiple directories. I'm just wondering if I'll regret not adding this feature.






CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-09-13 : 15:44:58
quote:
Originally posted by spirit1

> I know I'm not yet drunk...

never thought i'd read this from you, Jen

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp


come on

She's a filapina

I know something about this



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:47:18
quote:
Originally posted by Kristen

"I discovered that backing up to multiple compressed files is much faster"

Well I never! We backup to Compressed Folders too, so I'll look into that, thanks.

Is 5-threads the optimum, in your findings?

"Is the restore consistent as well? Does it divide the files equally?"

I HAVE been drinking, and I an DEFINITELY feeling dizzy at that!

Kristen


I didn't do a lot of testing on optimum number of files to backup to. The one I am doing now is backing up to 10 files. Basically, it takes about the same amount of time for the backup as a backup to an uncompressed directory.

I also tried backups to multiple files in an uncompressed directory, and that didn't seem to make any difference in run-time. I think once you get to a certain speed, you are limited more by how fast you can read the source database files than by how fast you can write the backup files to disk.

I haven't tested backups to multiple physical disks, but that may have potential for increased performance.







CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-13 : 15:47:34
do share brett! please!!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-13 : 15:49:44
quote:
Originally posted by Michael Valentine Jones

The "coolness factor" may be very high, but so is the effort. The real effort is not in the backup itself, but the cleanup of old files.

I already have a proc that does backups to multiple files, just not to multiple directories. I'm just wondering if I'll regret not adding this feature.



CODO ERGO SUM


coolness factor is usually equal to amount of work.

and if you have to ask that question then you probably will regret not adding it later.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 15:54:27
Getting back to my original question, it sounds like no one who has responded so far has ever done this.

Of course, that could be because they didn’t know they could, or it was just too much work to setup.


CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-13 : 16:10:21
"I'm just wondering if I'll regret not adding this feature"

Well, if you have multiple drives/spindles available for backup then that will definitely be faster, so well worth building in I reckon. I'll bet if you don;t add it management will propose backup up to multiple spindles the day after

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-09-13 : 16:43:14
quote:
Originally posted by Kristen

"I'm just wondering if I'll regret not adding this feature"

Well, if you have multiple drives/spindles available for backup then that will definitely be faster, so well worth building in I reckon. I'll bet if you don;t add it management will propose backup up to multiple spindles the day after

Kristen


I'm hoping by the time I need it, Tara will have a proc that does that posted on her blog.



CODO ERGO SUM
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site