SQL Log Shipping – Date Time Format in Transaction Log File Name

log-shippingsql serversql server 2014

Looking at my SQL log shipping file names (following is an example)

Q:\Backups\LogShipping\MyAudit\MyAudit_20201027165400.trn

What date time format is the transaction log expressed in (local military time, UTC time)?

Best Answer

What date time format is the transaction log expressed

Yes, it is UTC time with the format dbname_YYYYMMDDHHMMSS (time is backup start time in 24 hours format). I think, following screenshot (copied from..) can help for easy validation.

Left side: Highlighted is Log Shipping backup name with UTC datetime
Right side: Date modified column indicating the time in +4 time-zone

Related Solutions

SQL Server Log Shipping – File Copy Options

I had the same requirement as a component for our Disaster Recovery solution. Due to security and policy reasons, it was not possible to use regular Log Shipping.

We ended up transferring the logs via Linux servers and rsync, and implementing the Log Backup restore using a small application which can be found at https://github.com/kervinpierre/mssqlapplylogs

Resume SQL Server Log Shipping from Specific Transaction

I have manually reset the log shipping by updating the last_restored_file column in the log_shipping_monitor_secondary table on the standby server to the last transaction log that was successfully restored.

Something like this:

UPDATE lsms 
SET lsms.last_restored_file = '{transaction log file}'
FROM dbo.log_shipping_monitor_secondary lsms
WHERE lsms.secondary_database = '{DB Name}';

Best Answer

Related Solutions

SQL Server Log Shipping – File Copy Options

Resume SQL Server Log Shipping from Specific Transaction

Related Question