Here is the SQL2000 code (not that the SQL2000 Job Agent has a lower limit on the amount of chars allowed in the T-SQL statement so you have to put this code into a Stored Proc.
PRINT 'Starting up...'
PRINT CONVERT(varchar(100),GETDATE(),121)
DECLARE @dest_dbname varchar(255)
DECLARE @source_path varchar(255)
DECLARE @dest_filepath varchar(255)
DECLARE @log_filepath varchar(255)
DECLARE @stage_db_data_dir varchar(255)
DECLARE @stage_db_log_dir varchar(255)
-- These should be the only variables that you change
SET @dest_dbname = '[Intranet_Stage]'
SET @source_path = '\\srv-sql2000\SQLBackups\Intranet\'
SET @dest_filepath = '\\srv-devsql2k\MostRecent\Intranet.bak'
SET @log_filepath = '\\srv-devsql2k\MostRecent\Intranet_copy_log.txt'
SET @stage_db_data_dir = 'E:\SQL Server\Data\MSSQL\Data\'
SET @stage_db_log_dir = 'G:\SQL Server\Data\MSSQL\TLogs\'
DECLARE @source_mask varchar(255)
DECLARE @dir_cmd varchar(255)
DECLARE @copy_cmd varchar(255)
DECLARE @singleuser_sqlcmd nvarchar(255)
DECLARE @multiuser_sqlcmd nvarchar(255)
DECLARE @dbrestore_sqlcmd nvarchar(2000)
SET NOCOUNT ON
/* ********
This block pulls a DIR from the path specified above and
then generates and executes a COPY command based on the
most recent file. NOTE: does nothing if no matching file is found
***** */
PRINT 'searching for a file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
SET @source_mask = @source_path +'*.bak'
SET @dir_cmd = 'dir "'+@source_mask+'" /o-d /b'
CREATE TABLE #T ([output] varchar(255))
--fill temp table with the dir listing
INSERT INTO #T
EXECUTE [master].[dbo].xp_cmdshell @dir_cmd
DELETE FROM #T WHERE [output] IS NULL--remove empty lines
IF EXISTS (SELECT * FROM #T WHERE [output] LIKE '%.bak')
BEGIN
PRINT 'file found, copying file...'
PRINT CONVERT(varchar(100),GETDATE(),121)
--build COPY command
SELECT TOP 1 @copy_cmd = 'copy /V /B /Y /Z "' + @source_path + [output] +'" "'+@dest_filepath +'" > "'+@log_filepath+'"'
FROM #T
ORDER BY [output] DESC
EXEC [master].[dbo].xp_cmdshell @copy_cmd--run COPY command
SET @singleuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET single_user WITH ROLLBACK IMMEDIATE'
SET @dbrestore_sqlcmd = 'RESTORE DATABASE '+@dest_dbname+' FROM DISK = '''+@dest_filepath+''' WITH REPLACE '
SET @multiuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET multi_user WITH ROLLBACK IMMEDIATE'
CREATE TABLE #FILELISTONLY (
[LogicalName] [varchar](255) NULL,[PhysicalName] [varchar](255) NULL,[Type] [varchar](50) NULL,[FileGroupName] [varchar](255) NULL,[Size] [varchar](50) NULL,[MaxSize] [varchar](50) NULL
)
declare @st nvarchar(255)
set @st='Restore FILELISTONLY FROM DISK='''+@dest_filepath+''''
insert into #FILELISTONLY
exec [master].[dbo].sp_executesql @statement=@st
--exec('Restore FILELISTONLY FROM DISK='''+@dest_filepath+'''')
WHILE EXISTS (SELECT * from #FILELISTONLY WHERE [Type] IN ('D','L'))
BEGIN
DECLARE @UIDvarchar(250)
SELECT TOP 1 @UID=[PhysicalName] FROM #FILELISTONLY WHERE [Type] IN ('D','L') ORDER BY Type
SELECT TOP 1 @dbrestore_sqlcmd=@dbrestore_sqlcmd+
CASE [Type]
WHEN 'D' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_data_dir+[LogicalName]+'.mdf'' '
WHEN 'L' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_log_dir+[LogicalName]+'.ldf'' '
END
FROM #FILELISTONLY WHERE [PhysicalName]=@UID
DELETE FROM #FILELISTONLY WHERE [PhysicalName]=@UID
END
drop table #FILELISTONLY
/* ********
This block puts the DB in single user mode (killing all open connections) then
does a restore and finally puts the DB back in multi-user mode.
***** */
PRINT 'putting DB in single-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @singleuser_sqlcmd
PRINT 'restoring DB='+@dest_dbname+' from FILE='+@dest_filepath+'... ('+@dbrestore_sqlcmd+')'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @dbrestore_sqlcmd
PRINT 'putting DB back in multi-user mode...'
PRINT CONVERT(varchar(100),GETDATE(),121)
EXECUTE [master].[dbo].sp_executesql @statement = @multiuser_sqlcmd
PRINT 'finished.'
PRINT CONVERT(varchar(100),GETDATE(),121)
END
DROP TABLE #T
GO