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 : Important Commands for SQL Server DBA

--To check for Blocked Processes:

Sp_who2   

--To find all the log files and the percentage space used of those log files in an instance:

dbcc sqlperf (logspace)  

--To list all the files and their details in a particular database:

exec sp_helpfile

--To list all the databases and their details in a instance:

exec sp_helpdb

--To shrink a database file without specifying target size(preferred for log files):

dbcc shrinkfile(fileid)

--To shrink a database file with specifying target size(preferred for datafiles):

dbcc shrinkfile(fileid,target_size)

--To find the details about locks currently held by the processes:

exec sp_lock

--To find all the drive spaces through MS SQL:

EXEC master..xp_fixeddrives

--To put the database in single user mode at command prompt:

sqlservr.exe –m

--To put the database in Minimal mode at command prompt:

sqlservr.exe –f         -->  -c  for console applications

--To find out the Database ID?

 Select db_id ('db_name')

--To find out logins:

select * from sys.syslogins

--To find out current user:

Select current_user

--Short cut for MS SQL Server Management studio:

sqlwb.exe

--To check the service pack at product level:

select serverproperty ('machinename')

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

--To check the open transactions :

dbcc opentran('dbname')

--To find MS SQL Server product level details:

exec master..xp_msver

--To find the details of current processes running like percent completed:

select * from sys.dm_exec_requests

--To find the longest running queries

select * from sys.dm_exec_query_stats

--Command to find the free space and used space :

select * from dbo.sysfiles

--To check load in server and database and files:

select * from sys.dm_io_virtual_file_stats(database id,file id)

--To find the longest running queries

select * from sys.dm_exec_sql_text

--To find Orphan login:

sp_validatelogins

--To find orphaned users

EXEC sp_change_users_login 'Report'

--To map users to logins

exec sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'

--If you already have a login id and password for this user, fix it by doing:

exec sp_change_users_login 'Auto_Fix', 'user'

--To delete login:

exec sp_revokelogin 'USERX'

--To list all the objects owned by user id:

Select name from sysobjects where uid=user_id('user')

--To enable ‘show advanced options’ for server configuration options:

exec sp_configure 'show advanced options', 1

--To enable particlar server configuration option:

exec sp_configure 'option name', 1

--To find performnce related stastictics:

select * from sys.dm_os_performance_counters

--To find the information about indexes on tables and views.

select * from sys.dm_db_index_usage_stats

--To find out all of the users who are connected to the database server:

select * from sys.dm_exec_sessions

--To display the error log using the query:

xp_readerrorlog

--To display error log archive numbers and their dates:

exec sp_enumerrorlogs

--To view no. of traces running.

SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

--To find details about the traces which are running.

SELECT * FROM :: fn_trace_getinfo(default)

--To terminate a trace

EXEC sp_trace_setstatus 1, @status = 0 / @status=2

--To know the Space used bye Data,index in particular Database:

Sp_spaceused

--To know  the DB status of particular Database:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')

--To view jobs which failed at last run:

 sysjobactivity, sysjobschedules, msdb.dbo.sysjobservers, msdb.dbo.sysjobs

--To check pages of table :

Dbcc ind('dbname','tablename',-1)

--To check pages contents

dbcc trace on(3604)  dbcc page('dbname',fid,pid,1)              -- F:File  P:page

--To set maximum connections.

exec SP_Configure

--To find no.of connections.

SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0

--If log file is full,To find out reason.

Select name,log_reuse_wait_desc  from sys.databases