SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


automate estore database into database with a different name


automate estore database into database with a different name

Author
Message
gary.morey
gary.morey
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 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?
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 3427
Sure, write your code in a sproc and execute the sproc from a SQL Job. Very simple.

Andrew SQLDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223721 Visits: 42006
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8976 Visits: 3718
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’! **
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 3427
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
Summer90
Summer90
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7489 Visits: 3831
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.
gary.morey
gary.morey
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 158
Thank you all for your replies. I have implemented this and it is working great!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223721 Visits: 42006
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search