Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am taking daily backups of a SQL Server database. Right now the .bak file is about 2GB and it is growing day by day. There is a scheduled job is running which moves this .bak file from one location to another.

Is there a way to save the .bak file in chunks - like part1.bak, part2.bak etc..

so it will be much easier to move small data and merge at destination?

share|improve this question

migrated from stackoverflow.com May 15 '12 at 12:30

This question came from our site for professional and enthusiast programmers.

1  
Alternative: if you're using SQL Server 2008+ Enterprise Edition, look into backup compression. Otherwise, if you "just" want smaller dumps for transferring them from location A to B, and otherwise have enough disk space, just manually ZIP them. There is usually quite some space to gain here. –  Christian.K May 15 '12 at 5:28
    
Actually I am trying to automate process, and not go through manual process. Because I know that this .bak file will be about 50+ GB in near feature and moving from source to destination will be big hassle. Would it be nice idea to write C# application which will go and .zip this .bak file and split them into pieces? like 10MB per part. maybe. –  Teoman shipahi May 15 '12 at 5:33
    
My "alternative" could easily be automated as well; "manually ZIP" didn't mean that you do that manually every time, but rather that you use a tool like 7za.exe, possibly scripted, instead of a builtin mechanism of SQL Server. I'm not aware of a built-in way to write a backup in parts. And the BACKUP T-SQL command doesn't seem to have an option. You could try backing up individual filegroups, but I'm not sure if there are any caveats to that. –  Christian.K May 15 '12 at 5:37
    
Also, you could use a tool like the split to "manually" split a larger file into chunks of smaller sizes. While it is UNIX utility it will surely be available in one or more UNIX-Tools collections for windows (like Cygwin), or there may even by a true Windows port or a comparable tool. –  Christian.K May 15 '12 at 5:40

2 Answers 2

up vote 7 down vote accepted

Normal backup

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

to split the size into 3 use the following code, it will split the size of 3 gb into 1 GB each file you can add more files or less depending on the size you want

SET STATISTICS IO ON

SET STATISTICS TIME ON


BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'


SET STATISTICS IO OFF

SET STATISTICS TIME OFF

for more details

http://beyondrelational.com/modules/2/blogs/88/posts/10153/sql-server-split-database-full-backup-to-multiple-files.aspx

share|improve this answer

May be you'd like to use differential backup. It backups only data changed from the last backup, so you'll copy only the small amount of data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.