Moving MASTER Database

In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step.

Step 1: Query sys view and note down the existing location for MASTER database

USE MASTER 
GO 
SELECT 
NAME, 
PHYSICAL_NAME AS 'PhysicalFilePath', 
STATE_DESC AS 'DB Status' 
FROM SYS.MASTER_FILES 
WHERE NAME LIKE 'Mast%'

Step 2: Run alter database command and change the location for database files

ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MASTER, 
FILENAME= 'C:\SQLDB\Demo\Master.mdf' 
) 
GO 
ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MastLog, 
FILENAME= 'C:\SQLDB\Demo\MastLog.mdf' 
) 
GO 

Step 3: Stop SQL Server Service and move database files to new location

Step 4: Restart SQL Server Service, surprised ?

 

Step 5: This was expected, let’s see what errorlog has to say about this!

Refer the highlighted section, SQL Server service could not find the files. This is because we have moved that files to new location.

Step 6: Okay, so let’s go and change the start-up parameter. We can do this using Configuration manager.

Step 7: Right click on SQL Server service –> Properties –> Start-up Parameter

Step 8: Make correction in path for Master.mdf and Master.ldf

Step 9: Start SQL Server service, this time it will start.

You are done!!

Note: This is to be done when we have to do relocate databases to new drive, or file organization, or some error which force us to do this.

-- Hemantgiri S. Goswami

Moving MSDB to new location

In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step.

Step 1: Let’s query sys view and note down the location for database files

SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

Step 2: Run alter database and specify new location for database

SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.mdf'
)
GO

Step 3: Stop SQL Server service

Step 4: Once SQL Server service is stopped move MSDB database to new location
Step 5: Now, start SQL Server service. This time it will use the new path that we have configured in Step 2.
Note: If you have enabled and configure Database Mail, please make sure it works after you moved MSDB to new location.

Tomorrow, I will post about how to relocate Master database.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/)

Register for SQL Server 2012 Virtual Launch

`As we all are aware SQL Server 2012 virtual launch is scheduled after 60 hours roughly. Earlier I have discussed what’s new in SQL Server 2012 setup, MS has released SQL Server 2012 training kit and made it available for us to download. In recent past, I have blogged about a free eBook for SQL Server 2012 written by Ross Mistry ( Blog | Twitter ) and Stacia Misner ( Blog | Twitter) .

You will see lots of new and insightful information on new and enhanced features introduced in SQL Server 2012, this includes:

• AlwaysOn
• Automating AlwaysOn management
• New backup and restore capabilities
• ColumnStore index
• Semantic Search
• User-defined server roles
• Data quality service
• StreamInsight for SQL Server 2012
• Big data analytics and Hadoop
• T-SQL Enhancements
• SQL Server Data Tools (SSDT)
• SQL Azure

Sounds interesting ?

Get yourself register for the event, you will find the agenda of the event here.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/ )

Search MSDN or TechNet on Bing

Today, I found an interesting blog on CRM team blog which could be helpful to anyone who is wokring on Microsoft technologies. So I thought to share the same content overhere. Please visit the below link to have filtered search for Dynamics CRM 2011 SDK and implementation guide.

http://blogs.msdn.com/b/crm/archive/2011/03/22/find-sdk-and-implementation-guide-content-on-msdn-or-technet.aspx

Non CRM people can refer the below example.

How can I make new filtered search favorites?

These search filter favorites work because Bing indexes the MSDN and TechNet content using search tags embedded in the metadata of the document. Each page has several Search.MSCategory meta tags that describe the hierarchy that the page appears in the MSDN or TechNet table of contents. Once you identify the page that represents the true parent node for the portion of the MSDN table of contents, you can create a similar filtered search favorite. The tricky part is identifying that true parent node.
 
To create a filtered search favorite:
1. Navigate to MSDN or TechNet and identify a topic area you are interested in. In this example, let’s say I want to focus on the Silverlight documentation.
2. Examine the Table of contents. This works best using the “Classic” view.
3. Locate a topic below the main table of contents node. I’ll choose the XAML topic.
4. If you right click on the topic and use the context menu to choose View Source, in the area where you might expect to find meta tags you will see this:

<!-- Looking for metadata? Use the robot view instead http://currentsite/currenturl(robot).aspx -->

5. Following those instructions, open this page: http://msdn.microsoft.com/en-us/library/cc189054(VS.95)(robot).aspx
6. Now right click on the page and use the context menu to choose View Source.
7. Now you will see lots of meta tags. Look for the ones with the name value of Search.MSCategory
For this page you should find the following:

<<meta name="Search.MSCategory" content="ms310241" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="aa139615" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="cc838813" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="ff630996" xmlns="http://www.w3.org/1999/xhtml" />

Each of these represents the true parent in the hierarchy of the ‘anchor’ pages in the table of contents. At the top of the XAML overview page in the classic view you can see this visually:

MSDN > MSDN Library>.NET Development > Silverlight > XAML

But these anchor pages in the table of contents don’t necessary represent the true parent of the hierarchy that is indexed. To search for only Silverlight documentation we are interested in the value of the content attribute in the second meta tag from the bottom: cc838813.
If you open this page directly, (using http://msdn.microsoft.com/en-us/library/cc838813.aspx) you will see a strange page with the title @No Title, but this strange page is actually the root table of contents node according to the meta tags.
8. Now that you have identified the ‘true’ parent, With that page number you can create a search filter:

[Search Terms] meta:Search.MSCategory("cc838813") site:msdn.microsoft.com

9. Go to http://www.bing.com and type the search filter into the search field. Execute the search and save the resulting page as a favorite.

You have now created a search favorite for the Silverlight documentation.