August 25, 2004 at 6:03 pm
I have my full backups and differential backups set up in Jobs in SQL Server Agent. All of that is working fine. I now would like to come up with a way to add a second step to my backup process that will copy the newly created backup file to my backup server.
The big problem that I am having is that I am using a TSQL script to do the backup. For my second step I would like to do an Operating System Command to just copy the file over to mapped drive. My problem comes in with not knowing the name of the backup. My backups are named db_name_month_day_year.bak. So I would need a way in the Operating System Command to get the current date and dynamically insert that as the name of the file I want to copy.
August 26, 2004 at 11:28 am
I know you said you want to use operating system command. But, if you were to write it in SQL, you would have more flexibility.
Example: You could call your migration procedure from your backup script rather than setting up a new step.
Or - You could simply identify the newest file in your backup folder by sending the "DIR" to a temporary table and selecting max filename (that would require changing your naming convention to yy_mm_dd instead of mm_dd_yy). If you wanted to get really fancy, you could parse out the date into a datetime variable and check for either today's date or the newest date.
I've been setting up log shipping, and have a very simple stored procedure for copying the files. I'll paste it here, hoping it will help.
Steve
-- af_Ship_File
USE afDBA
IF EXISTS
(SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[af_Ship_File]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[af_Ship_File]
GO
CREATE PROCEDURE af_Ship_File
@in_file varchar(100),
@ship_loc varchar(100)
as
set nocount on
/*************************************************************/
--
-- Module Name: af_Ship_File
--
-- Description: Receives 2 parms.
-- @in_file: path and name of file to be shipped.
-- @ship_path: shipping location for the file.
-- Procedure which will ship the specified file to the central
-- repository.
--
-- Written By: Steve Phelps
--
-- Date: Aug 20, 2004
--
-- Modified :
-- Date:
--
--
-- USAGE:
-- exec af_Ship_File 'x:\SQL_Reports\Archive\DBASRV1_sp_config.txt',
-- '\\StandbyServer\ShareName\'
--
/*************************************************************/
-- the following declare and set are for testing only
-- declare @in_file varchar(100)
-- set @in_file = 'x:\SQL_Reports\DBASRV1_sp_config.txt'
-- set @filename = 'x:\sql_reports\DBASRV1_sp_config.txt'
declare
@command varchar(500)
set @command = 'copy ' + @in_file + ' ' + @ship_loc
print @command
exec master..xp_cmdshell @command
GO
August 27, 2004 at 8:25 am
This will get you the path and filename of the latest backup file.
SELECT DISTINCT B.PHYSICAL_DEVICE_NAME
FROM backupset a, backupmediafamily b, backupfile c
WHERE a.database_name = '<Your DB Name Here>'
AND a.type = 'D'
AND a.backup_start_date =
(SELECT MAX(backup_start_date)
FROM backupset WHERE database_name = '<Your DB Name Here>'
AND type = 'D')
AND a.backup_set_id = c.backup_set_id
AND a.media_set_id = b.media_set_id
Use xp_cmdshell to copy it where you need it.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 28, 2004 at 1:28 pm
Thank you both for your help. I now have this working. Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply