How to schedule a SQL FULL backup WITH COPY_ONLY

  • Jeffrey Williams-493691 (1/14/2010)


    Dugi (1/14/2010)


    Copy Only Backup with SSMS are supported just in SQL Server 2008!

    http://msdn.microsoft.com/en-us/library/ms187510.aspx

    Wrong - COPY_ONLY is not available when using the GUI in SSMS 2005, however - you can still perform copy only backups using the BACKUP command directly.

    Actually, it is just not supported when using the Backup Database task in the maintenance plans. You can always use an Execute SQL Task and build your backup command yourself, including the COPY_ONLY parameter.

    Here I'm talking for the SQL Server 2008 no for SQL Server 2005! - sorry a!!!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • In SQL Server 2008 you can do copy only backup with GUI and you can script it as T-SQL, then creating the Maintenance Plan for schedule with that T-SQL.

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • homebrew01 (1/13/2010)


    Why do you want scheduled COPY ONLY backups ? That function is usually used for one-time backups that will not affect the log chain.

    No, it is used (when used with a full database backup) for backups that do not change the differential base. Full backups never affect the log chain, unless said log chain was broken before the full backup ran.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • xxdbxx (1/14/2010)


    Correct. The thread-starter did not mention versions and since they seemed to know what copy - only backups were I assumed they were on SQL 2008.

    I'm using SQL Server 2005. That's why I'm posting in the SQL Server 2005 forum, not SQL Server 2008.

    Can anyone help with a script that do FULL backup WITH COPY_ONLY to a file, with a filename stating day and time of backup and a guide how to schedule the script ?

    I'm a newbie to SQL server scripting....

    I want COPY_ONLY backup because it's important for us to have backup inhouse (FULL) for fast restore, if an accident happens and we can't restore from our remotebackup.

    Thanks in advance,

    🙂 Jens

  • Are you using differential backups? If not, there's no real reason to take copy-only backups.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/14/2010)


    Are you using differential backups? If not, there's no real reason to take copy-only backups.

    Gail - the concern I would have here is that the OP is looking at automating the process. Once automated, everyone forgets that it is there - and then what happens when the database gets large enough, or business continuity requirements change and now differential backups are being performed?

    Always better to be safe than find out during a disaster that you can't recover the system.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • GilaMonster (1/14/2010)


    Are you using differential backups? If not, there's no real reason to take copy-only backups.

    We have remotebackup of the SQL server. We can't restore from LOG backup if we take FULL backup to our NAS. We have tried, doesn't work....

    Thanks in advance,

    🙂 Jens

  • http://www.veloci-group.com/remotebackup.html (1/15/2010)


    GilaMonster (1/14/2010)


    Are you using differential backups? If not, there's no real reason to take copy-only backups.

    We have remotebackup of the SQL server. We can't restore from LOG backup if we take FULL backup to our NAS. We have tried, doesn't work....

    I'm going to guess one of those is not native backups, or else there's something stranger going on here.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeffrey Williams-493691 (1/14/2010)


    Gail - the concern I would have here is that the OP is looking at automating the process. Once automated, everyone forgets that it is there - and then what happens when the database gets large enough, or business continuity requirements change and now differential backups are being performed?

    Always better to be safe than find out during a disaster that you can't recover the system.

    Good point.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/15/2010)


    http://www.veloci-group.com/remotebackup.html (1/15/2010)


    GilaMonster (1/14/2010)


    Are you using differential backups? If not, there's no real reason to take copy-only backups.

    We have remotebackup of the SQL server. We can't restore from LOG backup if we take FULL backup to our NAS. We have tried, doesn't work....

    I'm going to guess one of those is not native backups, or else there's something stranger going on here.

    Please enlighten me ? I'm by no mean an expert on SQL....

    If I restore full backup + the following log backup from remotebackup everything is fine. But if I do the same thing, but now there's a second full backup (to NAS) in between 2 log backups...... You say to me that it would work ?

  • http://www.veloci-group.com/remotebackup.html (1/15/2010)


    But if I do the same thing, but now there's a second full backup (to NAS) in between 2 log backups...... You say to me that it would work ?

    It should work. Log backups don't break the log chain. If it doesn't and all of the backups are native SQL backups, there's something odd happening.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply