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(12.8, 39%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJD%3C/text%3E%3C/svg%3E)
This question is very closely related to my previous post, but I am posting here again because this is little bit time sensitive.
Currently, I have pipeline completed where I use T-SQL statement as below to create a BAK file daily in Prem SQL:
BACKUP DATABASE YourDB
TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;
Then, I use PowerShell script to move BAK file into Azure Storage.
This process is for an external client who is going to use that BAK file to restore into their DB.
My question is: using the top T-SQL statement, would BAK file contain most recent data daily?
Or do I have to modify anything on the T-SQL statement?
When I run that T-SQL statement, the file appears to be updated daily (with most recent date and time stamp), so I assume it gets all data needed, but I am not sure that is true or not.
I would appreciate for your feedback.
As I answered in the previous thread: With that exact command, the file will include the most recent backup and all previous backups written to the same file. This can sometimes be useful, but it is more likely to cause confusion. And make the file too large.
Add INIT to avoid this:
BACKUP DATABASE YourDB
TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION, INIT;
@Erland Sommarskog Thank you so much again! What does INIT do?
INIT clears all existing backups in the file, so that the only backup you have in the file is the most recent one.
@Erland Sommarskog Thank you. I just added that line, and it appears that the size of BAK file got shrunk. I guess that helps. So, I should be good to go (nothing else to improve), correct?
I guess so. But again, if your client wants to restore this backup into Azure SQL Database that will not work out.
Yes, using the T-SQL statement you provided, the BAK file will contain the most recent data from the database at the time the backup is executed. The command:
BACKUP DATABASE YourDB
TO DISK = 'D:\Backups\YourDB.bak' WITH COMPRESSION;
creates a full backup of the database YourDB, and since you are running this command daily, each backup will reflect the state of the database at the time of the backup. The updated timestamp on the BAK file indicates that the backup process is successfully overwriting the previous file with the latest data.
You do not need to modify anything in your T-SQL statement for it to capture the most recent data; it is functioning as intended. Just ensure that the backup process is scheduled to run daily without interruptions to maintain the most current data in the BAK file.