question

Robp avatar image
Robp asked

I'm working on doing a sql backup from one server and doing the restore/overwrite

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.



USE [master]

RESTORE DATABASE [DatabaseName] FROM DISK = N'S:\DatabaseBackup_backup_2021_06_03_104440_4065060.bak'

WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5

GO


restorebackup-restore
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Robp avatar image Robp commented ·

In the example I used an S: drive instead of a B: drive...but the idea is the same...

0 Likes 0 ·

3 Answers

·
Kev Riley avatar image
Kev Riley answered

The way I've worked around this in the past is to have another step before the restore, that renames the backup file to a known, static name. You could easily do that with Powershell to find the latest .bak file (should only be one if your process is working correctly) and rename it to "BackupToRestore.bak", and then your restore process is hardcoded to use a file called "BackupToRestore.bak"


gci "B:\MovedBackups\" | sort LastWriteTime | select FullName -last 1 | %{
    rename-item $_.FullName "BackupToRestore.bak"
}
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

An alternative to powershell would be to use a linked server to query the source systems msdb and pull in the latest name.

But I would also advocate for powershell but a different track and use the dbatools module instead and utilise the community backed backup restore functions within that module.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

Just do an xp_DirTree from T-SQL using the 3rd parameter to make sure file names are visible. That way, you can easily do it all from a scheduled job without having to use PowerShell, etc.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.