Renaming databases with grace, any hint ?

Hi,

I want to implement the following pattern (pseudo-code follows):

[for index in x different databases, where x is rather big]

create database MyDatabase_#{index}_Temp launch a dedicated ssis package on this database if I detect no error, proudly rename MyDatabase_#{index}_Temp to MyDatabase_#{index}_Last_Known_Good (drop the last_known_good first if it exist)

Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.

My question: what is the simpliest way to achieve the rename operation (step 3) ?

I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.

I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.

I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?

best,

Thibaut

The best method for what you describe is probably to DETACH the "last good" and ATTACH the "new" as the old name. Yes, you will need to know the file names.
|||Thanks Tom. Too bad I have to know the file names, this pattern is very useful...

sql