none
Backup Database

    Question

  • There are database files mdf, ndf1, ndf2 on one database, on the other hand there are three drives as backup destination for each database files.

    We want to execute database backup parallel, by executing the following command at the same time,

    backup database <DB> FILE = <mdf>, FILE = <ndf1> to disk = Z:\backup ......

    backup database <DB> FILE = <ndf2>, FILE = <ndf3> to disk = Y:\backup . 

    but the backup was executed one by oine.

    Please any one help?

    regards,

    


    Sunday, April 13, 2014 11:58 AM

Answers

  • Hello,

    According to the BOL:

    Under the full recovery model, you must back up the transaction log, regardless of the rest of your backup strategy. A complete set of full file backups, together with enough log backups to span all the file backups from the start of the first file backup, is the equivalent of a full database backup

    Only one file backup operation can occur at a time. You can back up multiple files in one operation

    As per my understand, you can have two full file backup running at a time, but you can't have two simultaneous log backups. In your case, the file backups will run serial.

    Reference:Concurrent Administrative Operations

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support


    Monday, April 14, 2014 9:45 AM
  • To get a bit more specific, with SQL Server backups, if you have a database <mydb> with the files:

    Data.mdf

    data2.ndf

    data3.ndf

    Mylog.ldf

    then you can issue a single backup command:

    BACKUP DATABASE mydb. TO DISK='X:\backups\mydb1.bak', DISK=Y:\backups\mydb2.bak', DISK=Z:\backups\mydb3.bak'

    As Erland said,

    SQL will read all of the data files in parallel, and write to all three backup files in parallel  The backup files will not have the data segregated, but this is the way to get the files all backed up in parallel.


    Microsoft SQL Server Storage Engine PM

    Tuesday, April 15, 2014 12:09 AM

All replies

  • You can use  MIRROR clause to backup the entire database, what is the purpose of such strategy?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, April 13, 2014 12:22 PM
  • Hello,

    I'm not sure what you're trying to accomplish, but it'd be better if you stuck to a full backup (if you're going to do all of the files at the same time anyway) and use backup striping to take advantage of the multiple disks.


    Sean Gallardy | Blog | Twitter

    Sunday, April 13, 2014 1:25 PM
  • To do what you are asking for, you would have to use one connection per file. If you put the statements in the same script, they are executed sequentially.

    If all you want to do is to speed up the backup by using multiple disks it would be better to do:

    BACKUP DATABASE db TO DISK = 'Y:\backup\...', 'Z:\backup\...'

    That is, backup the entire database in one statement, but write the backup to multiple places.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 13, 2014 2:21 PM
  • Thank you for your replying, all!

    I  explain my works.

    I execute with management studio.

    At first open two 「New query」 window, and execute the following command each window.

    We guess the backup process is executed parallel, but it run serial.

    You sounds starange, in our enviroonment,  one DB(create database manually) backup run pararell, but the other DB(running with SAP") doesn't.

    Are there any limitation? 

    regards,

    backup database <DB> FILE = <mdf>, FILE = <ndf1> to disk = Z:\backup ......

    backup database <DB> FILE = <ndf2>, FILE = <ndf3> to disk = Y:\backup

    Sunday, April 13, 2014 5:13 PM
  • What recovery model are the two databases in?

    SELECT name, recovery_model_desc
    FROM   sys.databases
    WHERE  name IN ('manualdatabase', 'SAPdatabase')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 13, 2014 6:55 PM
  • Hi,

    It's recovery model is "Full".

    We use one database.

    I mean <DB SID> is one.

    <DB SID>

      - data.mdf

      - data1.ndf

      - data2.ndf

    regards,

    Monday, April 14, 2014 2:01 AM
  • Thank you for your replying.

    Our recovery model is "Full".

    regards,

    Monday, April 14, 2014 3:05 AM
  • Hello,

    According to the BOL:

    Under the full recovery model, you must back up the transaction log, regardless of the rest of your backup strategy. A complete set of full file backups, together with enough log backups to span all the file backups from the start of the first file backup, is the equivalent of a full database backup

    Only one file backup operation can occur at a time. You can back up multiple files in one operation

    As per my understand, you can have two full file backup running at a time, but you can't have two simultaneous log backups. In your case, the file backups will run serial.

    Reference:Concurrent Administrative Operations

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support


    Monday, April 14, 2014 9:45 AM
  • Thank you for your replying.

    What is "Only one file backup operation"?

         "Only one file backup" operation

                The data files are backup one by one. It's sure all data files will backup at the end.  

        Only one file backup "operation"

                The "one" backup command can run.  If execute another backup command while the first backup command running, it were wait till the first backup command finish.

    regards,

    Monday, April 14, 2014 2:23 PM
  •             The "one" backup command can run.  If execute another backup command while the first backup command running, it were wait till the first backup command finish.

    Yes, that is what Books Online says. That is, the behaviour you see is expected.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 14, 2014 10:08 PM
  • Hello,

    This feels very similar to how our Oracle DBAs treat their systems. SQL Server, when it comes to backup and restore, is a much different beast and can't be treated the same at all. If you do, you're going to run into a large amount of issues.

    I would stripe the backup across the disks as already pointed out by multiple people, including myself and Erland.


    Sean Gallardy | Blog | Twitter

    Monday, April 14, 2014 10:12 PM
  • To get a bit more specific, with SQL Server backups, if you have a database <mydb> with the files:

    Data.mdf

    data2.ndf

    data3.ndf

    Mylog.ldf

    then you can issue a single backup command:

    BACKUP DATABASE mydb. TO DISK='X:\backups\mydb1.bak', DISK=Y:\backups\mydb2.bak', DISK=Z:\backups\mydb3.bak'

    As Erland said,

    SQL will read all of the data files in parallel, and write to all three backup files in parallel  The backup files will not have the data segregated, but this is the way to get the files all backed up in parallel.


    Microsoft SQL Server Storage Engine PM

    Tuesday, April 15, 2014 12:09 AM