Strange Sql Server 2005 Behavior
Background:
I have a site built in ASP.NET with Sql Server 2005 as a database. The site is the only site on Windows Server 2003 that is in my client server room. The client is the local school district, so there is no remote desktop access and remote Sql server connection for data security, so if I need to serve the database I have to be on the terminal. I have FTP access to update my ASP code.
Problem:
Yesterday, I contacted a system issue. When I looked at it, it seems like the bug I solved almost a year ago has returned. I have a stored procedure that used an int value as a parameter, but a year ago we changed the system structure and updated the stored procedure to take nvarchar (10). The stored procedure has somehow changed to use int instead of nvarchar.
There is an external hard drive connected to the server that backs up data periodically and has the ability to recover the server in case of a failure. I would assume that somehow an older version of the database was restored, but the data that I know was inserted 7 days and 1 day before the error occurred is still in the database ...
Q:
Is there anyway the structure of the Sql Server 2005 database can be reverted to a previous version or restored to a previous version without touching the actual data? Nobody else should be able to access the server, so I'm a little crazy trying to figure out how this happened.
Any ideas?
a source to share
With the built-in SQL Server backup and restore mechanism, there is no facility to select only specific objects to restore. With transaction log backups, you can restore to a point in time, which may be before a specific transaction or ALTER statement, but the closest you will get. There is a tool that will allow you to select certain objects to restore, but they work either by restoring the database to a copy and copy over the objects you want, or directly to read the backup and copy those objects. In other words, it couldn't have happened by accident with the built-in tools. I am guessing that someone accidentally ran an old script stored by proc (s) that returned it.
a source to share
It would be trivial to modify the stored procedure without touching any data or any other stored procedure. How who, why, when the problem.
One sentence, run
select * from sys.procedures
and check the create_date and modify_date columns for both your problematic procedure and all other procedures in the database.
a source to share
I've seen similar things happen with an app that I installed in the same client location. Each time s'procs falls back to an older version.
This is just one client, the app is installed on a few others that never had this problem, and they are also the school district. This happens about once every 3 months or so, and no one should touch this machine. I'm not even sure if they have anyone in the house who knows how to open a business manager.
Out of curiosity, what kind of backup software is your client using? and, after checking the creation / modification dates of the procedures, did the server restart occur around this time?
I ask that my client has backup software that does some really weird things on this server. For example, on reboot, it should "replay" changes, including file operations, from the last successful backup. Also, is it installed in the VM?
a source to share