October 25, 2018 at 1:24 pm
Hello All,
I'm trying to copy a recent backup file from one server to another using the syntax below as part of restore database stored procedure within a SQL job. Unfortunately the script keeps throwing an error: "The system cannot find the file specified." I've created a share and granted permissions for the SQL Agent service account on the Source server. When I test executing using the hardcoded backup file name in the script it copies successfully, but I want it to dynamically check for the latest backup. Can someone review and advise what the issue may be?
Database backup source filename format- MyDatabase_backup_2018_10_25_175240_5281339.bak
Declare
@sourcefile_name nvarchar(256); Declare @cmd nvarchar(256);
SET
@sourcefile_name = 'MyDatabase_backup' + '_' + convert(varchar(10),getdate()-1,112)+'.bak'
SET @cmd = 'copy \\MyUNCpath\Backup\' + @sourcefile_name + ' ' + 'S:\Backup\'
EXEC master.sys.xp_cmdshell @cmd
Many thanks!
David
October 25, 2018 at 1:56 pm
I think if you PRINT @sourcefile_name, you'll see immediately why it's not working.
convert(varchar(10),getdate()-1,112) will NOT give you 2018_10_25_175240_5281339
You're going to need to get the list of files and parse for the filename that has the date you're after.
Personally, I'd look at the source server and configure the backup job to use a name that DOES match convert(varchar(10),getdate()-1,112)
October 25, 2018 at 2:36 pm
I tried using PRINT @sourcefile_name and it produces the same error message I mentioned initially in the post (i.e. "The system cannot find the file specified."). The backup file names are in the MyDatabase_2018_10_25_175240_5281339.bak format as they were created by a DB Maintenance plan and I can't change it. I would need to update code for this format. Do you have code example suggestions for this format?
October 25, 2018 at 3:41 pm
davidsalazar01 - Thursday, October 25, 2018 2:36 PMI tried using PRINT @sourcefile_name and it produces the same error message I mentioned initially in the post (i.e. "The system cannot find the file specified."). The backup file names are in the MyDatabase_2018_10_25_175240_5281339.bak format as they were created by a DB Maintenance plan and I can't change it. I would need to update code for this format. Do you have code example suggestions for this format?
When you use the print command to check what the source file name is, you would want to do it before it executes...so with your script, it would be something like: Declare @sourcefile_name nvarchar(256)
Declare @cmd nvarchar(256)
SET @sourcefile_name = 'MyDatabase_backup' + '_' + convert(varchar(10),getdate()-1,112)+'.bak'
PRINT @sourcefile_name
Another way to get the file names is to query the tables with backup information that are in msdb. Try joining msdb.dbo.backupset and msdb.dbo.backupmediafamily on media_set_id and play with querying that to get the information you are after. The type in backupset is the type of backup. D is a full backup. If you order by backup finish date desc, you can get the most recent backups.
A real basic example - If you just want the last full backup from one particular databases, it would be something like: SELECT TOP 1 mf.physical_device_name
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily mf
ON b.media_set_id = mf.media_set_id
WHERE b.[database_name] = 'YourDatabase'
AND b.type = 'D'
ORDER BY backup_finish_date desc
Sue
October 30, 2018 at 6:51 pm
I appreciated the suggestions above, but I'd like to use the SP that is being used for another production restore job that works. I'm still having an issue with getting this corrected. Note: the backup file name I'm trying to copy from the source server is appended with SQL Server's additional naming (i.e. _100007_0497985) The copy file backup logic in the SP works successfully when I hardcode the file name like it is below:
SET @sourcefile_name = 'MyDatabase_backup_2018_10_30_100007_0497985.bak'
But when I try to use the syntax to dynamically grab the recent prefixed database backup file shown below it throws an error "The system cannot find the file specified"
SET
@sourcefile_name = 'MyDatabase_backup' + '_' + convert(varchar(10),getdate()-1,112) + '.bak'
Can somebody re-work the code to parse the database backup file name in the format it currently is in so I can implement?
Many thanks in advance!
October 30, 2018 at 7:05 pm
davidsalazar01 - Thursday, October 25, 2018 2:36 PM
Many thanks in advance!
October 31, 2018 at 10:39 am
SELECT 'MyDatabase_backup' + '_' + convert(varchar(10),getdate()-1,112) + '.bak'
will give you:
{code}MyDatabase_backup_20181030.bak{code}
You can't really guess the rest of the file name, so you'll have to either get the list of files in the directory (example below) or change the job that generates the backup to use a name you CAN guess.
One option would be to use the (undocumented and unsupported) procedure xp_dirtree as below.
This will return the name of the first file that starts with MyDatabase_backup_20181030 with a .bak file extension sorted in descending order.
Try running this on your server to see what file name it returns.
Keep in mind that using undocumented features isn't best practice.
DECLARE @sourcefile_name NVARCHAR(64);
DECLARE @dirfiles TABLE (
fname NVARCHAR(max)
,depth TINYINT
,isFile BIT
);
INSERT INTO @dirfiles
EXECUTE ('EXEC master.sys.xp_dirtree ''\\MyUNCpath\Backup\'',0,1');
SELECT TOP 1 @sourcefile_name = fname
FROM @dirfiles
WHERE IsFile = 1
AND depth = 1
AND fname LIKE 'MyDatabase_backup' + '_' + convert(VARCHAR(10), getdate() - 1, 112) +
'%.bak'
ORDER BY @sourcefile_name DESC;
PRINT @sourcefile_name;
October 31, 2018 at 11:32 am
C DBA Lockhart - Wednesday, October 31, 2018 10:39 AMYou can't really guess the rest of the file name, so you'll have to either get the list of files in the directory (example below) or change the job that generates the backup to use a name you CAN guess.One option would be to use the (undocumented and unsupported) procedure xp_dirtree as below.
This will return the name of the first file that starts with MyDatabase_backup_20181030 with a .bak file extension sorted in descending order.
Try running this on your server to see what file name it returns.
Keep in mind that using undocumented features isn't best practice.
Not really accurate. The file name and location is in the backupmediafamily table. No need to find them executing xp_dirtree in the backup directory. Just need to use a query against msdb tables.
Sue
October 31, 2018 at 12:10 pm
Good point..
I've been assuming, when I should've been asking..
that the backups were being generated by another SQL Server on which this SQL Server does not have access to query msdb.
I assumed the point of this job was to copy the backup file from a folder that both servers have access to in order to copy the database from one server to another on a daily basis.
October 31, 2018 at 1:15 pm
C DBA Lockhart - Wednesday, October 31, 2018 12:10 PMGood point..
I've been assuming, when I should've been asking..
that the backups were being generated by another SQL Server on which this SQL Server does not have access to query msdb.
I assumed the point of this job was to copy the backup file from a folder that both servers have access to in order to copy the database from one server to another on a daily basis.
Really - you assumed that the backups are being done by a different SQL Server? Nonetheless, the history of backups (and restores) on an instance are written to msdb tables. Try a backup with a third part product and check the tables.
Sue
November 2, 2018 at 8:02 am
Re-read the initial post.
SQL Server A performs backup of database on SQL Server A to a shared folder.
Job on SQL Server B copies files from shared folder on Server A to local folder on Server B. (this job is currently failing)
November 2, 2018 at 8:03 am
davidsalazar01 - Thursday, October 25, 2018 1:24 PMI'm trying to copy a recent backup file from one server to another [...] I've created a share and granted permissions for the SQL Agent service account on the Source server.
November 5, 2018 at 11:02 am
does this help ??
-- choose database or currently used database will be selected
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT
s.server_name,
s.database_name,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.user_name,
s.backup_start_date,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
s.recovery_model, CASE s.[type]
WHEN 'D' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery, replace'
WHEN 'I' THEN 'Restore database ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery'
WHEN 'L' THEN 'Restore log ' + cast(s.database_name as nvarchar(max)) + ' from disk=''' + m.physical_device_name + ''' with norecovery'
end as cmd,
'exec xp_cmdshell ''copy ''' + m.physical_device_name + ''' ''' + replace (m.physical_device_name ,left(m.physical_device_name,2),'S:') + '''' cpy
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date ,
backup_finish_date
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply