[dba-SQLServer] Automated Backup and restore

Thanks Robert, that is a very useful piece of code.  Simple enough that even
I can understand it, but complete enough to make it usable as it is.

Still a little tone but you are making progress.  

;-)

I really and truly do appreciate examples like this.  I can read it, I can
understand it, and I can now go to it and see how to do other things that
this code also demonstrates.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert L.
Stewart
Sent: Thursday, March 27, 2008 10:15 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com