In my previous post we discussed about moving TEMPDB database to a different location.

In this we will discuss on how to move the MSDB & MODEL database.

As I said we can check the properties of any database using the following command:

SP_HELPDB <database name>

So for MSDB & MODEL databases, you can run the following command:

SP_HELPDB MSDB
GO
SP_HELPDB MODEL
GO

Now going to the process on moving the above databases to a different location follow the below steps:

1. Restart the SQL Server service in single user mode. Do the below to restart the server in a single user mode.

Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> Notification Services Command Prompt

or

Start -> Run –> Type Command or CMD -> Press OK or Enter

2. In the command prompt, type the following commands in the order.

a. NET STOP MSSQLSERVER (or) NET STOP MSSQL$<Instance_Name>

b. NET START MSSQLSERVER /c /m /T3608 (or) NET START MSSQL$<Instance_Name> /c /m /T3608

Now your SQL Server is in Single User mode and only one administrator can connect.

By default the object explorer is open and it is considered as one connection so if you click new query it is considered as second connection and hence you get the error. Inorder to avoid the error you need to click the disconnect button in the object explorer pane and then close the object explorer window.

3. Now click the New query and you could connect to the server. Once you are connected to it, run the following:

SP_DETACH_DB ‘MSDB’
GO
SP_DETACH_DB ‘MODEL’
GO

4. Now move the model and msdb data and log files to the desired location i.e F:\SystemDatafiles.

5. After you move the data and log files to the new location, attach the files using following:

SP_ATTACH_DB ‘MODEL’,'F:\DATA\MODEL.MDF’,'F:\DATA\MODELLOG.LDF’,
GO
SP_ATTACH_DB ‘MSDB”F:\DATA\MSDBDATA.MDF’,'F:\DATA\MSDBLOG.LDF’,
GO

6. Now start the SQL Server service in a normal mode using the below commands.

In the command prompt, type the following commands in the order.

a. NET STOP MSSQLSERVER (or) NET STOP MSSQL$<Instance_Name>

b. NET START MSSQLSERVER (or) NET START MSSQL$<Instance_Name>

7. Now check the properties of Tempdb like location, size, etc. using the following.

SP_HELPDB MSDB
GO
SP_HELPDB MODEL
GO

Now you can find the modified locations.

This is the way we change the location of MSDB & MODEL database.

In the next post, you can find more on changing the location of other system databases (MASTER).

Moving TempDB database to a different location:

As an administrator, sometimes it requires to move the System database to different location for various reasons like freeing up the space on drive, for improving the performance of the database, reducing the load on the drive, etc.

Now I am also facing the same issue, I need to free up the space on my C drive. Therefore, I am planning to move the System database. Today I tried in moving the Tempdb database to a different drive.

Before going with the process on moving the Tempdb database, I would like to discuss few things that are recommended just like prerequisites.

1. The source and destination drives should be of same type or format. Ex.: Fat32, NTFS, etc.
2. Make sure that the destination drive is not compressed. If it compressed already, you can uncompress by going to the properties of the drive.

Now let us go with process on moving Tempdb to a different location.

Data files of Tempdb are stored in the default location on C drive (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).

I want to move these files to F drive (F:\SystemDatafiles).

1. Execute the following to see the properties of Tempdb like location, size, etc.

SP_HELPDB TempDB

2. Now execute the following to change the location of data files of TempDB database.

3. Restart the Services of SQL Server.

4. Now check the properties of Tempdb like location, size, etc. using the following.

SP_HELPDB TempDB

Now you can find the modified locations.

This is the way we change the location of TempDb database.

In the next post, you can find more on changing the location of other system databases.

Microsoft SQL Server

The code base for MS SQL Server (prior to version 7.0) originated in Sybase SQL Server, and was Microsoft’s entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase itself. Microsoft, Sybase and Ashton-Tate originally teamed up to create and market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 3.0 on Unix, VMS, etc. Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1. Microsoft SQL Server v6.0 was the first version designed for NT, and did not include any direction from Sybase.

SQL Server Release History - as of 2009

About the time Windows NT was released, Sybase and Microsoft parted ways and each pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994, Microsoft’s SQL Server carried three Sybase copyright notices as an indication of its origin.

Since parting ways, several revisions have been done independently. SQL Server 7.0 was a rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which was the first edition to be launched in a variant for the IA-64 architecture.

SQL Server may refer to:

• Any database server that implements the Structured Query Language
• Microsoft SQL Server, a specific implementation of a relational database server from Microsoft
• Sybase SQL Server, a relational database server developed by Sybase

Microsoft® SQL Server™ is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions. In this section you will find information for several versions of SQL Server.

Microsoft SQL Server is an application used to create computer databases for the Microsoft Windows family of server operating systems. It provides an environment used to generate databases that can be accessed from workstations, the web, or other media such as a personal digital assistant (PDA).

T-SQL:

T-SQL (Transact-SQL) is the primary means of programming and managing SQL Server. It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself. Client applications, both which consume data or manage the server, leverage SQL Server functionality by sending T-SQL queries and statements which are then processed by the server and results (or errors) returned to the client application. SQL Server allows it to be managed using T-SQL. For this it exposes read only tables from which server statistics can be read. Management functionality is exposed via system-defined stored procedures which can be invoked from T-SQL queries to perform the management operation.

Source: Wikipedia (http://en.wikipedia.org/wiki/Microsoft_SQL_Server), FunctionX (http://www.functionx.com/sqlserver/Lesson01.htm), About.com (http://databases.about.com/od/sqlserver/Microsoft_SQL_Server.htm)