question

Cataster-7485 avatar image
0 Votes"
Cataster-7485 asked ErlandSommarskog commented

How to specify differential backup option?

Im implementing automated log shipping solution, and came accross differential backups. Im interested in this feature, however, the Backup-SqlDatabase documentation has no information about it... Is there a way to specify this option somewhere?

Here's my script so far:

 #First, create a 1-time full backup of the the primary instance DB that we are interested in shipping over to the secondary server
 Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.bak"
    
 #Restore the full .bak 1-time
 Restore-SqlDatabase -ServerInstance $SecondaryServerInstance -Database $DatabaseName -BackupFile "$BackupFilePath\$DatabaseName.bak" -AutoRelocateFile -NoRecovery -PassThru
    
 #Then, create a backup of the the primary instance DB transaction log that we are interested in shipping over to the secondary server
 Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.trn" -BackupAction Log
    
 #Next, restore the transaction log for the DB on the secondary server/instance (scheduled at some point)
 Restore-SqlDatabase -ServerInstance $SecondaryServerInstance -Database $DatabaseName -BackupFile "$BackupFilePath\$DatabaseName.trn" -RestoreAction Log
sql-server-generalwindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten converted comment to answer

Hi @Cataster-7485 ,

 Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -Incremental

This command creates a differential backup of the database 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The backup file is named "MainDB.bak".

Source: https://docs.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps#example-9--create-a-differential-backup

It's not part of a log file backup.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndreasBaumgarten

Cannot perform a differential backup for database "MainDB", because a current
database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option

0 Votes 0 ·

Hi @Cataster-7485 ,

replace MainDB with the name of your database?


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

1 Vote 1 ·

@AndreasBaumgarten

Of course I used a different database, like Test1

0 Votes 0 ·
Show more comments
Cataster-7485 avatar image
0 Votes"
Cataster-7485 answered ErlandSommarskog commented

@AndreasBaumgarten
I think dbatools will simplify this, it creates the jobs automatically and everything so thats awesome. but for the life of me i can't understand why its not working...

The jobs get created and the Database is restored on the secondary server instance from what it appears, yet the jobs are failing. Why is it failing?
I thought maybe it has to do with the logon service account, that maybe the sql service NT account doesnt have sufficient permissions to the directories, so i changed the logon to my local account since im admin. I know its risky but for the meantime, this is for testing purposes so its fine.
But that didnt work...

I updated sql based on KB4537869, restarted, still didnt work.

Here is my script:

 $params = @{
     SourceSqlInstance = $PrimaryServerInstance
     DestinationSqlInstance = $SecondaryServerInstance
     Database = $DatabaseName
     GenerateFullBackup = $true
     BackupNetworkPath = $SharedPath
     BackupLocalPath = $LocalPath
     CompressBackup = $true
     Standby = $true
     BackupScheduleFrequencyType = 'daily' 
     BackupScheduleFrequencyInterval = 1
     CopyScheduleFrequencyType = 'daily'
     CopyScheduleFrequencyInterval = 1
     RestoreScheduleFrequencyType = 'daily'
     RestoreScheduleFrequencyInterval = 1
     CopyDestinationFolder = $TransactionLogsCopyPath
     Force = $true #Ignore some errors in the parameters and assume defaults. It will also remove the any present schedules with the same name for the specific job.
 }
 Invoke-DbaDbLogShipping @params


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndreasBaumgarten
I resolved the issue!

At C:\Users\...\Documents\DB Log Shipping\Backups\Local there was no Test18 directory created. I created the folder Test18 manually, and the backup is now succeeding.

However, this is insane, because the Invoke-DbaDbLogShipping should have created the Test18 folder AUTOMATICALLY when i executed the command. Apparently its only creating the folder under the $SharedPath and $TransactionLogsCopyPath but NOT $LocalPath. For automation purposes, I ended up just setting the local path to the $SharedPath

However, for some reason I am getting this error at the transaction log copy job on secondary server:

2021-08-22 21:00:00.86 Starting transaction log copy. Secondary ID: 'c29f....'
2021-08-22 21:00:00.87 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2021-08-22 21:00:00.87 Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider)

I checked permissions, and clearly Im sysadmin...

125428-image.png


0 Votes 0 ·
image.png (38.6 KiB)

So how is that job set up? It's not a T-SQL job step is it?

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered AndreasBaumgarten edited

Hi @Cataster-7485 ,

maybe you are confusing something. I haven't written anything about dbatools Invoke-DbaDbLogShipping. That was Erland ;-)
I had given the hint to the local share same as Erland did.

125413-image.png


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten



image.png (105.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cataster-7485 avatar image
0 Votes"
Cataster-7485 answered Cataster-7485 edited

@AndreasBaumgarten
I opted to use dbatools Invoke-DbaDbLogShipping command.
I couldnt use it before because of the network shared drive requirement, but @ErlandSommarskog had suggested I can emulate my machine as a network drive, which didnt cross my mind last week

One thing I dont understand is how is the 15 min interval in the example determined...The interval is set to "1" but I think 1 is used to specify a daily interval frequency TYPE, not interval TIME.

So whats the parameter to increase the interval time from 15 mins to say 30 mins or 2 hours?

Also, for now I utilized the example given and just changed the database to whatever my database is. i checked and looks like the backup/copy/restore operations are created, but Im seeing these errors:

Backup Agent Log:

Backing up transaction log. Primary Database: 'Test14'<c/> Log Backup File: 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn'<nl/>
Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) <nl/>
Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data)
Error: Object must implement IConvertible.(mscorlib)
First attempt to backup database 'Test14' to file 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn' failed because Cannot open backup device 'C:\Users...\Documents\DB Log Shipping\Backups\Local\Test14\Test14_20210822193000.trn

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered AndreasBaumgarten edited

If you want to implement a log-shipping solution, I don't really see much use for the differential backup. For log shipping you typically start with restoring a full backup, and the you apply transaction logs from there, using the option WITH STANDBY, so that you can apply more logs.

Possibly, if you have a very large database you start with a full backup, but by the time you have it on the log-shipping server it is quite old, and for some reason you don't want to apply those logs, so you take a differential, to get a starting point more closer in time. But it does not sound compelling to me.

· 14
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog
I see. How do i specify the STANDBY option in the Backup/Restore powershell command I have thus far?

Btw, the reason we're using powershell to automate this rather than the log shipping config tool is because this way we can handle new databases added over time automatically, otherwise we'd have to configure log shipping for each new database.

0 Votes 0 ·

I see. How do i specify the STANDBY option in the Backup/Restore powershell command I have thus far?

So what does Backup-Sql-Database --help offer? (Sorry, I do very little PowerShell.

Btw, the reason we're using powershell to automate this rather than the log shipping config tool is because this way we can handle new databases added over time automatically, otherwise we'd have to configure log shipping for each new database.

Make sense. But did you check if there is something already that you can use at dbatools.io? That's the place to go to for PowerShell with SQL Server.

0 Votes 0 ·

@ErlandSommarskog
that was the first thing i tried, dbatools!
Sadly, the command requires a shared network path and I dont have a network drive I can use for testing :/
I tried sharepoint but even bypassing the trusted zones didnt help with access. So i just scrapped dbatools and opted for the native Backup/Restore powershell commands instead

0 Votes 0 ·
Show more comments

So what does Backup-Sql-Database --help offer? (Sorry, I do very little PowerShell.

That should of course be Restore-Sql-Database.

0 Votes 0 ·
Show more comments
AndreasBaumgarten avatar image
1 Vote"
AndreasBaumgarten answered Cataster-7485 commented

Hi @Cataster-7485 ,

the full backup contains the full db and the log files.
The differential backup contains the db and log files but only the changes since full backup.
The log backup contains just the log backups since db full backup and not the db file(s).

The full backup is the base to restore the full DB.
The differential backup is smaller than a full backup but contains db and log files.
The log backup is the smallest/fastest backup because only the transaction logs since full or differential backup are in the backup.

Lets assume the following backups (F= Full Backup, D = Differential Backup, L = Log Backup):
F1 - L1 - L2 - L3 - D1 - L4 - L5 - L6

If you want to restore the full db you need F1 + D1 + L4 +L5 + L6 (L1, L2 and L3 aren't needed because D1 contains everything that changed since F1).

I don't think that differential backups are a good option for SQL Log Shipping. I know SQL log shipping using one Full Backup and than Log Backups in a short interval.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndreasBaumgarten
Amazing explanation, thank you Andreas!

So I am scrapping the idea of differential log backups.
However, Im running into this issue if I rerun the log restore operation over and over

 Restore-SqlDatabase : System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward.


I believe per Earl's comment, i should be using STANDBY but idk whats the equivalent of standby for Backup-sqldatabase/Restore commands?

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered Cataster-7485 commented

Hi @Cataster-7485 ,

maybe this helps: https://docs.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps#example-9--create-a-differential-backup

 Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -Incremental


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndreasBaumgarten oh wow how did i miss that example! Btw Andreas, do i specify the differential backup on the .bak command or the transaction log command?

  Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.bak" -Incremental

or

 Get-SqlDatabase -ServerInstance $PrimaryServerInstance -Database $DatabaseName | Backup-SqlDatabase -BackupFile "$BackupFilePath\$DatabaseName.trn" -Incremental -BackupAction Log

or both?

0 Votes 0 ·