Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Database statement - any way to exclude the stored procs? Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 2:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:12 AM
Points: 167, Visits: 868
Hi all,

We are restoring databases in a migration but need to do some repointing of the sprocs (to SSIS packages), however during the restoration that repointing would be lost.

Is there a way to restore without overwriting those sprocs?

Or, is there a way to back up the sprocs only, and restore them after running Restore Database??

Thanks,



Jake
Post #1562172
Posted Wednesday, April 16, 2014 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
No.

A restore over an existing database is equivalent to dropping the old DB and replacing it. It's not a merge, SQL discards the existing DB and completely replaces it with what's in the backup file.

Script the procedures before the restore (management studio, object explorer, right click the DB, tasks, Generate scripts, then work through the wizard



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1562183
Posted Wednesday, April 16, 2014 3:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:12 AM
Points: 167, Visits: 868
Effectively, recreate those sprocs, ok.

We have 30+ (user) databases, is there a script out there that can loop through the databases and generate Create Sproc statements for all of them?
Post #1562195
Posted Wednesday, April 16, 2014 3:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
I suppose you could loop over each database and query sys.sql_modules. But the Generate Scripts option I mentioned is Management Studio functionality, not a SQL command.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1562199
Posted Wednesday, April 16, 2014 3:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:12 AM
Points: 167, Visits: 868
Indeed...it does seem like the smartest way is to script out the new sprocs, Restore the db's, drop the newly restored sprocs, and run those scripts.

Thanks for your help Gail. :)
Post #1562212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse