CODEWITHSUNDEEP
.netsqlbackuprestore
Ankit Prajapati
Ankit Prajapati

Reputation: 85

How do I split large SQL database backup files in .net?

I am working on backing up and restoration of SQL Database. I am new to this.I have an issue related to Backup process. I have SQL database and I am using BACKUP and RESTORE classes to perform backup and restore of database.

I want to make my program more efficient using split backup. I have aprx 15 to 20 GB of data. so I want to split my my backup files in specified limit lets say 8-10 GB. I can do this using below SQL statements: 

BACKUP DATABASE AdventureWorks
TO  DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
    DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
    DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

But I want to do this by using Microsoft.SqlServer.Management.Smo.Backup classes. And I want to get the size of my backup files. Because my criteria is to split file if my database size exceeds 10 GB otherwise it will not split my file. SO my issue is how can I get size of my database when taking backup.

Upvotes: 0

Views: 2265

Answers (2)

Scott Chamberlain
Scott Chamberlain

Reputation: 127553

If your original query works like you said it does (I don't know off the top of my head if it works that way or not) you just need to add the additional devices to the Backup object

This should be the equivalent SMO code

var server = new Server(/*...*/);
var backup = new Backup();

backup.Action = BackupActionType.Database;
backup.Database = "AdventureWorks";

backup.Devices.AddDevice(@"C:\Backup\MultiFile\AdventureWorks1.bak",DeviceType.File);
backup.Devices.AddDevice(@"C:\Backup\MultiFile\AdventureWorks2.bak",DeviceType.File);
backup.Devices.AddDevice(@"C:\Backup\MultiFile\AdventureWorks3.bak",DeviceType.File);

backup.SqlBackup(server);

Upvotes: 1

satnhak
satnhak

Reputation: 9861

Have you tried setting the BlockSize on the Backup object?

var server = new Server();
var backup = new Backup { BlockSize = 1073741824 };

// other settings

backup.SqlBackup(server);

Upvotes: 0

Related Questions