1

First, I need to get an assumption verified: If I setup a differential backup plan and someone comes along and manually creates a backup without specifying COPY_ONLY then it's going to throw the plan off, right? (I'm assuming a scenario where they backup to some arbitrary location and then maybe even delete the backup they created after they're done with it).

Secondly, we currently have a RedGate backup solution in place that uses the code below. I'm not familiar with how the master..sqlbackup proc it uses works so I am wondering if it's possible for me to leave this in place if I setup a differential plan alongside it or if I would need to disable this.

Why would I do this? Well, we're migrating to the cloud and I'd like to setup differential backups for the purpose of minimizing downtime as we move the database. But I'd just as soon not mess with the existing backup plan if I don't have to. And if I have to then I have to, but would rather not just in case something goes awry with my backup plan in the process.

DECLARE @exitcode INT , @sqlerrorcode INT;

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES 
    [DB1, DB2, etc]

    TO DISK = ''F:\MSSQL11.MSSQLSERVER\MSSQL\Backup\<TYPE>\<DATABASE>\<AUTO>'' 
    WITH    ERASEFILES_ATSTART = 1b, 
            CHECKSUM, 
            DISKRETRYINTERVAL = 10, 
            DISKRETRYCOUNT = 3, 
            COMPRESSION = 4, 
            THREADCOUNT = 4, 
            NAME = ''<AUTO>'', 
            DESCRIPTION = ''<AUTO>''"', 
    @exitcode OUTPUT, @sqlerrorcode OUTPUT

IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

EDIT: And I can already imagine someone might ask why I don't just use RedGate to do the diff backup plan. First, I have no experience with it and second we won't have RedGate on the new server.

Improve this question

1 Answer 1

Reset to default
2

If I setup a differential backup plan and someone comes along and manually creates a backup without specifying COPY_ONLY then it's going to throw the plan off, right?

Yes, your next differential backup will have a differential base LSN of the full backup the other person performed so your restore chain would require that backup.

Secondly, we currently have a RedGate backup solution in place that uses the code below. I'm not familiar with how the master..sqlbackup proc it uses works so I am wondering if it's possible for me to leave this in place if I setup a differential plan alongside it or if I would need to disable this.

If you add a differential backup in parallel to this, your differentials will still require the RedGate backups in order to restore the DIFF because the RG backups will reset the differential base LSN.

Why would I do this? Well, we're migrating to the cloud and I'd like to setup differential backups for the purpose of minimizing downtime as we move the database. But I'd just as soon not mess with the existing backup plan if I don't have to. And if I have to then I have to, but would rather not just in case something goes awry with my backup plan in the process.

EDIT: And I can already imagine someone might ask why I don't just use RedGate to do the diff backup plan. First, I have no experience with it and second we won't have RedGate on the new server.

If you don't have RedGate on the new server, how are you planning to restore FULL the backups taken by RedGate in order to restore your differentials? My understanding is that restoring a backup taken by RedGate requires RedGate to be installed. I could be wrong (limited experience with RG) but this is how most third-party backup tools for SQL Server work, usually because of custom encryption or compression that is applied to the backups.

Without additional info available, I would suggest one of the following:

  1. Replace RG on the existing server now with whatever you're planning to use in Azure as an end-state (unless you're using Managed Backups in Azure).
  2. Look at Log Shipping to keep your Azure server in sync with on-premise until you're ready to cut over. You would have to disable any RedGate log backups that are in place.
Improve this answer
2
  • Sorry, I guess I thought this was clear, but the differential plan would NOT be with RedGate. That's kind of the point since, like you said, I can't restore RG DBs w/o RG.
    – BVernon
    Commented May 14, 2019 at 17:28
  • I'm thinking if I modify the script in my post to do COPY_ONLY then that should continue to work without affecting my differential plan though, right?
    – BVernon
    Commented May 14, 2019 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.