Get your server issues fixed by our experts for a price starting at just 25 USD/Hour. Click here to register and open a ticket with us now!

Author Topic: Change SQL Server Instance Name  (Read 3914 times)

0 Members and 1 Guest are viewing this topic.

Dony

  • Guest
Change SQL Server Instance Name
« on: March 01, 2015, 02:58:29 pm »
Change SQL Server Instance Name


First get the output of the current instance configuration. You can get the instance name stored in the SQL Server metadata.

Make sure you have backup of all the database if you are changing the production server instance name.

Run the below query to get the current instance name
NOTE: Steps to run a query and take back up are mentioned in the ned of the article
=====
sp_helpserver
select @@servername
=====


You can change the instance name using below query.



Default Instance
====
sp_dropserver 'old_name'
    go
    sp_addserver 'new_name','local'
go
====

Named Instance
====
sp_dropserver 'Server Name\old_Instance_name'
    go
    sp_addserver 'ServerName\New Instance Name','local'
go
====

Verify sql server instance configuration by running below queries

====
sp_helpserver
select @@servername
====

Restart the SQL Server Services.

If the above steps doesnot work, only option is to re-install your SQL server, in which you can set the instance name while installation.

************************************

How to Run a SQL Query

*First, you will want to log into SQL Server Management Studio using Windows Authentication
*Next, you will want to select from the list of databases the one which you are running the query against.Once the database is selected, click on the button in the upper left-hand corner of the window which says, ‘New Query‘.
*A window will then open beside the database list, enabling you to enter your query. Once it has been entered, click the button which says ‘Execute‘ at the top of the window. Management Studio will then run your query against the selected database, and once processed, it will confirm whether or not the query was run successfully.



Backup a Database using Management Studio


*Connect to your database server, using SQL Server Management Studio Express.
*Once connected to database server, browse to  database in the left window pane of Management Studio.
*Right Click on database, and under “Tasks” choose “Back Up"
*A new window will open where we will configure where to save the database backup file.
*Ensure that the “Backup type” is set to “Full” to get a Full backup of your database.
*Click “Add…” to specify where to save the backup file
*A window will pop up, asking you where to save the backup file. Enter a location of your choice.
*Now, we have to make sure that the backup you are creating overwrites all existing backup sets, as appending it to an existing set can cause conflicts when attempting to perform a restore. On the left-hand side of the window, click on ‘Options‘, and then click on ‘Overwrite all existing backup sets‘.
*Once this option is in place, all that is left to do is to run the backup! Click “OK” to begin the database backup.