Search code examples
sqlsql-serverdatabasedatabase-backups

How to backup Microsoft SQL Server database to create multiple 100MB .bak files

One .bak file:

BACKUP DATABASE dbName TO DISK = C:\dbname.bak   1200 MB

How to backup Microsoft SQL Server database into multiple .bak files, splinting it to multiple files like DBName01.bak,DBName02.bak,....'DBName0N.bak there N is parameter for number of files.

Where N would be dynamic parameter.

Solution

  • Yes you can take your backups to multiple files, its actually more convenient to have smaller files rather than having one large file.

    The syntax for taking backups to multiple files would be:

    BACKUP DATABASE [dbName] TO  
  DISK = N'D:\backups\MultipleFiles\MyDB_Backup_File1.bak',  
  DISK = N'D:\backups\MultipleFiles\MyDB_Backup_File2.bak',  
  DISK = N'D:\backups\MultipleFiles\MyDB_Backup_File3.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'dbName-Full Database Backup'
       , SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    The data is distributed equally among all the files, so if you want smaller files use more files to take backups.