• 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