[dba-SQLServer] Automated Backup and restore

For everyone except John (would not want to waste his time)

CREATE PROCEDURE [dbo].[usp_CopyDatabase]
	@strDBNameFrom varchar(100),
	@strDBNameTo varchar(100),
	@strDBDir  varchar(255)
AS
BEGIN
     SET NOCOUNT ON;

     DECLARE @Sql varchar(max)
     SET @Sql = 'BACKUP DATABASE ' + @strDBNameFrom + ' TO DISK = ' + 
@strDBDir + @strDBNameFrom + '.bak WITH INIT'
     EXEC sp_executesql @Sql

     SET @Sql = 'RESTORE FILELISTONLY FROM DISK = ' + @strDBDir + 
@strDBNameFrom + '.bak'
     EXEC sp_executesql @Sql

     SET @Sql = RESTORE DATABASE ' + @strDBNameTo +
         ' FROM DISK = ' + @strDBDir + @strDBNameFrom + '.bak
         WITH MOVE ' + @strDBNameFrom + '_Data' TO  ' + @strDBDir + 
@strDBNameTo + '.mdf,
         MOVE ' + @strDBNameFrom + '_Log TO ' + @strDBDir + 
@strDBNameTo + '.ldf'
     EXEC sp_executesql @Sql
END

I did not lookup the exact syntax. You might meed to enclose the 
dbname and other parts of
the variables in CHAR(39), the single quote.

Robert