Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: DB backup script

    Hello, I inherited a SQL Server from my predecissor and came accross this backup script..Can anyone please help me in understanding what this means?
    This script is currently backing up the 1.3GB database. and the current backup file size is 67GB. Maybe this script is adding up all the backs cummulatively everyday. I want it to make a fullbackup once a day and make incremental backups remaining 6 days. At the end of the week, I want to save the file somewhere and then start fresh with a new backup file. Is that possible? How do I achieve it. Any online sources to read some helpful tips? Appreciate your help...
    ---------------------

    BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
    select @i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
    RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @i
    Kishore

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You are correct. The script is only appending backups. The NOINIT is what does that. If you want to overwrite the file change NOINIT to INIT.

    As to changing it, you can set up 2 jobs under SQLAgent. The first runs weekly, and does the following:
    1) copy the old backups to a new directory
    2) Backs up the database (with init)

    The second job would run 6 days a week and just do the differential backups probably to the same file, and let the weekly job pick up it's old file.

    Play around with jobs in SQLAgent, and you should get it quickly enough. Also, look up BACKUP in BOL, so you can see all the bells and whistles there.

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by MCrowley
    You are correct. The script is only appending backups. The NOINIT is what does that. If you want to overwrite the file change NOINIT to INIT.

    As to changing it, you can set up 2 jobs under SQLAgent. The first runs weekly, and does the following:
    1) copy the old backups to a new directory
    2) Backs up the database (with init)

    The second job would run 6 days a week and just do the differential backups probably to the same file, and let the weekly job pick up it's old file.

    Play around with jobs in SQLAgent, and you should get it quickly enough. Also, look up BACKUP in BOL, so you can see all the bells and whistles there.
    Hi Thanks.
    Thats a good suggestion. I will do that. Also, In the script that I copied F is for full. What needs to be changed to make full to differential.
    I want to create 2 jobs.
    1- Full backup every friday night with INIT
    2- Diff backup the remaining days with NOINIT.
    I will have a windows scheduler to copy this file every week to alternate location.
    Kishore

  4. #4
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by kingno1
    Hello, I inherited a SQL Server from my predecissor and came accross this backup script..Can anyone please help me in understanding what this means?
    This script is currently backing up the 1.3GB database. and the current backup file size is 67GB. Maybe this script is adding up all the backs cummulatively everyday. I want it to make a fullbackup once a day and make incremental backups remaining 6 days. At the end of the week, I want to save the file somewhere and then start fresh with a new backup file. Is that possible? How do I achieve it. Any online sources to read some helpful tips? Appreciate your help...
    ---------------------

    BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
    select @i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
    RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @i
    The switch database_name='STS_pthsps01_1'and type!='F' means that it is fullbackup. How do I change it to be Differential? I tried creating a new job, but, its all T-SQL that I can do. I didnt see a GUI way.
    Kishore

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The restore command actually ends at "STATS = 10, NOFORMAT" After that, a query to determine the filenumber starts. The restore verifyonly checks the validity of the backup (kinda).
    The types of backups are as follows:
    D: Full
    I: Differential (incremental)
    L: Transaction log

    Why the query is looking for type not equal to "F", I am not sure, since all of them are not equal to "F".

  6. #6
    Join Date
    Mar 2004
    Posts
    84
    Thanks Mcrowly for the clarification. I am clear now on the existing command. Now, Looking at the script, I am not seeing it to backup FULL. Can you please point me to where I can say backup D or backup I? I looked in BOL, and couldnt find any help from the T-SQL perspective there. Appreciate your help.
    Kishore

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A full backup is the default behaviour of the BACKUP DATABASE command. In order to get a differential backup, you have to supply the DIFFERENTIAL argument in the WITH list.

  8. #8
    Join Date
    Mar 2004
    Posts
    84
    Hi, Thanks for your reply.
    I have modified the script to take differential as follows and it works fine.. Is this correct?
    --------------------
    BACKUP DATABASE [STS_pthsps01_1] TO DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH NOINIT , DIFFERENTIAL, NOUNLOAD , NAME = N'STS_pthsps01_1 backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
    select @i = position from msdb..backupset where database_name='STS_pthsps01_1'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='STS_pthsps01_1')
    RESTORE VERIFYONLY FROM DISK = N'E:\PTHSPS01_BACKUP\SharepointDB' WITH FILE = @i
    --------------------------------


    Looks like this script backs up the diff between the full backup and the time diff is executed. The next diff backup will be again between full backup and 3rd day.
    1) Full backup
    2) Diff bwtween 1 and 2
    3) Diff between 1 and 3
    4) Diff between 1 and 4

    How do we make the 3) to be 2 and 3 instead of 1 and 3?
    Kishore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •