Home Forums SQL Server 2008 SQL Server Newbies automate estore database into database with a different name RE: automate estore database into database with a different name

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)