1

I often restore my Testing database from Production db, but this time I noticed something odd: we have a stored procedure called (USP_AddTS), one of its variable names have slightly a different name in both databases (which I capitalized in example below):

USE [ProductionDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
 (  @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),  
@systemID tinyint,
@RELEASEDATE smalldatetime,
………… 
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@releasedate,….

2)  USE [TestingDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
(   @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),  
@systemID tinyint,
@TSRELEASEDATE smalldatetime,
………… 
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@TSreleasedate,….

We recently added a new column named (TransmittalReleaseDate) to the table (TransmittalSheet), so we updated the (USP_AddTS) to include the new variable (@ReleaseDate) like I’m showing above , but for some reason after I restored my Testing DB from a recent copy from ProductionDB, the variable name got updated to @TSReleaseDate only in Testing DB, unlike the ProductionDB that shows it as @ReleaseDate, is this possible or am I mistaken? There was another variable line from the declared list above that I commented out from the ProductionDB and I made sure it was saved, but from the TestingDB was not commented out and still available.

I will keep an eye when I refresh or restore again, as I may incorrectly chose the prior date’s backup copy to restore from, but I thought of asking here too. I'm using SQL Server 2008R2, Thanks.

1 Answer 1

3

Its not possible for the variable name to change by itself, its likely you restored from a different (older? newer?) backup than what you thought. You can check sys.objects for the stored procedure create/modified dates and compare across the 2 databases.

4
  • Thanks for the great tip, it does have different modified dates between the 2 databases. It must be showing the last modified entry. I wish if there is somewhere that shows how many times it got modified or what is the value that it got modified to. As we changed it manually to the correct variable name couple of times, but its a great start to compare for future ref, thanks again. Jul 14, 2015 at 23:49
  • @Shayma you need a DDL trigger - or to simply take away ALTER PROCEDURE rights if people can't be held accountable to document their changes, intentionally bypass source control, etc. Jul 15, 2015 at 1:03
  • @Aaron such a great and useful post about a DDL trigger, since I can't take away their Alter Procedure rights, I will definitely try it on my Testing DB first as it seems safe to be created on my Production database too. Any recommendation on how our documentation should be standardized between our current 3 developers? We are not using any source control.. thanks again. Jul 15, 2015 at 7:37
  • @ShaymaAhmad, check the default trace as that may help identify when and who made the change: dba.stackexchange.com/questions/10716/…
    – Dan Guzman
    Jul 16, 2015 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.