Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

automate estore database into database with a different name Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 10:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
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?
Post #1453226
Posted Wednesday, May 15, 2013 2:33 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
Sure, write your code in a sproc and execute the sproc from a SQL Job. Very simple.

Andrew SQLDBA
Post #1453273
Posted Wednesday, May 15, 2013 11:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1453327
Posted Thursday, May 16, 2013 3:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1453392
Posted Thursday, May 16, 2013 5:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:35 PM
Points: 975, Visits: 3,350
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
Post #1453427
Posted Friday, May 17, 2013 6:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 1,269, Visits: 2,781
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.



Post #1453980
Posted Friday, May 17, 2013 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
Thank you all for your replies. I have implemented this and it is working great!
Post #1453990
Posted Friday, May 17, 2013 5:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse