Oh well,

Thanks very much none the less Tibor

Ant

Hi greg,

That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:

Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)

ALTER   PROC MyBackupLiveData

@Office NVARCHAR(4),
@SecmanDB NVARCHAR(20),
@ERPEDB NVARCHAR(20),
@ControlDB NVARCHAR(20),
@CompanyDB NVARCHAR(20)

AS

DECLARE @FilePath NVARCHAR(60)
DECLARE @FullPath NVARCHAR(60)
DECLARE @DatePart CHAR(8)
DECLARE @DBName NVARCHAR(30)

-- CREATE DATE TO APPEND TO BACKUP NAME
SET@DatePart = '_' + CONVERT(NVARCHAR,  DATEPART(yyyy,GETDATE()))+
        CONVERT(NVARCHAR,  DATEPART(mm,GETDATE())) +
        CONVERT(NVARCHAR,  DATEPART(dd,GETDATE()))

-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))

-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@SecmanDB)
INSERT #Databases VALUES (@ERPDB)
INSERT #Databases VALUES (@ControlDB)
INSERT #Databases VALUES (@CompanyDB)

-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases

OPEN  TablesCursor

FETCH NEXT FROM TablesCursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- SET PATH & FILENAME OF BACKUP FILE
    SET @FilePath = @Office + '\' + @DBName + @DatePart   + '.bak'

    SET @FullPath = 'C:\Backups\' + @FilePath

    -- PERFORM BACKUP FROM LIVE SERVER
    BACKUP DATABASE @DBName
    TO DISK = @FullPath

    -- PERFORM RESTORE TO TEST SERVER

    SET @FullPath = '\\etestsql\Backups\' + @FilePath

    RESTORE DATABASE @DBName
    FROM DISK = @FullPath

    FETCH NEXT FROM TablesCursor INTO @DBName
END

-- REMOVE ALL OBJECTS

CLOSE TablesCursor
DEALLOCATE TablesCursor

DROP TABLE #Databases

--- Thanks very much for your time on this

Ant