How to rename MSSQL DB Permanently After Restore?

I am splitting my database into multiple databases and trying to maintain existing schemas and data.
I need to RESTORE an MSSQL  database 2 or more times and rename it permanently.
I need the names of the physical files to change and during this rename process.
How is this best achieved?
SQL2012
LVL 7
XGISAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
XGISConnect With a Mentor Author Commented:
My solution was to Restore and Rename the database to a new name then.  Then click on files and change the names of all the individual db and log files prior to clicking OK. I now have 2 databases running independently.
0
 
mikeyd234Commented:
There are several ways to do the rename, however to rename the physical database files at operating system level you will have to take the database offline

1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.

2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.

3. You could Backup the database and then restore, changing the file location during the restore process.

4. using T SQL

ALTER DATABASE databaseName SET OFFLINE
GO


ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
GO


--if changing log file name
ALTER DATABASE  databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
GO


ALTER DATABASE databaseName SET ONLINE
GO


Easier though to just take it offline and use windows explorer to browse to the DB mdf files and rename. (option 1)

Thanks
0
 
XGISAuthor Commented:
Hello mikeyd234... thats the same page as I am on.  I will delete it all and try it again from restore.  It wont come back online right now because it is still looking for old paths. etc
0
 
mikeyd234Commented:
Try detach the database, then reattach specifying the new file names
0
 
XGISAuthor Commented:
Hello mikey, pls note I tried attach reattach also.  As soon as it goes live things get complex.   The rename at restore is the quickest and cleanest solution I could identify.  Thankyou for your input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.