• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

How to change a filename during a swl on the fly.

Some Background:
My company has production servers and on a daily basis we download a database to our lan so we can run reports from it. The code we use to load the database is as follows:

USE master
RESTORE DATABASE cheshire_Reporting
FROM DISK = 'C:\BAckup\cheshire\cheshire_backup_200711190030.bak'
, MOVE 'cheshire_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cheshire_reporting_Data.mdf'
, MOVE 'cheshire_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cheshire_reporting_Log.ldf'
, MOVE 'sysft_ft_cheshire' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sysft_ft_cheshire_reporting'
I created a sql jog with this code and it works with no problems.

My production server is set up to save the .bak file as the filename_Backup_Date.bak.
For ex. - cheshire_backup_200711190030.bak
This date changes everyday

Question - Is there a way to create a sql server job that I can set up to run daily that will take this name
change into consideration??


1 Solution
DECLARE @place varchar(max)
set @place = 'C:\BAckup\cheshire\cheshire_backup_' + replace(replace(replace(convert(varchar(16), getdate(), 20),'-', ''),' ',''),':','') + '.bak'
RESTORE DATABASE cheshire_Reporting
FROM DISK = @place
hexvaderAuthor Commented:
We had to manipulate the code slighly to deal with the date actually being 1 day behind the current date but it worked like a charm.
Thanks and happy Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now