April 16, 2014 at 2:31 am
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
April 16, 2014 at 2:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2014 at 3:18 am
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?
April 16, 2014 at 3:24 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2014 at 3:56 am
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. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply