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 ADDbutton 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. 

How to change the password in SQL Clustering? Or Best practices of Password Change on SQL Failover cluster

Best practices of Password Change on SQL Failover cluster 

Important note: This activity is required downtime. Use RDP <Remote Desktop Protocol> to log into each physical node of the cluster

1. Changing password for Windows Cluster services-

Start Run à enter Services.msc à Change password for windows cluster services.

2. Changing password for SQL Services-

Start Run à enter SQLServerManager.msc(SQLServer 2005) or SQLServerManager10.msc (SQL Server 2008)

SQL Server Configuration Manager Change the password for SQL services. Start from Active node. Change First Active instance and change its other passive nodes

To make sure cluster running fine: - Once password changes done on all SQL services on Active and Passive nodes, SQL Instances must need to be failed over.

3. Failover test on Cluster Administrator-



Start Run à entering cluadmin.exe


Once done on all nodes, failover the instances to another node and make the password get effected.


Restart the Cluster services on each node to make the password get effected.


Please comment if you know other way to change the password. - Jainendra Verma

What is backups in SQL Server? How to take all type of backup, backup syntax with example?

What is backups in SQL Server? How to take all type of backup, backup syntax with example?

1)       Full Backup: - Full backup, backs up the entire Database including Transaction Log. With full Backup Point-in-time recovery is possible because it contains .mdf and .ldf. It offers complete protection against media failures. It takes more time & requires more space to store the backup file

--FULL BACKUP: -

Backup database dbname to disk = “path”

--Example

BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

2)       Differential Backup: - Differential backup records only the data that has change or modified extents since the last full backup. Takes less time and requires less space. To perform the differential backup, we require at least one full backup.

--DIFF. BACKUP: -

Backup database dbname to disk = “path” with differential

--Example

 BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL

3)       T.Log Backup: - Transaction Log that records all transactions since last transaction log backup or differential backup. It can be used for recovering the data to the point of failure (or) specific point-in-time. These are the incremental backups; it is possible only in Full and Bulk logged recovery, models only. It is used to truncate the T.log periodically. To implement log shipping

--T.LOG BACKUP: -

Backup log dbname to disk = “path”

--Example

BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

4)       File/File Group backup: - File and File group backups are a specialized form of DB backup. In which only certain individual file (or) file groups from a DB are backup.

--FILE/FILE GROUP BACKUP: - APPLICABLE ONLY ON ADDITIONAL FILEGROUP, NOT ON PRIMARY FILEGROUP.

backup database <dbname> filegroup=’FG name’ to disk=’path’

--Example

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' TO DISK = 'C:\TestBackup_ReadOnly.FLG'

5)       Copy only backups: If we want backup without effecting existing plan we can use copy only backups. SQL server supports copy-only full backup and copy only t.log backup.

--COPY ONLY BACKUPS:

Backup database/Log dbname to disk = “path” with copy_only

--Example

Backup database [AdventureWorks2012] to disk = 'D:/copyonlybak.trn' with copy_only

6)       Striped backups: If the size of backup is large where there is no required disk space in any drive we can split backup into multiple files into different disks.

--STRIPED BACKUPS:

Backup database dbname to disk = “path1,to disk=’path2’ WITH NOFORMAT,STATS=10

--Example

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

7)       Mirrored Backups: In order to reduce restoration problems we can take backup of same db in multiple locations so that if any media set is get damaged we can restore from the. Maximum we can write 4 mirrors for same media set.

--MIRRORED BACKUPS:

Backup database dbname to disk = “path’, MIRROR TO disk=’path2’ WITH FORMAT

--Example 1

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'

WITH FORMAT

--Example 2

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',

DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak',

DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak'

WITH FORMAT

8)       Tail-log backup: The most common situations-

To perform a log backup that will skip log truncation and then take the database into the RESTORING state in an atomic state, use the NO_TRUNCATE and NORECOVERY options together.

--Example

BACKUP LOG AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH NORECOVERY, NO_TRUNCATE

Note: We recommend using NO_TRUNCATE only when your database is damaged or corrupted. 

If database is offline and you are unable to start but you need to restore the database, than will have to back up the tail of the log. No transactions can occur during this time, using the WITH NORECOVERY option is not mandatory.

If a database is damaged, you should try to make a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP statement. 

CHECKSUM: is also an option which will show you the Page Id in result where the database has corrupted. 

--Example

BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH CHECKSUM, CONTINUE_AFTER_ERROR

Option: -

INIT means overwrite

noinit mean append

format mean independent

noformat: both files are required

Note :- To enable checksum by default for all database backups-

       SP_CONFIGURE BACKUP CHECKSUM DEFAULT

Please comment below if missing any type of backup- Jainendra Verma