none
DATABASE BACKUP PROCESS RRS feed

  • Question

  • Hi , 

        I have configued a sql server with 3 node clustering in my company. I have configured a script for everyday backup process. this script reducing the size of the transaction log and backupping the sql database an replace the older backup .The backup is stored in a a folder[ D:\backup_sql].

    My questions are: 

    1. Where is stored temporaly the sql database file when backup process is executing ? in which folder ? I didn't find in the file in Temp folder.
    2. If a a backup process is stopped suddenly , what will happen ? It will corrupt the older backup which the backup process suppose to replace or older backup will stay intact ?

    Could you please someone answer for these question because i did’nt find the answer .

    BR,

    Techni


    Monday, August 12, 2019 10:09 AM

Answers

  • Hi technicien14,

     

    You didn't clarify that you are in the availability group environment, which led us to think that this is a sql cluster. You can perform both log backups and database backups on the secondary replica. It also truncates the logs on all replicas, frees up space, and relieves the pressure on the primary replica.

     

    For more details, please refer to https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by technicien14 Monday, August 19, 2019 12:22 PM
    Tuesday, August 13, 2019 9:46 AM
  • This is no different from a normal backup. When you suddenly interrupt during the backup process (equivalent to a backup failure), the old backup will not be overwritten or deleted.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by technicien14 Monday, August 19, 2019 12:22 PM
    Monday, August 19, 2019 8:58 AM

All replies

  • I don't understand your question, what do you mean here? During the backup the database files stays where they are, there is nothing temporaly created.

    Question 2 depends on the exact process you perform, if the old backup is moved/deleted first and the backup stops, then you don't have any valid backup at all.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2019 10:41 AM
  • Hi , 

         I thought the backup is created in a tempaoraly forlder and it moves to the destination folder [D:\backup_sql] after the end of the backup process. Is it true ?

    Here is the scipt i use : 

    DECLARE @primaryReplica int 
    SET @primaryReplica = (SELECT sys.fn_hadr_is_primary_replica ('test'))
    IF (@primaryReplica =0)
    BEGIN
    BACKUP DATABASE [test] TO DISK = N'D:\BACKUP_SQL\test_Bdd' WITH COPY_ONLY, INIT ;
    END

    According to the script, do you think the old backup is deleted before the creation of a new backup ? 

    Thanks in advance.

    BR, 

    Techni


    Monday, August 12, 2019 11:00 AM
  • The backup file is created where you define it, so directly in Folder D:\BACKUP_SQL\ and nowhere else.

    You use the Option COPY_ONLY, is that really intended? In productive enviroment you shouldn't use that Option, only e.g. to create a copy for a test database restore.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2019 11:26 AM
  • Hi , 

    Tha,k you for your answer. Do you think if the backup process stopped suddenly , it will corrupt the older backup ? Isn't it ?

    I explain why i have implement this solution.

        I have confronted a of increading transaction-log without limit.  this filled the disk completely , I was not able to do anuthying else in the database. Then i have decided to implement a solution.

    My infrastucture contains 3 nodes. 1 primary nodes and 2  secondary nodes.Everyday a script l execute in the seconday node to backup the database . Another script  execute in the primary node to backup transaction-log and reduce its size. 

    Here are the two types of scipts that iw use : 

    This script is executed in the seconday nodes everyday.

    DECLARE @primaryReplica int 
    SET @primaryReplica = (SELECT sys.fn_hadr_is_primary_replica ('test'))
    IF (@primaryReplica =0)
    BEGIN
    BACKUP DATABASE [test] TO DISK = N'D:\BACKUP_SQL\test_Bdd' WITH COPY_ONLY, INIT ;
    END

    This scipt is executed in the primay node everyday.

    DECLARE @primaryReplica int
    SET @primaryReplica = (SELECT sys.fn_hadr_is_primary_replica ('test'))

    IF (@primaryReplica =1)
    BEGIN
        BACKUP LOG [test] TO DISK = N'D:\BACKUP_SQL\test_log.trn' WITH NOFORMAT,INIT, SKIP, REWIND, NOUNLOAD,  STATS = 10
    DBCC SHRINKFILE ( test_log, 10240)
    END

     Do you think this type of backup is not safe ? what is the best backup to save my databse from a possible crash ? 

    Best Regards, 

    Techni

    Monday, August 12, 2019 11:55 AM
  • Hi technicien14,

     

    You didn't clarify that you are in the availability group environment, which led us to think that this is a sql cluster. You can perform both log backups and database backups on the secondary replica. It also truncates the logs on all replicas, frees up space, and relieves the pressure on the primary replica.

     

    For more details, please refer to https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by technicien14 Monday, August 19, 2019 12:22 PM
    Tuesday, August 13, 2019 9:46 AM
  • Hi Dedmon Dai, 

                          Thnaks for your answer. According to the script, do you think the old backup is deleted before the creation of a new backup ? 

    DECLARE @primaryReplica int 
    SET @primaryReplica = (SELECT sys.fn_hadr_is_primary_replica ('test'))
    IF (@primaryReplica =0)
    BEGIN
    BACKUP DATABASE [test] TO DISK = N'D:\BACKUP_SQL\test_Bdd' WITH COPY_ONLY, INIT ;
    END

    what happens if a a backup process is stopped suddenly , what will happen ? It will corrupt the older backup which the backup process suppose to replace or older backup will stay intact ?

    I need to know before finalizing the documentation for the client. Could you please anyone answer for this question ? 

    Best regards,

    Techni

    Tuesday, August 13, 2019 11:47 AM
  • This is no different from a normal backup. When you suddenly interrupt during the backup process (equivalent to a backup failure), the old backup will not be overwritten or deleted.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by technicien14 Monday, August 19, 2019 12:22 PM
    Monday, August 19, 2019 8:58 AM
  • Hi , 

        Thank you very much for the answers.

    Best regards, 

    Techni

    Monday, August 19, 2019 12:23 PM