Backup Programmability in SQL Server 2005

Tags:
Backup
SQL Server 2005
SQL Server backup
How can I backup the Programmability (functions, triggers stored procedures etc.) as part of a schedule job? Do I need to write code or can I use the wizard?

Answer Wiki

Thanks. We'll let you know when a new response is added.

These are backed up when you backup the database itself.

If you wanted to write something to script out the procedures, functions, triggers, etc and save them somewhere you would need to use the sp_helptext procedure within a cursor which gets the object names from the sys.procedures, sys.triggers, and sys.functions DMVs.

declare @name as sysname
declare cur CURSOR for SELECT name FROM sys.procedures
open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0
BEGIN
      exec sp_helptext @name
      print 'GO'
      fetch next from cur into @name
END
close cur
deallocate cur

The above code will print all the code on the screen. If you put this into a job step, and setup the advanced properties of the job step to log the output to a text file, you’ll get all the stored procedure T/SQL into a single file. This can be replicated for triggers, functions, etc.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,075 pointsBadges:
    report
  • IT Systems Analyst
    Thank you very much for the help. You are very kind and helpful. Tzach.
    15 pointsBadges:
    report
  • Denny Cherry
    No problem. I'm happy to help.
    66,075 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following