August 26

MSSQL: Find specific data in any table of a database

MSSQL: Find specific data in any table of a database

 

NOTE:  RUN USE MASTER INDEPENDENTLY OF REMAINDER OF QUERY.  This query runs slowing and takes a lot of server resource.

USE MASTER

CREATE PROCEDURE SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

By:  Narayana Vyas Kondreddi.

— Purpose: To search all columns of all tables for a given search string

 

Tested procedure:

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 

SET NOCOUNT ON

 

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

 

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ”

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

FROM        INFORMATION_SCHEMA.TABLES

WHERE                     TABLE_TYPE = ‘BASE TABLE’

AND          QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

AND          OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

), ‘IsMSShipped’

) = 0

)

 

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM        INFORMATION_SCHEMA.COLUMNS

WHERE                     TABLE_SCHEMA           = PARSENAME(@TableName, 2)

AND          TABLE_NAME              = PARSENAME(@TableName, 1)

AND          DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)

AND          QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

)

END

END

END

 

SELECT ColumnName, ColumnValue FROM #Results

END

 

 

NEXT RUN

 

 

Use names

 

EXEC SearchAllTables ‘nighthawk%’
GO

Category: Databases | Comments Off on MSSQL: Find specific data in any table of a database
August 26

MSSQL: Restoring a DB

MSSQL Restoring a DB

 

Sometimes this requires setting the DB into Single User mode.
Open SQL Query Analyzer

Single User: alter database db-name set SINGLE_USER
Multi User: alter database db-name set MULTI_USER

If the database will not restore do to someone being in it.
Goto Management – Current Activity – Process Info
Sort by BD name
Highlite the user that is locking you out – Right click and Kill Process

Category: Databases | Comments Off on MSSQL: Restoring a DB
August 26

SQLite: Command Line Shell For SQLite

Command Line Shell For SQLite

The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use the sqlite3 program.
Getting Started

To start the sqlite3 program, just type “sqlite3” followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite3 program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press “Enter” and the SQL will be executed.

For example, to create a new SQLite database named “ex1” with a single table named “tbl1”, you might do this:

$ sqlite3 ex1
SQLite version 3.6.11
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values(‘hello!’,10);
sqlite> insert into tbl1 values(‘goodbye’, 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

You can terminate the sqlite3 program by typing your systems End-Of-File character (usually a Control-D). Use the interrupt character (usually a Control-C) to stop a long-running SQL statement.

Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:

sqlite> CREATE TABLE tbl2 (
…>   f1 varchar(30) primary key,
…>   f2 text,
…>   f3 real
…> );
sqlite>

Aside: Querying the SQLITE_MASTER table

The database schema in an SQLite database is stored in a special table named “sqlite_master”. You can execute “SELECT” statements against the special sqlite_master table just like any other table in an SQLite database. For example:

$ sqlite3 ex1
SQLite vresion 3.6.11
Enter “.help” for instructions
sqlite> select * from sqlite_master;
type = table
name = tbl1
tbl_name = tbl1
rootpage = 3
sql = create table tbl1(one varchar(10), two smallint)
sqlite>

But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.

The schema for TEMPORARY tables is not stored in the “sqlite_master” table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named “sqlite_temp_master”. The “sqlite_temp_master” table is temporary itself.
Special commands to sqlite3

Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot (“.”), then that line is intercepted and interpreted by the sqlite3 program itself. These “dot commands” are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter “.help” at any time. For example:

sqlite> .help
.backup ?DB? FILE      Backup DB (default “main”) to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? …      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS?     Options are:
–no-drop: Do not drop old fkey triggers.
–ignore-errors: Ignore tables with fkey errors
–exec: Execute generated SQL immediately
See file tool/genfkey.README in the source
distribution for further information.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.iotrace FILE          Enable I/O diagnostic logging to FILE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
csv      Comma-separated values
column   Left-aligned columns.  (See .width)
html     HTML <table> code
insert   SQL insert statements for TABLE
line     One value per line
list     Values delimited by .separator string
tabs     Tab-separated values
tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default “main”) from FILE
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM …     Set column widths for “column” mode
sqlite>

Changing Output Formats

The sqlite3 program is able to show the results of a query in eight different formats: “csv”, “column”, “html”, “insert”, “line”, “list”, “tabs”, and “tcl”. You can use the “.mode” dot command to switch between these output formats.

The default output mode is “list”. In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol (“|”). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.

sqlite> .mode list
sqlite> select * from tbl1;
hello|10
goodbye|20
sqlite>

You can use the “.separator” dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:

sqlite> .separator “, ”
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

In “line” mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:

sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10

one = goodbye
two = 20
sqlite>

In column mode, each record is shown on a separate line with the data aligned in columns. For example:

sqlite> .mode column
sqlite> select * from tbl1;
one         two
———-  ———-
hello       10
goodbye     20
sqlite>

By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the “.width” command. Like this:

sqlite> .width 12 6
sqlite> select * from tbl1;
one           two
————  ——
hello         10
goodbye       20
sqlite>

The “.width” command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to “.width” as necessary to specify the widths of as many columns as are in your query results.

If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.

The column labels that appear on the first two lines of output can be turned on and off using the “.header” dot command. In the examples above, the column labels are on. To turn them off you could do this:

sqlite> .header off
sqlite> select * from tbl1;
hello         10
goodbye       20
sqlite>

Another useful output mode is “insert”. In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.

When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:

sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO ‘new_table’ VALUES(‘hello’,10);
INSERT INTO ‘new_table’ VALUES(‘goodbye’,20);
sqlite>

The last output mode is “html”. In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.
Writing results to a file

By default, sqlite3 sends query results to standard output. You can change this using the “.output” command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use “.output stdout” to begin writing to standard output again. For example:

sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$

Querying the database schema

The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.

For example, to see a list of the tables in the database, you can enter “.tables”.

sqlite> .tables
tbl1
tbl2
sqlite>

The “.tables” command is similar to setting list mode then executing the following query:

SELECT name FROM sqlite_master
WHERE type IN (‘table’,’view’) AND name NOT LIKE ‘sqlite_%’
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN (‘table’,’view’)
ORDER BY 1

In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you’ll find exactly the above query.

The “.indices” command works in a similar way to list all of the indices for a particular table. The “.indices” command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the “.schema” command. With no arguments, the “.schema” command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to “.schema”, it shows the original CREATE statement used to make that table and all if its indices. We have:

sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
)
sqlite>

The “.schema” command accomplishes the same thing as setting list mode, then entering the following query:

SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type!=’meta’
ORDER BY tbl_name, type DESC, name

Or, if you give an argument to “.schema” because you only want the schema for a single table, the query looks like this:

SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type!=’meta’ AND sql NOT NULL AND name NOT LIKE ‘sqlite_%’
ORDER BY substr(type,2,1), name

You can supply an argument to the .schema command. If you do, the query looks like this:

SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE ‘%s’
AND type!=’meta’ AND sql NOT NULL AND name NOT LIKE ‘sqlite_%’
ORDER BY substr(type,2,1), name

The “%s” in the query is replace by your argument. This allows you to view the schema for some subset of the database.

sqlite> .schema %abc%

Along these same lines, the “.table” command also accepts a pattern as its first argument. If you give an argument to the .table command, a “%” is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.

The “.databases” command shows a list of all databases open in the current connection. There will always be at least 2. The first one is “main”, the original database opened. The second is “temp”, the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.

sqlite> .databases

Converting An Entire Database To An ASCII Text File

Use the “.dump” command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.

A good way to make an archival copy of a database is this:

$ echo ‘.dump’ | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

$ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2

Other Dot Commands

The “.explain” dot command can be used to set the output mode to “column” and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:

sqlite> .explain
sqlite> explain delete from tbl1 where two<20;
addr  opcode        p1     p2     p3
—-  ————  —–  —–  ————————————-
0     ListOpen      0      0
1     Open          0      1      tbl1
2     Next          0      9
3     Field         0      1
4     Integer       20     0
5     Ge            0      2
6     Key           0      0
7     ListWrite     0      0
8     Goto          0      2
9     Noop          0      0
10    ListRewind    0      0
11    ListRead      0      14
12    Delete        0      0
13    Goto          0      11
14    ListClose     0      0

The “.timeout” command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.

And finally, we mention the “.exit” command which causes the sqlite3 program to exit.
Using sqlite3 in a shell script

One way to use sqlite3 in a shell script is to use “echo” or “cat” to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to use in conjunction with programs like “awk”. For example:

$ sqlite3 ex1 ‘select * from tbl1’ |
>  awk ‘{printf “<tr><td>%s<td>%sn”,$1,$2 }’
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$

Ending shell commands

SQLite commands are normally terminated by a semicolon. In the shell you can also use the word “GO” (case-insensitive) or a slash character “/” on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won’t work in sqlite3_exec(), because the shell translates these into a semicolon before passing them to that function.
Compiling the sqlite3 program from sources

The source code to the sqlite3 command line interface is in a single file named “shell.c” which you can download from the SQLite website. Compile this file (together with the sqlite3 library source code to generate the executable. For example:

gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread

Category: Databases | Comments Off on SQLite: Command Line Shell For SQLite
August 26

MYSQL: How To Mysql Master Slave Resync

How To: Mysql Master Slave Resync

If you are using the nice master-slave replication feature of MySQL (available since early version 3.x if I remember well), it may happens that the slave may get out of sync for various reasons.
One typical error is like this:
090908 12:58:48 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
090908 12:58:48 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
this error can happen for example on the slave if the master crashes and the binlog isn’t updated correctly in the fsck recovery.

So here is a quick description of how to resynchronize the slave in a single master single slave configuration.

This procedure involves a stop of the slave server, while the master will have the tables in lock mode for the time of taking a snapshot/export of your databases to be copied on the slave.

Step 1. On the Slave
Issue the following commands to mysql:
STOP SLAVE; # stop the Slave I/O threads
RESET SLAVE; # forget about all the relay log files
/etc/init.d/mysql stop # stop the database

Step 2. On the Master
Issue the following commands to mysql:
mysql> RESET MASTER; # reset the bin log counter and wipe out bin log files
mysql> FLUSH TABLES WITH READ LOCK; # flush buffers and LOCK tables (both MyISAM and InnoDB)
mysql> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql>

Plaase take note of the file (mysql-bin.000001) and the position (98). You’ll need it later on the slave.

Now, don’t exit from the current mysql client, since you will release the lock, and we don’t want this. Take another shell and do the following:
– make a copy (gzipped tar for example) of the datafiles of MyISAM databases
– make an export of the InnoDB databases (via mysqldump –databases –lock-all-tables DBNAME1 [DBNAME2…] > dumpfile.sql ).
To reduce the down time of the master database, If you don’t have InnoDB databases and you are using LVM on the MySQL volume, you can for example take a snapshot instead of copying the datafiles (take the snapshot and then UNLOCK TABLES;).

Go back to the mysql client where you issued the “FLUSH TABLES WITH READ LOCK;” and type “UNLOCK TABLES;” or logout from the client.
Now the master is available again to mysql clients.

Step 3. Go back on the Slave
Verify that the slave mysql is STOPPED.
Copy the dumps and the archives of the databases you made in previous steps on the master to the slave.
Replace the datafiles of the MyISAM databases you got from the master. These steps may vary and can be done in different ways (scp of the datafiles, mysqldump from the slave connecting to the master and so on… that’s your choice :-) .
Edit /etc/my.cnf and insert “skip-slave-start” in the mysqld section of the file to avoid the start of the slave I/O threads.
Start the mysql instance on the slave. Delete the InnoDB databases and import them from the SQL dumps.
Now issue the following to mysql using the right parameters taken from SHOW MASTER STATUSG above:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> SLAVE START;

Verify that the the slave connects to the master and that is getting the binlog.
mysql> show slave statusG
....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
Seconds_Behind_Master: 1634

Slave_IO_Running tells you that the slave is connected to the master, while Slave_SQL_Running says you that the slave is applying the binlog and updating the local datafiles. If they are both set to “Yes” all is fine, replication is working.
Seconds_Behind_Master is the “lag” in seconds from the slave to the master, and should decrease to 0 in a short time (depending also on the activity on the master from the UNLOCK TABLES instant).

Don’t forget to comment out the “skip-slave-start” in /etc/my.cnf so that next time mysql restart on the slave the I/O threads are automatically started.

That’s all folks. Beware that the “Seconds_Behind_Master” status counter is completely unreliable.

If you want to precisely monitor the lag between the master and the slave or better to periodically check the right sync status of the slave with the master, I’d suggest to use the utility from the Maatkit package, which is a must for a MySQL administrator.

Please refer to the MySQL Reference Manual about replication for more details.

To purge old binlog files I use this script running on the slave from root’s crontab once a week:

#!/bin/bash
CURRENT_LOGFILE=$(/usr/bin/mysql -e "SHOW SLAVE STATUSG" | awk '$1 == "Master_Log_File:" {print $2}')
/usr/bin/mysql -h MASTER -e "PURGE MASTER LOGS TO '${CURRENT_LOGFILE}'"
exit $?

((enjoy))

Category: Databases | Comments Off on MYSQL: How To Mysql Master Slave Resync
August 26

MSSQL: How to rename an Instance of MSSQL 2005

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server 2005

SQL Server 2005
Updated: 15 September 2007When you change the name of the computer that is running Microsoft SQL Server 2005, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. The following steps cannot be used to rename an instance of SQL Server 2005. These steps can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance portion of the name, Instance1, will remain unchanged. In this example, the \ComputerNameInstanceName would be changed from \MB1Instance1 to \MB2Instance1.

Before you begin

Before you begin the renaming process, review the following information:

  • When an instance of SQL Server is part of a SQL Server failover cluster, the process of renaming the computer differs from the process of renaming a computer that hosts a stand-alone instance. For information about renaming a computer that hosts a failover cluster instance of SQL Server 2005, see How to: Rename a SQL Server 2005 Virtual Server.
  • SQL Server does not support renaming computers that are involved in replication, except in the case of using log shipping with replication. The secondary computer in log shipping can be renamed if the primary computer is permanently lost. For more information, see Replication and Log Shipping.
  • When you rename a computer that is configured to use Reporting Services, Reporting Services might not be available after the computer name change. For more information, see Renaming a Report Server Computer.
  • When renaming a computer that is configured to use database mirroring, you must turn off database mirroring before the renaming operation, and then re-establish database mirroring with the new computer name. Metadata for database mirroring will not be updated automatically to reflect the new computer name.
  • After the computer renaming operation, users who connect to SQL Server through a Windows group that uses a hard-coded reference to the computer name will not be able to connect to SQL Server if the Windows group specifies the old computer name. To ensure that such Windows groups have SQL Server connectivity following the renaming operation, update the Windows group to specify the new computer name.

You can connect to SQL Server using the new computer name after you have restarted SQL Server. However, to ensure that @@servername returns the updated name of the local server instance, you should manually run one of the following procedures, depending on whether you are updating a default or named instance.

  • For a renamed default instance, run the following procedures: 

    sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

    Restart the SQL Server instance.

  • For a renamed named instance, run the following procedures: 

    sp_dropserver <old_nameinstancename>
GO
sp_addserver <new_nameinstancename>, local
GO

    Restart the SQL Server instance.

After a computer has been renamed, any connections that used the old computer name must connect using the new name.

  • Select information from either @@servername or sys.servers. The @@servername function will return the new name, and the sys.servers table will show the new name.

If the computer has any remote logins, running sp_dropserver may generate an error similar to this:

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

  • For a default instance, run the following procedure: 

    sp_dropremotelogin old_name
GO
  • For a named instance, run the following procedure: 

    sp_dropremotelogin old_nameinstancename
GO
Maintenance Plans

[Tai Yee – MSFT] The script below was added by a customer to the SQL Server 2008 version of this topic(http://technet.microsoft.com/en-us/library/ms143799(SQL.100).aspx) and for SQL 2008 R2 ( http://msdn.microsoft.com/en-us/library/ms143799.aspx ). As it also applies to SQL Server 2005, it is being added here as well. However, please be advised that this method is not supported by Microsoft; use at your own risk.

Maintenance plans don’t get their connections changed to the new server name and so they may break. After a rename you may find that you cannot delete or rename the existing maintenance plans, so either delete them before renaming the server and recreate them afterwards or run the following script to fix them:

use msdb
DECLARE @oldservername as varchar(max)
SET @oldservername='<server name><instance name>'

-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername=@@servername

declare @xml as varchar(max)
declare @packagedata as varbinary(max)
-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor
FOR
SELECT    id
FROM         sysdtspackages90
WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix


declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1)  -- for each plan

begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysdtspackages90 where id= @planid  -- get the plan's xml converted to an xml string

declare @planname varchar(max)
select @planname=[name] from  sysdtspackages90 where id= @planid  -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
UPDATE    sysdtspackages90 SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

end
fetch next from PlansToFix into @planid  -- get the next plan

end

close PlansToFix
deallocate PlansToFix
----- This will also handle the packages that have a tag such as
----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>


DECLARE @oldservername as varchar(max)
SET @oldservername='<server name><instance name>'-- set the new server name to the current server name
declare @newservername as varchar(max)
set @newservername = @@servername
declare @xml as varchar(max)
declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
DECLARE PlansToFix Cursor FOR
SELECT id
FROM sysdtspackages90
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

OPEN PlansToFix
declare @planid uniqueidentifier
fetch next from PlansToFix into @planid
while (@@fetch_status<>-1) -- for each plan
begin
if (@@fetch_status<>-2)
begin
select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max))
from sysdtspackages90 where id= @planid -- get the plan's xml converted to an xml string
declare @planname varchar(max)select @planname=[name] from sysdtspackages90 where id= @planid -- get the plan name
print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
UPDATE sysdtspackages90 SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
end
fetch next from PlansToFix into @planid -- get the next plan
end
close PlansToFix
deallocate PlansToFix

By: Microsoft



            


                

                        
                Category: Databases                |
                
                Comments Off on MSSQL: How to rename an Instance of MSSQL 2005