how to test before putting in production

Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.

How can I do modifications without affecting the production server and the users ?
thanks.

The simplest solution is to have a local copy of your database and test against that. You can download the MSDE from Microsoft for free.

A more complicated solution would be to create the test SPs in a different schema from dbo, and reference them that way. (I've had good luck doing that with Oracle using synonyms. Everything was referenced via synonym, and if a developer wanted to override the object, he'd create a new one with the same name as the synonym in his own schema. I'm not sure how well this would work in SQL Server.)

|||Thanks for your reply. As you said the first solution seems easier.

I still have a few questions if you don't mind:
When I download MSDE, do they have utilities to copy my whole database from SQL server to MSDE ?|||

I use enterprise manager to back up the production DB, and then restore it on MSDE.

Yes. Just change the connection string to point to the appropriate db. It you have it in your web.config, it should be easy to do.

You can do a simple copy/paste. I use SqlDiff to copy schema/procedure changes from one database to another.

|||thank you , seems good i'll try that.