SQL Server -How to move the “system database” files in SQL Server

It’s Very Simple 10 steps to do this.

In this example System database files existing in C: Drive and I will move it in D: Drive.

Step1- Check the D drive is access. Right click on D drive and go to Properties, go to security tab and find the Administrator “group or username” is there on not.

Step2- Next Step to create a folder in D drive. And the folder name should be as SQL server “Instance ID” name, which is “MSSQL10.MSSQLSERVER”. We can get this name from existing SQL server path for example see following path:-   

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

We can say this folder is the Data container for all System Database files.

Now we have to check the folder permission of the folder and if anything is missing so we will give that permission to newly created folder “MSSQL10.MSSQLSERVER”.  

Step 3- Open the both old folder and newly create folder properties and click on security tab and match the old folder and newly create folder “group name” and “permission” and click on ADD button than search the “group name” and ADD it in new folder.

Step4- Now we will move the Model and MSDB and TEMPDB database.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = modeldev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\model.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = modellog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\modellog.ldf');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = msdbdev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\msdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = msdblog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\msdblog.ldf');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\MSSQL10.MSSQLSERVER\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\MSSQL10.MSSQLSERVER\templog.ldf');

GO

Step 5- Stop the SQL server instance and COPY the Model and MSDB database file form C: Drive and PASTE it into D Drive- D:\MSSQL10.MSSQLSERVER .  Now we can START the SQL server instance.

Note- No Need to move TEMPDB files. Once the instance will START it will create automatic new TEMPDB log files.

Step 6- Now we have to move Master database. Go to Configuration manager, Right click on SQL server Instance and Properties, Click on Advanced tab. Here we will get the “Startup Parameters” value.

“Startup Parameters” value:-

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

In this value we need to change the Path of each log file. Our current log file path is- D:\ MSSQL10.MSSQLSERVER

New “Startup Parameters” value:-

-dD:\ MSSQL10.MSSQLSERVER\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\ MSSQL10.MSSQLSERVER \mastlog.ldf

Step 7- Now we have to STOP the SQL server instance.  Copy the Master database log files and Paste it in D Drive D:\ MSSQL10.MSSQLSERVER.

Step 8- Verify all the file has been moved in New Location,

Step 9- Delete the ALL the System Database files from C drive.

Step 10- Now we must have to START the SQL server instance. 

AND ROCK and ROLL 

SQL Server: Best practice to Move database from one Location to another location.

Most of  SQL Server professional may know the how to move the database form One location to another location. And most of the professional use the "detach and attach" method but..

"Is it best practice?"   NO!!

WHY? – If your database file size is1TB or around 1TB it is not a best practice. So here is the question how to move the database if the database files size is large.

Why it not good practice because while attaching the database it will create the New database on new location where we are moving the database log files. So in this case the existing user will be removed from the database.

Actually it’s quite easy.

Please find the below steps-

USE master;

GO

-- Return the logical file name.

SELECT * FROM sys.master_files WHERE database_id = DB_ID('MY_test_DB')

    AND type_desc = 'LOG';

GO

-- Offline the database

ALTER DATABASE MY_test_DB SET OFFLINE;

GO

-- Physically move(Copy and Paste) the file to a new location.

-- Now we have to modify the path specified in FILENAME to the new location of the file on your server.

ALTER DATABASE MY_test_DB MODIFY FILE ( NAME = MY_test_DB_Log,

FILENAME = 'D:\NewLocation\MY_test_DB_Log.ldf');

GO

-- Online the Database

ALTER DATABASE MY_test_DB SET ONLINE;

GO

--Verify the new location file location.

SELECT * FROM sys.master_files WHERE database_id = DB_ID('MY_test_DB')

    AND type_desc = 'LOG';

SQL SERVER – Tips of Linked Server in SQL Server

        Linked Server is providing facility to access / connect data to other Data source like MS Access, Oracle, My SQL etc. To access other data source into SQL server instance. We can use Linked server option. To creating Linked Server we have to follow below steps-

Using User Interface-

1.       Open  SSMS(SQL Server Management Studio) and open server object(If not found this option then find it in ‘View’ menu )

2.       Then Right click on the Linked server and click on the ‘New Linked server’

3.       Then we need to provide required Stuff(Server name, Instance Name, Default database name, Data source type etc) and we can access data from newly connected Data Source.

Using SQL Query –

SQL Server has provided Stored procedure to creating Linked server.

Name - Sp_AddLinkedServer

Example-

EXEC sp_addlinkedserver

   @server = N'SEATTLE Mktg',

   @provider = N'Microsoft.ACE.OLEDB.12.0',

   @srvproduct = N'OLE DB Provider for ACE',

   @datasrc = N'C:\MSOffice\Access\Samples\MS_ACCESS_DATABASE.accdb';

GO

SQL Server – How to see the SQL Server ERROR Log.

SQL server stored the error log, which occurred in run time. Some time we need to see the error log to get the solution for Client and generally look into the stored procedure and need to see the error log to get instant solution of the problem

After login we can see the error log in SQLAGENT   it is the very simple way to see the error log but some time DBA team don’t gives the direct access then we can also see the error log using TSQL for analyse error and it reason.

In the sql server have inbuilt stored procedure sp_readerrorlog. Using this SP, we can see the error log for the specified time.

Code of System Stored procedure SP_readerrorlog:-

CREATE PROC [sys].[sp_readerrorlog](

   @p1     INT = 0,

   @p2     INT = NULL,

   @p3     VARCHAR(255) = NULL,

   @p4     VARCHAR(255) = NULL)

AS

BEGIN

   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)

   BEGIN

      RAISERROR(15003,-1,-1, N'securityadmin')

      RETURN (1)

   END

  

   IF (@p2 IS NULL)

       EXEC sys.xp_readerrorlog @p1

   ELSE

       EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

END

Parameter of SP:-

• Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
• Log file type: 1 or NULL = error log, 2 = SQL Agent log
• Search string 1: String one you want to search for
• Search string 2: String two you want to search for to further refine the results

Note:- We can run this system procedure without any parameter.

Please see below for readymade script to get error log.

Scripts1: To find the Error log in SQL Server 2000

DECLARE @HOURS INT

SET @HOURS = 24 -- it is the time of error log

-- creating the temp table to store the error log

CREATE TABLE #ErrorLog

(ErrorLog Varchar(1000),

ContinuationRow INT)

-- Inserting the data using SP

INSERT INTO #ErrorLog

EXEC sp_readerrorlog

DELETE FROM #ErrorLog

WHERE

(LEFT(LTRIM(ErrorLog),4)

NOT LIKE DATEPART(YYYY,GETDATE())

AND ContinuationRow = 0)

OR ContinuationRow = 1

OR LEN(ErrorLog) < 25

OR

(CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME)

< CAST(DATEADD(HH,-@HOURS,GETDATE()) AS VARCHAR(23)))

-- see the data

SELECT * FROM #ErrorLog

--Clear the data

DROP TABLE #ErrorLog

Scripts2: To find the Error log in SQL Server 2005.2008 and 2008R2

DECLARE @HOURS INT

SET @HOURS = 24 -- it is the time of error log

-- creating the temp table to store the error log

CREATE TABLE #ErrorLog1

(LogDate DateTime, ProcessInfo Varchar(50),

[Text] Varchar(4000))

-- Inserting the data using SP

INSERT INTO #ErrorLog1

EXEC sp_readerrorlog

DELETE FROM #ErrorLog

WHERE LogDate < CAST(DATEADD(HH,-@HOURS,

GETDATE()) AS VARCHAR(23))

-- see the data

SELECT * FROM #ErrorLog

--Clear the data

DROP TABLE #ErrorLog

 Share if you have any other option to get ERROR Log.