Renaming Database

• bruceShining

  Grasshopper

  Points: 17

  More actions

  October 24, 2003 at 6:25 am

  #58866

  I want to rename a database and also rename the .mdf and .ldf files so I can insert a new database with same name as oldDatabaseName.

  Suggestions?

  I have read forums and found a nice way to rename a database like:

  --(when DB is known to be idle)....

  EXEC sp_dboption @OldDatabaseName, 'single user', 'TRUE'

  EXEC sp_renamedb @OldDatabaseName, @NewDatabaseName

  EXEC sp_dboption @NewDatabaseName, 'single user', 'FALSE'

  But there is a problem...

  What I want to do now is restore an updated schema version (newer data also) of the same database with the oldDatabaseName. When I attempt, it fails with msg that the oldDatabaseName.mdf file exists.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  October 24, 2003 at 7:25 am

  #479458

  Detach the database (sp_detach_db), rename the files, and then attach the database (sp_attach_db) using the new filenames.

  Or you could leave the filenames on the old database alone and do a RESTORE DATABASE...WITH MOVE of the backup.

  --Jonathan

  
  
  --Jonathan

• simon_hyan

  Ten Centuries

  Points: 1177

  More actions

  October 24, 2003 at 8:45 am

  #479494

  if u want to compare the current DB and the DB which you will create by the updated schema version, you can fellow these steps:

  1> Dump current DB, then drop it.

  2> Create new DB by the updated schema version, the DB has the same name as before and new data

  3> Restore the dump with the different DB's name and files' names