Post reply

automate estore database into database with a different name

  • May 15, 2013 at 10:48 am

    #394785

    I currently have 2 databases on the same server: MAPA and MAPA2.

    MAPA is being backed up nightly as part of a scheduled maintenance plan in SSMS. I need to be able to have an automated process, that I can schedule, that will take the latest backup file from the MAPA database and restore it into the MAPA2 database.

    Is there a simple way that I can do this?

  • May 15, 2013 at 2:33 pm

    #1615570

    Sure, write your code in a sproc and execute the sproc from a SQL Job. Very simple.

    Andrew SQLDBA

  • May 15, 2013 at 11:23 pm

    #1615612

    AndrewSQLDBA (5/15/2013)

    Sure, write your code in a sproc and execute the sproc from a SQL Job. Very simple.

    Andrew SQLDBA

    If it's so simple, would you post some code to do it?

    --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)

  • May 16, 2013 at 3:47 am

    #1615668

    steps to generate the automated process:

    - manually go througt the RESTORE GUI and generate a script for the complete restore command

    - change the name of the backupfile in the generated RESTORE script to a variable

    - wrap the code in a stored procedure with a required variable for the backupfile

    - query the MSDB backup tables (backupset, backupmediaset, etc.) to get the name of the most recent backup

    - call the stored procedure using the value for the backupfile from the MSDB tables

    - put code from the above step into a nightly job

    It's probably a bit more compex then Andrew mentioned, but it's not that hard either 😉

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • May 16, 2013 at 5:17 am

    #1615696

    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

  • May 17, 2013 at 6:57 am

    #1616155

    This is what I do...

    on prod server create this as a job and schedule:

    BACKUP DATABASE DB1PROD TO DISK = '\\PQASERVER\H$\backups\DB1PROD_db.BAK'

    On test server schedule this as a job some time after the restore completes:

    RESTORE DATABASE [DB1PQA] FROM DISK = N'H:\backups\DB1PROD_db.BAK' WITH FILE = 1, NOUNLOAD, REPLACE

    GO

    use DB2PQA

    alter database DB1PQA set recovery simple

    then reset users for the non-prod db

    then another step, Operating system of this to delete the backup file

    del H:\backups\TMXPROD_db.bak

    For your environment for whatever steps you use normally to restore the db just hit the script button which will script out the DDL required for each step. Then you can put that into steps in the job. Put the different tasks in different steps so if the job fails you can easily see what failed, fix it and restart it from that point forward. I have a handful of restores setup as jobs so if someone wants the db refreshed in a nonprod environment all the steps are in the jobs so I don't accidentally miss something AND it takes only a few seconds to refresh the db.

  • May 17, 2013 at 7:04 am

    #1616164

    Thank you all for your replies. I have implemented this and it is working great!

  • May 17, 2013 at 5:46 pm

    #1616358

    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)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply