Split Backup Files while Backup and Restore SQL Server Database

Just a day before working on one of the projects, I had to take a backup of one database of 30 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 16 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 15 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one – file backup from AdventureWorks database.

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

GO

The result is displayed below. Here, the backup is taken in a single file.

Now, let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks

TO    DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

    DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

    DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

GO

In the previous example, we can clearly see that backup is split into three equal parts of the original backup file size.

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file.

RESTORE DATABASE AdventureWorks

FROM  DISK = 'C:\Backup\SingleFile\AdventureWorks.bak' 

GO

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]

FROM    DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',

        DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',

        DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak' 

GO

Make sure that while restoring database, the database is not in use, otherwise it will give an error of database in use. In the event of an error taking place, close all the connections and re-attempt to restore the database.

Related Posts

1. Backup and Restore Transaction Log in SQL Server
2. How to Verify the SQL Server Backup
3. Backup SQL Server Database using SSMS
4. Backup SQL Server database using T-SQL
5. SQL Server 2005 database DBA Checklist for database administrator

Tags: multi file backup, split sql Backup, SQL Server, SQL Server 2008 Backup, SQL Server Backup, SQL Server Database Backup, TSQL Backup