March 17, 2017 at 6:01 am
I currently have a process setup where I take a backup everyday and restore it to another SQL Server. The only issue is that the .bak file name changes everyday. Is there a way that I can use a wild card to import the backup file and restore it to another SQL server. Thanks
March 17, 2017 at 6:33 am
I would expect you could do something like this with Powershell, although whether that's an option depends largely on how your process works.
The first question would be, how does the filename change?
Are the files named something like DBName_DATE.bak, or does the entire name change every time, something like {GUID}.bak?
If the former, then it should be fairly straight-forward, especially if the restore is happening on the same day as the backup was run. If the latter, you'll have to use something like the "date modified" or "date created" of the file (which almost certainly means Powershell)
More details of the process would help get better suggestions on doing what you need.
March 17, 2017 at 6:37 am
You can query the msdb tables on your server (the server that you are running your backup command) and find the full name
Something like this:
SELECT TOP 10 * FROM msdb.dbo.backupset A
INNER JOIN msdb.dbo.backupmediafamily B ON A.media_set_id = B.media_set_id
WHERE CONVERT(DATE, A.backup_start_date) = CONVERT(DATE, GETDATE()-1)
AND type = 'F'
March 17, 2017 at 6:40 am
jasona.work - Friday, March 17, 2017 6:33 AMI would expect you could do something like this with Powershell, although whether that's an option depends largely on how your process works.
The first question would be, how does the filename change?
Are the files named something like DBName_DATE.bak, or does the entire name change every time, something like {GUID}.bak?
If the former, then it should be fairly straight-forward, especially if the restore is happening on the same day as the backup was run. If the latter, you'll have to use something like the "date modified" or "date created" of the file (which almost certainly means Powershell)More details of the process would help get better suggestions on doing what you need.
The file name format is such: database_backup_2017_02_21_000501_3833034.bak The only part of the name that is static is "database_backup". I was hoping I could do this all in SQL or SQLCMD instead of running a script to change the name then restore the database with tsql.
March 17, 2017 at 6:50 am
chris.balbuena - Friday, March 17, 2017 6:40 AMjasona.work - Friday, March 17, 2017 6:33 AMI would expect you could do something like this with Powershell, although whether that's an option depends largely on how your process works.
The first question would be, how does the filename change?
Are the files named something like DBName_DATE.bak, or does the entire name change every time, something like {GUID}.bak?
If the former, then it should be fairly straight-forward, especially if the restore is happening on the same day as the backup was run. If the latter, you'll have to use something like the "date modified" or "date created" of the file (which almost certainly means Powershell)More details of the process would help get better suggestions on doing what you need.
The file name format is such: database_backup_2017_02_21_000501_3833034.bak The only part of the name that is static is "database_backup". I was hoping I could do this all in SQL or SQLCMD instead of running a script to change the name then restore the database with tsql.
Then I suspect, unless you can query across to the source servers MSDB as Luiz suggested, you'd have to do this with a command-line script.
March 17, 2017 at 7:54 am
Sue
March 17, 2017 at 8:05 am
I'm running STD so that would not work. I used to have a sql script that would import the name up the file into a temp table and rewrite it out in standard format but I wanted to see if there were any other ways to do it without it. Looks like the easiest way would be to script a sqlcmd to changes the name on modify/create date and then restore.. Thanks for all the help everyone.
March 17, 2017 at 9:50 am
chris.balbuena - Friday, March 17, 2017 6:01 AMI currently have a process setup where I take a backup everyday and restore it to another SQL Server. The only issue is that the .bak file name changes everyday. Is there a way that I can use a wild card to import the backup file and restore it to another SQL server. Thanks
use powershell to enumerate the folder and pull the required filenames for the restore
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply