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. 

You may also need: "Rename Logical Database File names of an Existing Database 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. 

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 the “Design” option from the list, which will open the design of the table as shown below.

4. Under the 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 a 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

Related Post: "Reset Identity Column Values In SQL Server"

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)

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:

You may also need:
"Set Database To Bulk-logged Recovery Model in SQL Server"
"Set Database To Simple Recovery Model in SQL Server"

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

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: