I'm working on doing a sql backup from server one and doing the restore/overwrite on server two. I've got the first part setup to where it's saving the .bak file from server one to server two, however the .bak file has a suffix of the date saved to it for example "backup_2021_06_03_104440_4065060.bak".
I'm working on creating a sql job on server two to take that moved backup from server one and do a restore, However, that suffix is going to change when a backup is taken and I'm not sure how to dynamically set that up when doing the restore/overwrite on server two. Is there a way in the maintenance job on server one to save the .bak file without the date suffix and keep the name static? Is there another way to do the restore from looking at what files are available in the directory? The file would be in a directory like this B:\MovedBackups\DatabaseBackup_backup_2021_06_03_104440_4065060.bak. Is there a way to create the restore/overwrite job to just recognize the file that exists in B:\MovedBackups\ ?
I'll have a job that removes the backup file once the restore is done, so the same name can be used and only one file exists at a time.
RESTORE DATABASE [DatabaseName] FROM DISK = N'S:\DatabaseBackup_backup_2021_06_03_104440_4065060.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5