automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You ca
n
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or tes
t
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytim
e
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pa
ss
> various parameters to SP to tell where is source, destination, domain, etc
.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
Ramu
If you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>
|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf youâ're using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
nightâ's backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If thatâ's what you want.
John Vandervliet
"Ramu" wrote:
> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

Automated Restore

Hi,

I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".

Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.

I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.

Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.

I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.

I've only used restore via the enterprise manager and I was wondering if anyone here could help.

kind regards,

Mike,You need to create a scheduled job that will execute something like this:

restore database ManagementDBYesterday
from disk = 'backup_location\ManagementDB.bak' with recovery,
move 'ManagementDB_Data' to 'data_location\ManagementDBYesterday_Data.ndf',
move 'ManagementDB_Primary' to 'data_location\ManagementDBYesterday_Primary.mdf',
move 'ManagementDB_Log' to 'log_location\ManagementDBYesterday_Log.ldf'
If you have different logical file names for each database, you may also want to do this for clarity:

alter database ManagementDBYesterday
modify file (name=ManagementDB_Primary, newname=ManagementDBYesterday_Primary)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Data, newname=ManagementDBYesterday_Data)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Log, newname=ManagementDBYesterday_Log)

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIAGrant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the database frequently
> Now, whenever i make any change to my database, i need to regenerate the Sql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Documents every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>