This is the code that I use. I have a DatabaseMaintenance Database that all my maintenance code is stored and runs form

------------ Backup Database ----------

BACKUP DATABASE [<DatabaseName>]

TO DISK = N'<Full Path to where you want you backup to reside>.bak'

WITH COPY_ONLY

, NOFORMAT

, INIT

, NAME = N'<Some Name>'

, SKIP

, NOREWIND

, NOUNLOAD

, STATS = 10

, CHECKSUM;

DECLARE @backupSetId AS INT;

select @backupSetId = position

from msdb..backupset

where database_name=N'<Database Name>' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'<Database Name>' )

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''<Database Name>'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'<Full Path to where you want you backup to reside>.bak'

WITH FILE = @backupSetId

, NOUNLOAD

, NOREWIND;

------------ Restore the Database from a Backup ----------

ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [<DatabaseName>]

FROM DISK = N'<Full Path to where you want you backup to reside>.bak'

WITH FILE = 1,

MOVE N'<Database Data File Name>'

TO N'<Path of the Original Database Data File Name>.mdf',

MOVE N'<Database Log File Name>'

TO N'<Path of the Original Database Log File Name>.ldf',

NOUNLOAD,

REPLACE,

STATS = 10;

ALTER DATABASE <DatabaseName> SET Multi_User;

That is what I use to backup a database and then restore that database backup over an existing database of a different name. I also use this same code for when I have a database to restore on a different server, I use a SSIS package, but still call the same stored procedures.

Andrew SQLDBA