This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 89%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
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
Hi @Cataster ,
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".
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
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
Hi @Cataster ,
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
Of course I used a different database, like Test1
Hi @Cataster ,
you created a full backup of the DB first?
----------
(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Andreas Baumgarten
@Andreas Baumgarten ohhh i had to create a full backup first. I then appended -Incremental and it started working!
However, im confused, as far as a i know, differential backup is basically only additions/modifications since last backup gets created/restored on secondary instance.
But then whats the point of log shipping if differential backup does pretty much the same thing?
I thought log shipping is used because its inefficient to restore the full database backup regularly, and so we could just restore the transaction logs which are composed of just the changes on the primary database.
So then if differential backup does pretty much only the changes, whats the point of backing-up/restoring transaction logs separately (log shipping) then?
Hi @Cataster ,
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
@Andreas Baumgarten
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?
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.
@Erland Sommarskog
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.
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.
@Erland Sommarskog
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
So what does Backup-Sql-Database --help offer? (Sorry, I do very little PowerShell.
That should of course be Restore-Sql-Database.
Sadly, the command requires a shared network path and I dont have a network drive I can use for testing :
Well, in most cases, you do ship the logs to an instance on a different machine, and then you need a network share.
Reinventing the wheel just because you don't have a network share available, does not seem like a good argument.
@Erland Sommarskog except the problem is the developer refuses to give me the information until I have fully tested a log shipping solution locally :/
So ive been using my localhost and another localhost instance i created as my primary/secondary DB's, and randomly created DB's for testing purposes.
For crying out loud! Get a second computer to play with or create a VM to work with, and then try to with the dbatools solution.
@Erland Sommarskog hmm i gotta buy another PC lol
Did you try a network share on the SQL server itself?
For testing: Create a share with appropriate permissions on your SQL server.
(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Andreas Baumgarten
Hi Erland,
it looks like Restore-SqlDatabase doesn't provide a with standby option.
https://learn.microsoft.com/en-us/powershell/module/sqlserver/restore-sqldatabase?view=sqlserver-ps
----------
(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Andreas Baumgarten
Au contraire, the -standbyFile option is what we are looking for.
@Erland Sommarskog hmm i gotta buy another PC lol
In most organisations there are more than one PC.
Even if you do it at home, there are more than on PC in many homes, for instance old and retired computers than can come to use in situations like this.
And, as I said, you can use a virtual machine. If you have not gotten started with virtual machines, this is a good occasion. VM are great for tests when you need a throw-away machine. Or, like here, when you need an extra machine.
Then again, you should be able to emaulate a network share on your own machine with \YOURCOMPUTER\share.
But making a design decision based on that you only have one computer at hand right now is not a good decision.
Really?
If you say so .... Ok :-)
----------
(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
Regards
Andreas Baumgarten
WITH STANDBY takes a file as an argument, so what else would -standbyFile refer to? What they mean with "imaging strategy", I don't know.
Hi @Cataster ,
maybe this helps: https://learn.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
@Andreas Baumgarten 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?
@Andreas Baumgarten
I opted to use dbatools Invoke-DbaDbLogShipping command.
I couldnt use it before because of the network shared drive requirement, but @Erland Sommarskog 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