Stored procedure lost while migrating to new server

Question

Stored procedure lost while migrating to new server

Ray Cacciatore

Ten Centuries

Points: 1260
More actions
August 24, 2011 at 12:26 pm

#243738

We recently migrated a database from one server to another. I asked the DBA to move the database to a new server that has SQL Server 2008 R2. The old server had SQLServer 2005.
I distinctly remember adding a stored procedure when the database was on the old server. I even had a confirmation by the DBA at the time and by the user as well. The reports that use this stored procedure are even in our file folder.
Yet, this one stored procedure is missing on the new server. Is there something that the DBA might have forgotten to do when migrating the database over to the new server? This is very strange, I am 1000% sure we created it on the old server and even granted execute permission on it. My proof is the written reports.
Has anyone had this happen to them?
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

How was the migration completed, detach/attach or backup/restore? If the later, are you sure the restore was from a current (just before migration) backup?

Lowell SSC Guru Points: 323518 More actions · Answer 2

maybe the stored procedure is in the master or msdb database, and thus was not migrated when that one database moved over?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ray Cacciatore Ten Centuries Points: 1260 More actions · Answer 3

Actually, I learned from the DBA today that the stored procedure really was copied over to the new server. But what the DBA told me is that the EXECUTE permission on the stored procedure was not there on the new server. The stored procedure was created in June 2010. It was working fine on the old server until the db was migrated over to the new server. Now we discovered that the EXECUTE permission was never transfered over. Unfortunately, the users never noticed because this stored procedure is used once a year (year-end procedure).