I'm on a mission!
I don't know how you manage your database migration from Dev to Live, but here's our usual process:
- Set up a Dev database and add the developer to the db_owner role
- Let them mess about with Dev until they're happy with it (or until they're told off for doing stupid things, like not using synonyms for linked servers)
- Wait until they're happy with Dev
- Back up Dev (or detatch it, depending on how I feel) and restore it on the new server with a shiny, new _LIVE name
- Empty any tables with test data
- Set up the connections and maintenance plan
You'll notice there's a step missing in there. There should be another bit called:
- Alter database settings with the old _DEV to new _LIVE suffix
Now, there's not that much to do in this step normally, unless your developer has been sticking arbitrary DEVs into their SP and table names. If they're nice and considerate though, there's usually nothing to change. Nothing but one thing. A thing that really bugs me that I used to forget:
LOGICAL FILE NAMES!
It really annoys me now when I find a live database whose logical file names are DATABASENAME_DEV. It's petty I know, but if you have a decent naming scheme you should follow it!
So what to do about this travesty? Welp, it's really easy to change a logical file name. You don't even need to take the database offline. Just run the following:
ALTER DATABASE [Databasename] MODIFY FILE (NAME= Databasename_data_dev, NEWNAME= Databasename_data_live) GO ALTER DATABASE [Databasename] MODIFY FILE (NAME= Databasename_log_dev, NEWNAME= Databasename_log_live) GO
Just get on with it!
Name your logical files right!
DON'T MAKE ME HURT YOU!