6

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?

Improve this question
4
  • 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
    Commented May 15, 2012 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
    Commented May 15, 2012 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
    Commented May 15, 2012 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
    Commented May 15, 2012 at 5:40

2 Answers 2

Reset to default
9

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

Improve this answer
1
  • so easy and works like a charm, even so many years later! thanks for this gem
    – bkwdesign
    Commented Feb 7, 2020 at 20:55
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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