Sometimes we need to move the database from one instance to another instance. But we don’t dare to erase our old database right away. We keep the old database as a backup in the old instance for a while. It is a good way to use the old database if there is a problem in the new instance. But there is a risk that the application will accidentally go to the old database. Even if you disable the login on the old instance, you need to make sure that the old database cannot be used. You can disable all the logins that use the application on the instance where the old database is, but sometimes this can be difficult.
In such a case,
- You can set the database To Read Only Mode.
- You can detach the database.
- I prefer to take database offline and in my opinion it is the safest way.
Method 1: You can use the following script to change the database name and set the database To Read Only Mode.
|
1 2 3 4 5 6 7 |
ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC sp_renamedb 'AdventureWorks2014','AdventureWorks2014New' GO ALTER DATABASE [AdventureWorks2014New] SET READ_ONLY WITH NO_WAIT GO ALTER DATABASE [AdventureWorks2014New] SET MULTI_USER WITH ROLLBACK IMMEDIATE |
Method 2: To detach the database, you can right-click on the database and click Tasks-> Detach on SSMS.
But before detach the database, you must learn the paths of the database files with the following query and save this information anywhere in order to be able to attach it.
|
1 |
select * from sys.master_files where database_id=DB_ID('AdventureWorks2014') |
You can learn more about detach-attach from the below articles.
“How To Change The Disk Of Database Files On SQL Server(Detach Attach & Backup Restore Methods)“,
“How To Detach and Attach a Database Containing FileStream Data”
Method 3: You can take the database offline. You can perform this operation with the help of the following script. I’m using this method.
|
1 |
ALTER DATABASE [AdventureWorks2014] SET OFFLINE WITH ROLLBACK IMMEDIATE |
If you want to take it online again, you can use the following script.
|
1 |
ALTER DATABASE [AdventureWorks2014] SET ONLINE |
