CODEWITHSUNDEEP
sqlsql-serverbackupssms
user1000591
user1000591

Reputation: 21

Management Studio Backup SPs / UDFs

Just wanted to know if there was an easy way to backup Stored Procedures and / or User Defined Functions ?

As a developer, one would usually want to retain existing versions of various database objects on production ( i.e. objects like tables / views / triggers / SPs / UDFs / anything MS manages ) so as to be able to revert to the most recent state of the database in case the situation for a rollback were to arise.

We know a backup of a db would fit the bill, but would be an overkill if the change was simply 1 SP.

At present, the process is manual and therefore time-consuming and also prone to human error, for a mundane task / should really have been automated.

Am asking if it were somehow possible ( or at least if it is in MS's dev pipeline ) to set a server state so it knows to "backup" anything that is altered. Therefore, every db object would then have an "archive" or "older versions" folder that one could use to browse the object's X most recent versions.

Upvotes: 2

Views: 55

Answers (4)

Ben Thul
Ben Thul

Reputation: 32707

One solution that I've seen employed is that you rename the existing object with sp_rename and then deploy your new one. so, if you have sp_foobar in your database and you want to deploy a new version of it, you'd rename the existing one to sp_foobar_20111017012345 and then deploy your new one. If you need to revert to the old version (or any previous version), you'd do a select name from sys.objects where name like 'sp_foobar%', find the right one, drop sp_foobar, and use sp_rename to rename the appropriate one back to sp_foobar.

Upvotes: 0

devio
devio

Reputation: 37215

I wrote a command-line utility called SMOscript which scripts all database object definitions to file.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161781

I don't know if you were aware of it, but you can use source control with SSMS.

Better yet, see Working with Database Projects. Database Projects in Visual Studio 2010 bring database developers many of the features that "code" developers have had since forever, including source control and automated deployment.

Upvotes: 2

bako_mite
bako_mite

Reputation: 24

In Object explorer go to the desired Database. Right mouse click to open the menu. Select option Tasks>>generate scripts. The wizard will walk you through the process. It is sometimes tricky, so play around with it. Good luck.

Upvotes: 0

Related Questions