question

Gogolo avatar image
Gogolo asked

Backup only Stored Procedure, or export tham all in txt

Dears, Can I backup only stored procedures from DB, or in any case export tham in one go to test onr any other file format..?
sql-server-2005backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Yup. Your starting point would be to read this: and note the bit about "backwards compatibility". Then check for the appropriate INFORMATION_SCHEMA objects, and a useful article on the main SSC site:
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
If you don't need to automate it, you can use the Script Database As... wizard in SQL Server Management Studio. Just right click on the database in question you'll see the menu choice. The wizard is pretty self-explanatory. @ThomasRushton has you covered on how to use TSQL to make this happen. The other option you have if you need to automate is to look at using SQL Server Management Objects (SMO) with a programming language. You can use it with Powershell very well. That would allow you to automate it if needed. The final option you have is to look at a third party product like [Red Gate SQL Compare][1] (disclosure, I do work for them) or [Red Gate SQL Source Control][2]. These tools can move your procedures out of the database to scripts or to your source control system of choice. They can be automated too. [1]: http://www.red-gate.com/products/sql-development/sql-compare/ [2]: http://www.red-gate.com/products/sql-development/sql-source-control/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I only add, that in this way you are able to backup (script out) only non encrypted stored procedures. The same applies to @ThomasRushton answer.
0 Likes 0 ·
Ah, true. I tend not to think about those things.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.