CODEWITHSUNDEEP
sqlsql-serversql-server-2005stored-procedures
Neo
Neo

Reputation: 16239

How can I restore my Stored Procedures again into dababase at once?

I had done generate script and take out my 100+ stored procedures into a separate 100 files like we take backup now i made changes into those files. but now i need to alter all those original stored procedures from database. i mean to say now all those 100 files i need to again load into database.

How can I do this like I had generate script to take out those sp's from database is there any way to restore all new sp's into same database at once?

please help.

Upvotes: 0

Views: 8112

Answers (4)

Mosmodify
Mosmodify

Reputation: 1

Please use this script for execute in your database...

Select 
    Convert(varchar(Max), Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) as [Script] 
from 
    fn_dblog(NULL, NULL)

You will see script stored procedure are dropped.

Good luck :)

Upvotes: 0

Carol Baker West
Carol Baker West

Reputation: 259

ApexSQL Build does exactly what you need. It executes SQL scripts against new or existing databases, and you can specify to drop the object first, so there will be no conflicts when executing CREATE PROCEDURE statements You can create T-SQL, C# or exe

enter image description here enter image description here

Upvotes: 0

Kangkan
Kangkan

Reputation: 15571

If you wish to take out the scripts of stored procedures, you can take out them using "Generate Script" you shall get CREATE scripts. Once you change the scripts' body, you can rerun the scripts on the DB. But before that you can either drop the SP in the database or change the CREATE statement to ALTER in the script file.

One more way is to generate ALTER script when you take out the script. But this works with one proc at a time. You can right-click the SP and select "Script Stored Procedure as -> ALTER to -> File..." from the context menu.

Upvotes: 2

Raptor
Raptor

Reputation: 54258

You can backup your database stored procedures into a file, and restore them to your new database server using Backup and Restore tool.

Upvotes: 0

Related Questions