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.