Renaming a database

• Digs

  SSCertifiable

  Points: 7333

  More actions

  May 2, 2010 at 4:26 pm

  #221153

  I have a DB named MyProjectDB, the under lying files mdf and ldf has these names as well.

  I want to change the database name and the underying file names, or can COPY, SAVE AS to a new name.

  Or do I have to create a new DB, run a SQL script from MyProjectDB and install this into new DB, then copy and paste data etc over

  Please advise

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  May 2, 2010 at 4:38 pm

  #1161040

  Two ways to do this, either:

  ALTER DATABASE currentname MODIFY NAME = Newname (renames existing db)

  to change file names

  alter database yourdb set offline

  alter database MODIFY FILE ( NAME = logical_file_name, filename= 'path to new file name' )

  physically change the file name

  alter database yourdb set online

  or

  restore database newname from disk = 'backup of existing db'

  and use the with move option to rename the physical files (create a copy of the existing database)

  you don't have to alter the logical file names but if you want to use the following after the above steps

  alter database MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

  ---------------------------------------------------------------------

• Digs

  SSCertifiable

  Points: 7333

  More actions

  May 2, 2010 at 5:04 pm

  #1161044

  I think I got it right...