Backing up database objects

Question

Post reply

Backing up database objects

Felicitywie

Right there with Babe

Points: 717
More actions
September 15, 2010 at 12:57 am

#238233

I have just recently started working at a new organisation. One of their requests is for me to ensure that backups are made of their Dev, QA, and User Testing environments.
I do not require that any data is backed up, only the objects like tables, and stored procedures.
I could possibly script out the database on a regular basis, but I would need to automate it, so was hoping for a better suggestion.
😀
Any ideas?
Thanks
Feli

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

SMO should do the trick.

Every database object has a SMO counterpart that can create a script.

Tables apart, database objects that have a text definition (procedures, views, triggers etc.) can be scripted out with object_definition(object_id).

Hope this helps.

Gianluca

-- Gianluca Sartori

Toby White SSCertifiable Points: 6187 More actions · Answer 2

There may not be anything you can do about it, but it sounds like a bad requirement. It puts the responsibility on you for other people's code. The test and development databases shouldn't be a repository for code. They should know what they have deployed to these environments because they have scripts in Source Safe, subversion, or some other repository. How else will they know what they need to ultimately deploy to production? I hope the answer is not that they do a diff between dev/test and production.

I suggest that you do compressed backups of production, and tell them that they shouldn't do backups of dev/test because all that can be recreated because the devs/testers are saving their build scripts. Otherwise you are going to have to jump through hoops for an inelegant solution using SMO, as Gianluca suggested, Redgate, or some other third party vendor.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

100% agreed.

It's also true that if this is one of the first tasks you have been assigned on your new job, you'd better first of all do as required, then suggest a better alternative solution.

Just my 2 cents, anyway.

-- Gianluca Sartori

Felicitywie Right there with Babe Points: 717 More actions · Answer 4

Thanks for the responses, I'll admit, when I was given the requirement I also thought its an odd requirement, but had to put some research into it, so that I had some suggestions before completely shutting the request down.

I have suggested that they store their coding in Source Safe (as it is already available on all those environments), so that at any stage they can go back to older versions of their code.

In the event of a failure of the servers, we canrecreate those environments with the latest production backup. I think the developers need to ensure backup of their own code if they do not use a central repository.

Thanks again!

Toby White SSCertifiable Points: 6187 More actions · Answer 5

Excellent - and kudos to you for "standing up" to the developers Felicitywie. This is a good precedent to set in your new position. There is a delacate balance between being the "jerk road block" DBA and a responsible steward of your companies data. What you did is on the responsible side and helps the developers to be responsible and accountable.