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
ASKER
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
Try detach the database, then reattach specifying the new file names
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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\
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\
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