How to modify the instance name of the sqlserver database

sqlserver modify instance name

if serverproperty('servername') &lt;&gt; @@servername 
begin 
	declare @server sysname 
	set @server = @@servername 
	exec sp_dropserver @server = @server 
	set @server = cast(serverproperty('servername') as sysname) 
	exec sp_addserver @server = @server , @local = 'LOCAL' 
END
-- Delete the old server name
EXEC sp_dropserver 'C9FV63M3';
-- Add a new server name
EXEC sp_addserver 'C9FV63M32', 'local';

After modification, restart the server. implement

 SELECT serverproperty('servername') --Server name 
 SELECT @@SERVERNAME   --Database instance name

SQLSERVER Modify instance name and schema information

1. GUI method The latter is to change the name of the instance in the alter database method

2. Create a user under Global Security.

3. Create the schema and user under the security of the instance (the schema name and user name are always used, using the name of the new instance).

4. Use sa to execute the command sp_change_users_login 'Update_one','NEWname','NEWname'

5. Delete the previous old schema name and instance. (If prompted to have a connection, delete it again after completing step 6.)

6. Modify the owner of tables and views in the database instance:

&lt;strong&gt;use newname;&lt;/strong&gt; 
DECLARE @table SYSNAME; 
DECLARE @schema SYSNAME; 
DECLARE @new_schema SYSNAME; 
&lt;strong&gt;SELECT @schema &lt;/strong&gt;&lt;strong&gt;= 'oldname' --Original owner
SELECT @new_schema = 'newname' --&lt;/strong&gt;&lt;strong&gt;New lord&lt;/strong&gt;
DECLARE csr CURSOR FOR  
SELECT ,  FROM  o  
INNER JOIN  u ON o.schema_id = u.schema_id  
WHERE  in( 'u','p','v','tf','fn','if') AND  = @schema  
OPEN csr; 
FETCH NEXT FROM csr INTO @table, @schema;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
exec ('ALTER SCHEMA ' + @new_schema + ' TRANSFER [' + @schema + '].[' + @table + ']'); 
FETCH NEXT FROM csr INTO @table, @schema; 
END  
CLOSE csr  
DEALLOCATE csr ;

Write it yourself and verify it another day.

This is the end of this article about modifying the instance name of SQLserver. For more related contents of modifying the instance name of SQLserver, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!