Automatically copy entire database to Dev server

[akalinowski]

Our dev staff has asked me to automate the copy of the live db to the dev db and i am not sure how to do this.
i currently just backup the db and restore manually once or twice a week but they want a fresh new copy every day so their projects mirror live data as close as possible...

i know there has to be a way to do it but i'm not asking google the right questing.
anyone have any insight?
thanks

akalinowski

 

[dhulbert]

Set up a nightly back up of the live database then schedule a restore on the dev server.

Just make sure you schedule the restore to happen after the backup finishes.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[ousoonerjoe]

oooh... doesn't the restore blow out any objects that the developers are creating? I think the best question here is, What's the best way to port the production data over without affecting any of the structures (tables, stored procedures, views, triggers, etc)? I'm currently trying to find a good methodology for this as well.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[SQLBill]

Your question was about copying the live database to dev. You stated you are currently manually backing up the database and restoring it.

That overwrites all the existing database.

dhulbert gave you a solution to the issue you posed...now you say that's not what you want to do as it will overwrite everything.

So..........what do you really need to do?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[akalinowski]

no that solves my problem
ousoonerjoe is having a similair problem.
but he raises a good questsion i need to pose to my devs
but my original question is answered.

akalinowski

 

[dhulbert]

ousoonerjoe

there is no simple way round this issue, if you ue back up and restore then you will overwrite the destiation directory,

If you grab data from live and pull it to dev you will have failures when the developers change tables, columns, data types etc.

A resosonably tidy solutions is to backup and restore live onto a second database on your dev server, seperate to the development area. That way the developers can grab the data as they require with no impact on the live system.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[ousoonerjoe]

Yeah... that's what I thought. I haven't found any real good way to do this. I thought about creating a job, but just like you mentioned, a changed table can blow up the job. The only solution I have come up with is to make a copy of Production, then use Import/Export to migrate the data into the Dev Database on an "as needed" basis.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[SQLBill]

Sorry about missing that it was two different posters asking questions.

Our developers keep a 'store' of their changes that have not been promoted to Production. When we refresh development with a backup from production, they reapply the necessary changes from their 'code store'.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875