Move master database from one drive to another

Sometime you may need to move master database from one drive then you may help from given below info

Open SQL Server Configuration manager from program files. In SQL Services Nodes, right clieck on SQL Server(i.e.MSSQLSERVER) and choose Properties and go to Advanced TAB and Edit Startup Parameters values to point to planned location for the master database data abd log files and then click on ok.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-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


If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.

and restart the instance.

Verify the file change for the master database by running the following query.

CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

Move TempDB from one drive to another drive

Sometime you may face some problem related to Tempdb like Tempdb is full or it's affecting drive then that we need to move tempdb from one drive to another.
Please follow following steps for that.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

after executing above command. Please restart the SQL server Servvices.

To varify the location of the tempdb please execute following commands

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Split Function in Separated by delimeter in a Strings of a column

Some time we need to extract the values from a string that time we use to do a lots of RND that time we can use given below function. This function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (Data varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1>

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

How to use this function?

Ok Let me explain

we have column jobids in a table TABLE1 which is a string. We have to extract the data separated with comma.

select * from Table1

Result

300-35,300-34

Now we have to separate the delimited by comma.

Declare @jobids as varchar(50)

select @jobids=jobids from Table1

select * from dbo.split(@jobids,',')

Result-

Data

300-35

300-34

Restoring Differential backup to database

To restore a differential backup, you must first restore a full backup while ensuring that the databases is NOT recovered. The most recent differential backup is then applied to the database.

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSFULL.BAK' WITH NORECOVERY

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSDIFF.BAK' RECOVERY

The first command restore Full backup, leaving the database unrecovered. The second command applies a differential backup and then recovers from database.

List all unused indexes

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName
FROM sys.indexes I
WHERE -- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- limit our query only for the current db
AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName

Find Size all Database with Path

Find the Size of Ldf File
Total Size of All LDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Total Size of Each LDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Find the Size of MDFFile
Total Size of All MDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1
Total Size of Each MDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1

Create a User AND assign Permision

--Create a User
--Set DatabaseName as the current database
Use DatabaseName
Go
--Changed database context to 'DatabaseName'.
---- Create a new server login name: Amit
CREATE LOGIN Amit WITH PASSWORD=’password’
Go
-- Create a new database user linked to the login name
CREATE USER Amit FOR LOGIN Amit;
-- Grant database ALTER permision to the user
GRANT ALTER To Amit;
Go
-- Grant database CONTROL permision to the user
GRANT CONTROL To Amit;
Go