AndrewSQLDBA (5/16/2013)
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
Thanks, Andrew.
--Jeff Moden
Change is inevitable... Change for the better is not.