Backing Up The .Bak File in segments RRS feed

  • Question

  • My database (sql server 2008 R2) bakup file (.Bak) is of 28GB. I need to transfer the same from the production server location to a backup server location on regular basis. Following are the issue that are obvious while doing the same :

    1) Bandwidth : Such an amount of data transfer will take the bandwidth for a ride.

    2) Time : Even if the transfer is made just once in a fortnight or monthly task (which I am not very much willing), it might take up good amount of time depending upon the upload and the download speeds internet connections of both the servers.

    3) Better approach : There has to be a better approach for this ubiquitous problem.

    Isn't there any way by which I can :

    1) Breakup the backup file into segments and then copy them.

    2) Compress (not zip because it hardly makes a compression of considerable amount) the database to reduce its size not affecting the data in regard when its restored again, 

    3) Transfer it in a more faster and easier way!


    Avaneesh Bajoria.

    Wednesday, April 24, 2013 5:48 AM


All replies

  • You can split the backup file by adding more files to the backup set. SQL server will equally distribute the size between the files


    SQL Server - Split Database Full Backup to Multiple files

    Krishnakumar S

    Wednesday, April 24, 2013 6:20 AM
  • Hi there, thanks again for a reply. Well, in this case (multiple splits) how can the database be restored again ?



    Wednesday, April 24, 2013 6:52 AM
  • BACKUP DATABASE x TO DISK = 'C:\x1.bak', DISK = 'C:\x2.bak' ...

    RESTORE DATABASE x FROM DISK = 'C:\x1.bak', DISK = 'C:\x2.bak' ...

    Also, you can use the COMPRESSION option for the backup command:

    BACKUP DATABASE x TO DISK = 'C:\x1.bak', DISK = 'C:\x2.bak' WITH COMPRESSION ...

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Naomi N Friday, April 26, 2013 1:55 AM
    Wednesday, April 24, 2013 6:55 AM
  • What happens if Backup is in progress and a bulk delete command is executed (or a bulk insert for that matter) ?

    1) Will the query get executed ? if yes will it take time to get executed then when the database is not being backed up ?

    2) What data will the backup store. Will it contain the delete rows as well ?


    Avaneesh Bajoria.

    Monday, April 29, 2013 6:52 AM
  • 1.

    You can continue with other operations during backup. SQL Server documentation specify that:

    "SQL Server uses an online backup process to allow for a database backup while the database is still being used. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation."

    Backup Overview (SQL Server)

    2. What all things get backed up? Paul Randal has an excellent article on this.


    an excerpt is below:

    "In terms of what gets backed up, the way a full backup works is:

    1. Note the transaction log’s LSN (Log Sequence Number)
    2. Read all allocated extents in the various data files
    3. Note the LSN again
    4. Read all the transaction log between the starting LSN and the ending LSN

    Any transaction that commits before or on the LSN read in step 3 will be fully reflected when the database is restored. If not, the transaction will be undone."

    He also has a script to test this. His blog has many excellent articles on backup and restore as well.


    Hope this is helpful.

    Krishnakumar S

    Monday, April 29, 2013 9:49 AM
  • What exactly are you trying to do with the backup?  If you are trying to do an HA solution, instead of restoring it to a backup server, use transactional replication or always on (database mirroring) or log shipping.

    Monday, April 29, 2013 3:07 PM