October 7, 2013 at 5:27 am
Hi,
I am using Sql server 2008 R2,
In my Organisation,we daily restore the Database.
We need to give the daily reports from this updated newly restored database.
For this cuurently,after restoring the database,we register(create) all the Stored Procedure,on this server and execute the SP.
My requirement is,once the database is restored, all the SP should be automatically created on the restored database.
Or any other alternative to do this.
Please help me how to do this, this is urgent for me.
Thanks in Advance!!
October 7, 2013 at 5:33 am
Assuming you have the restore job scheduled as a SQL Agent job then why not add an extra step where you call a single stored procedure that contains the code needed to create all the other stored procedures?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 7, 2013 at 5:50 am
No,we don't restore database through sql server agent.
we manulaly restore the database because time of recieving back up is not fixed.
Please suggest me how to do this.
October 7, 2013 at 7:33 am
Don't manually restore the database.
Create a script that restores the database and make the procedures you need part of the script (after the restore of course). You can then schedule the whole thing through SQL Agent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2013 at 7:35 am
Wait, didn't you already ask this question on another forum here at SQL Server Central?
Please don't do that. It leads to multiple answers and confusion across the discussions. As you can see, many of us monitor more than one of the forums on here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2013 at 7:47 am
Grant Fritchey (10/7/2013)
Don't manually restore the database.Create a script that restores the database and make the procedures you need part of the script (after the restore of course). You can then schedule the whole thing through SQL Agent.
I do this all the time. I'll have a backup/copy job that runs on one server to backup a production database. I'll have a job on the development server waiting for the backup to arrive and restore it. In order to avoid a premature start of the restore job I copy the database with a different file extension than what the restore job is looking for. When it finishes the copy I have another step that simply renames the backup to what the restore jobs is looking for.
The restore job can be chock full of neat stuff like adding additional SPs or simply send an email when the restore is finished.
This process, by the way can be automated using an SSIS package as well. There are built in objects that will allow you to backup/copy/restore a database.
The opportunities are endless. Simply pick a path, run with it, make it work, tweak it where you run into difficulties... That's what it is all about.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 7, 2013 at 7:53 am
Kurt W. Zimmerman (10/7/2013)
Grant Fritchey (10/7/2013)
Don't manually restore the database.Create a script that restores the database and make the procedures you need part of the script (after the restore of course). You can then schedule the whole thing through SQL Agent.
I do this all the time. I'll have a backup/copy job that runs on one server to backup a production database. I'll have a job on the development server waiting for the backup to arrive and restore it. In order to avoid a premature start of the restore job I copy the database with a different file extension than what the restore job is looking for. When it finishes the copy I have another step that simply renames the backup to what the restore jobs is looking for.
The restore job can be chock full of neat stuff like adding additional SPs or simply send an email when the restore is finished.
This process, by the way can be automated using an SSIS package as well. There are built in objects that will allow you to backup/copy/restore a database.
The opportunities are endless. Simply pick a path, run with it, make it work, tweak it where you run into difficulties... That's what it is all about.
Kurt
Absolutely. Our biggest thing was having scripts that masked certain production data (such as email addresses) so we could let the developers or testers use the structures and similar data without spamming our client list. And then scripts for setting security, etc., etc.
Automation is the key to all your future life as a DBA.
I even have a presentation I do for Red Gate where I show how to take a backup, restore, clean the data, shrink it, back it up again, remove the database, and then use the smaller, clean, copy of the database for development deployments and deployment automation... ALL through PowerShell.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2013 at 8:05 am
Grant Fritchey (10/7/2013)
Kurt W. Zimmerman (10/7/2013)
Grant Fritchey (10/7/2013)
Don't manually restore the database.Create a script that restores the database and make the procedures you need part of the script (after the restore of course). You can then schedule the whole thing through SQL Agent.
I do this all the time. I'll have a backup/copy job that runs on one server to backup a production database. I'll have a job on the development server waiting for the backup to arrive and restore it. In order to avoid a premature start of the restore job I copy the database with a different file extension than what the restore job is looking for. When it finishes the copy I have another step that simply renames the backup to what the restore jobs is looking for.
The restore job can be chock full of neat stuff like adding additional SPs or simply send an email when the restore is finished.
This process, by the way can be automated using an SSIS package as well. There are built in objects that will allow you to backup/copy/restore a database.
The opportunities are endless. Simply pick a path, run with it, make it work, tweak it where you run into difficulties... That's what it is all about.
Kurt
Absolutely. Our biggest thing was having scripts that masked certain production data (such as email addresses) so we could let the developers or testers use the structures and similar data without spamming our client list. And then scripts for setting security, etc., etc.
Automation is the key to all your future life as a DBA.
I even have a presentation I do for Red Gate where I show how to take a backup, restore, clean the data, shrink it, back it up again, remove the database, and then use the smaller, clean, copy of the database for development deployments and deployment automation... ALL through PowerShell.
I forgot about PowerShell... Yes Grant... So many paths, so little time... 😀
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 7, 2013 at 8:11 am
I strongly agree with grant and kurt here..The more we make our job AUTOMATE..more our life will be easier
AND
there is be LESS chances of "skip/fogret" chances.
Whenever, there is word comes "Repetition" in your plate..start thinkning about "automation". 😛
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply