Saturday, 30 September 2017

Rename Physical Database File names of an Existing Database in SQL Server

Usually, Physical database names are not changed after the database creation, unless there is a requirement for name change for the created database. Say, a database is created and it is renamed with a new name. The new database name will still point to the same physical paths of the originally created database and hence cause confusion to rectify the right database file names.

Unlike, the logical file names which can be altered using the ALTER command for the database, the physical database file names cannot be changed when the database is online. Very straightforward to know that these physical paths are already being used when the database is ONLINE. Hence, we will have to take the database offline and then proceed to alter the physical database file names. 

Before moving on to the methods of renaming the physical database files, let’s check the information of the database by using a simple stored proc.

EXEC sp_helpdb <Database_Name>

Example:

USE SQLArena_Test
EXEC sp_helpdb SQLArena_Test

From the above stored proc, if we have to gather the physical data filenames and logical filenames of the database, proceed with the query as:

select file_id, NAME as [LOGICAL_FILE_NAME], PHYSICAL_NAME
from sys.database_files


Take database offline and then rename the Physical Database Files

In order to change the physical filename from the screenshot, let’s first disconnect all the opened session in the SQL Server and take the database OFFLINE.

USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

Check if the database is offline or online with the command as:

SELECT NAME, STATE_DESC FROM SYS.DATABASES WHERE NAME='SQLARENA_TEST'



Now, all the sessions and process connected to SQLArena_TEST database is disconnected and taken the database to offline mode. Let’s ALTER the physical filenames by "_CHANGED" :

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_CHANGED.mdf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_1', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_1_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_2', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_2_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_log', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_log_CHANGED.ldf')
GO

Set the database to online and now you can now check the database information and the status of the databases as:

EXEC sp_helpdb SQLArena_Test
GO
USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

While using these scripts in production should be with caution, since backups may fail if the logical filenames are incorrectly assigned. 

Wednesday, 27 September 2017

TRIM Function to Remove Blank Spaces From a String in SQL Server

TRIM Function to Remove Blank Spaces From a String in SQL Server
Often while entering data in the tables or during bulk insertions, there are high chances of accidently adding white space either in the trailing or leading end of the string. Such instances can cause very annoying for the users to see the results in a shabby format. To overcome the problem of blank space in the string, we can create the TRIM() function and pass the string as parameter to the function.

Before moving to create the trim function, let’s see the two types of Trim functions that are available in SQL Server

1.  LTRIM - This function helps in removing the blank spaces from the leading end of the string
2.  RTRIM - This function helps in removing the blank spaces from the trailing end of the string

Using the above function in SQL Server to trim the string of an existing table in a database. The initial step is to create the table and insert some values to it with blank spaces. For example, In the CusName column insert names with the blank spaces both front and back of the string.

USE SQLArena_Test
GO
Create table TestDB
(
EID int null,
CusID nvarchar(100) null,
CusName Varchar(100) null
)

From the above table created, in case the names in “CusName” consists of leading and trailing spaces, then use the below command:

UPDATE TestDB SET CusName= LTRIM(RTRIM(CusName))

In the update statement, both LTRIM() and RTRIM() functions are applied while passing the CusName as the parameter. Both the functions are needed to apply in SSMS or Oracle to remove unwanted white spaces. In case if we want to create a function named “Trim” then we can create the function as show:

use SQLArena_Test

GO

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

GO
SELECT dbo.TRIM('SQLArena')
GO

Additionally, using these function in JOINT and WHERE clause can reduce the performance drastically and using these in production should be into consideration as the query run-time can increase very high. 

Tuesday, 26 September 2017

How to Add or Remove Identity Property on Column from a table in SQL server

I got stuck at a point on removing the identity column from a table in a database, thinking much about T-SQL scripts to remove the identity property. However, there is no direct DDL scripts like ALTER or MODIFY tables to remove the identity property.

Steps to remove the Identity property through GUI in SSMS:

1. Before dropping the identity, please check the table from the database that you are referring to
2. From the “Object Explorer”, expand the database referring to and right click on the table where the identity property needs to be changed.
3.  Select “Design” option from the list, which will open the design of the table as shown below.
4. Under Result section, the design is popped with the details of the table with respect to structure.
5. Now, expand the “Identity Specification” and opt as “Yes/No” in the “Is Identity” option.
6. Click on the “Save” option to effect the changes made

When you click on the "Design option", the below layout pops up:


Take a look at the result section to opt the "Is Identity" changes:
















Additionally, there is an option to generate T-SQL script by the action performed using the GUI in SSMS. Since there is no ready-made script for changing the identity property, a feature in SSMS could be used to generate the T-SQL script.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

/*creating a DumpTable*/
CREATE TABLE dbo.Tmp_DumpTable
       (
[COUNT] int not null identity(1,1),
fruits nvarchar not null,
vegetables nvarchar not null
       )  ON UserData
       TEXTIMAGE_ON UserData
GO
ALTER TABLE dbo.Tmp_DumpTable SET (LOCK_ESCALATION = TABLE)

GO
/  /*Setting the Identity_Insert to ON*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable ON
GO
IF EXISTS(SELECT * FROM dbo.DumpTable)
       EXEC('INSERT INTO dbo.Tmp_DumpTable (Count,Fruits,Vegetables)
              SELECT Count,Fruits,Vegetables FROM dbo.DumpTable WITH (HOLDLOCK TABLOCKX)')
GO
/  /*Setting the Identity_Insert to OFF*/
SET IDENTITY_INSERT dbo.Tmp_DumpTable OFF
GO

/*Dropping the created DumpTable*/
DROP TABLE dbo.DumpTable
GO
EXECUTE sp_rename N'dbo.Tmp_DumpTable', N'DumpTable', 'OBJECT'
GO

COMMIT

Monday, 25 September 2017

Rename Logical Database File names of an Existing Database in SQL Server

Basically, in SQL Server there are two database files, one as physical file name and the other as logical file name. We will be much focusing on the logical file name of a database. Generally, when a databases is created, one primary data file, two secondary data files and one logical files are created. The logical files in a database helps holding the active transaction of a database. We should see that the logical files are always kept unique to resist from unnecessary confusions.

Usually, in SQL server while managing a database in ethical form, just one logical file is created. In case if there are too much transactions in a database, which keeps them filled always, then probabilities could be to create a new logical file.

 To view the information of a database, use the below stored proc:

EXEC sp_helpdb <Database_Name>

Example: 

USE SQLArena_Test
GO
sp_helpdb SQLArena_Test

Following the above query can show the details of the database mentioned. In order to select the logical file name from the database, use the below query:

select file_id, NAME as [LOGICAL_FILE_NAME], PHYSICAL_NAME
from sys.database_files


In order to rename the logical file names from the mentioned screenshot to a new name, let’s follow the script below:

Rename SQLArena_Log to SQLArena_Log_1, along with change in physical data files and indexes.

USE SQLArena_Test
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Data1, NEWNAME=SQLArena_Data_1)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Log, NEWNAME=SQLArena_Log_1)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Data2, NEWNAME=SQLArena_Data_2)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Indexes,NEWNAME=SQLArena_Indexes)

Saturday, 16 September 2017

Set Database to Full Recovery Model in SQL Server

Full recovery model is SQL server is an option, which helps in keeping all the data transactions in the transaction log. The entire transaction will be held until there is a transaction log backup created. The idea is pretty simple, wherein the entire transactions are logged into the transaction log, which then gets moved to the primary file or secondary file accordingly. Usually, keeping in full recovery option can lead to increased space, since the transaction log contains all the transactions. Further, to avoid the same, there is a need to create log backup to reduce the size of transactions with held in the transaction log.

To recovery from disasters, full recovery helps in point in time recovery. One advantage of full recovery model in SQL server is that, in case if there are any failure in the transaction log, the entire process gets cancelled or revoked. In SQL Server, when a database is created, by default full recovery model is set.

Some of the reasons to Full Recovery model:
  1. Point-in-time recovery is possible with full recovery model
  2. Very huge transactions was held and the data is very critical
  3.  Protection against data loss
T/SQL script to set the database to simple recovery model

ALTER DATABASE <Database_Name> SET RECOVERY FULL

Follow the below steps in SQL Server Management Studio graphic user interface:
  1. Right on the respective database and click “properties”
  2. Select “options” from “Select a page” side bar
  3. A drop is seen under “Recovery Model”. Select “Full” from the drop down.
  4. Click “OK” to set the database to simple recovery model
The screenshot for the same tagged below:







Wednesday, 6 September 2017

Set Database To Bulk-logged Recovery Model in SQL Server

The concept of bulk-logged is pretty tricky when recovery model is into consideration. Bulk as the name says, does bulk transactions such as inserts, updates, imports, which will be captured on transaction log during database recovery. Unlike full recovery model, the Bulk-logged takes the minimal of the fully logged transactions. On a transaction-by-transaction in a database, there will be increased risk of data loss when set as bulk-logged recovery model. Bulk-logged can take a lot of space consumption if there are not transaction log backups created. However, the bulk-logged can help to recovery from point-in-time as far as there no transaction logs involved. Additionally, backups that can be created from the bulk-logged setting would be full backups, differential backups, COPY_ONLY backups and T-log backups.

Some of the reasons to go for Simple Recovery Model:

  1. Minimum log space is used by bulk-logged
  2. High Performance
  3. Comparatively less critical data
  4.  Possibility of point-in-time recovery


T/SQL script to set the database to bulk-logged recovery model:

ALTER DATABASE <Database_Name> SET RECOVERY BULK_LOGGED


Follow the below steps in SQL Server Management Studio graphic user interface:

1.      Right on the respective database and click “properties”
2.      Select “options” from “Select a page” side bar
3.      A drop is seen under “Recovery Model”. Select “Bulk-logged” from the drop down.
4.      Click “OK” to set the database to simple recovery model

The screenshot for the same tagged below:



You may also need: Set Database To Simple Recovery Model in SQL Server

Friday, 1 September 2017

Set Database To Simple Recovery Model in SQL Server

“Simple” as the name says, is an option in SQL Server to set the database to simple recovery model in order to recover a database from disasters. Simple Recovery Model will trap simple and minute transactions happening in the database, since the transaction will be overwritten on the existing data once set as “Simple”. Though the database is in simple recovery model that is rewriting with new transactions, point-in-time recovery is not possible. However, in case of recovery, either the full backups or differential backups that are latest with respect to date can be used. 

 Some of the reasons to go for Simple Recovery Model:
  1.  Data present is simple and non-critical
  2.  Simple mode is usually used for testing or development environment
  3. Higher performance bulk copy operation.
  4. Space occupied in the disk is drastically less
T/SQL script to set the database to simple recovery model

ALTER DATABASE <Database_Name> SET RECOVERY SIMPLE

Follow the below steps in SQL Server Management Studio graphic user interface:

1. Right on the respective database and click “properties”
2. Select “options” from “Select a page” side bar
3. A drop is seen under “Recovery Model”. Select “Simple” from the drop down.
4. Click “OK” to set the database to simple recovery model

The screenshot for the same tagged below: