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 – How to get all columns name list with Database Name, Table Name, Schema and other details

Run the below script and see the result.

SELECT [TABLE_CATALOG]

      ,[TABLE_SCHEMA]

      ,[TABLE_NAME]

      ,[COLUMN_NAME]

      ,[ORDINAL_POSITION]

      ,[COLUMN_DEFAULT]

      ,[IS_NULLABLE]

      ,[DATA_TYPE]

      ,[CHARACTER_MAXIMUM_LENGTH]

      ,[CHARACTER_OCTET_LENGTH]

      ,[NUMERIC_PRECISION]

      ,[NUMERIC_PRECISION_RADIX]

      ,[NUMERIC_SCALE]

      ,[DATETIME_PRECISION]

      ,[CHARACTER_SET_CATALOG]

      ,[CHARACTER_SET_SCHEMA]

      ,[CHARACTER_SET_NAME]

      ,[COLLATION_CATALOG]

      ,[COLLATION_SCHEMA]

      ,[COLLATION_NAME]

      ,[DOMAIN_CATALOG]

      ,[DOMAIN_SCHEMA]

      ,[DOMAIN_NAME]

  FROM [INFORMATION_SCHEMA].[COLUMNS]

There are many other way to get the same result in SQL Server, if you can find than please share with me. 

SQL SERVER - What is the role of resource database WITH example

Before run below script run the detail  of resource database.

 

·        The resource database is read only database which contain the all Sys.Objects is SQL Server.

·        Resource database physical location is <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. And the file name is always mssqlsystemresource.ldf.

·        This file cannot backup and restore. But if it required than you have to simple copy and paste option.

·        We cannot overwrite resource database.

·        Resource database can only modify by Microsoft customer support service specialist. Resource database ID is always 32767

·        To get the version number, last update date time and to access the sql server object definition of resource date base we can use  

SELECT SERVERPROPERTY('ResourceVersion');

GO

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO

SQL SERVER - Difference between RDBMS and DBMS

RDBMS and DBMS:-

1.      RDBMS DBMS + Referential Integrity

2.      An RDBMS is one that follows 12 rules of CODD.

DBMS:

·        In DBMS no relationship concept

·        It supports Single User only

·        It treats Data as Files internally

·        It supports 3 rules of E.F.CODD out off 12 rules

·        It requires low Software and Hardware Requirements.

·        FoxPro, IMS are Examples

RDBMS:

·        It is used to establish the relationship concept between Two database objects, i.e, tables

·        It supports multiple users

·        It treats data as Tables internally

·        It supports minimum 6 rules of E.F.CODD

·        It requires High software and hardware requirements.

SQL-Server, Oracle is examples

SQL SERVER - What is DBMS

·        In DBMS no relationship concept

·        It supports Single User only

·        It treats Data as Files internally

·        It supports 3 rules of E.F.CODD out off 12 rules

·        It requires low Software and Hardware Requirements.

SQL SERVER : We cannot Perform Operation in MASTER database

Good Afternoon .........All...........

I Friends this very important thing which I am sharing with you. Just read about Master Database Restriction.

The operations cannot be performed on the master database in SQL Server 2005/2008/2012.

• Adding files or filegroups SQL Server 2005/2008
•  
•  Changing collation. The default collation is the server collation SQL Server 2005/2008
• 
  
• Changing the database owner. master is owned by dbo SQL Server 2005/2008
• 
  
• Creating a full-text catalog or full-text index SQL Server 2005/2008
• 
  
• Removing the primary filegroup, primary data file, or log file.
• 
  
• Renaming the database or primary filegroup.

• We cannot Setting the database to OFFLINE.
• 
  
•  We cannot Setting the database or primary filegroup to READ_ONLY.
• 
  
• Enabling change data capture. 
•  
• Participating in database mirroring.
•  
• Creating triggers on system tables in the database.
•  
• Dropping the database SQL Server 2005/2008
•  
•  Dropping the guest user from the database.

I hope this ll help to you .................