SQL Server # TSQL to Rename Database Name along with its Logical and Physical Name

There are many ways to rename the SQL server Database name. Today, I will explain how to rename a Database in SQL Server using T-SQL along with its Logical and Physical Name.

The very easiest and most popular way of renaming is – Suppose, you have a database named as “TestDB”. Now you want to rename it with “NewTestDB”.

  --Rename the data base "TestDB to New_TestDB".

  EXEC sp_renameDB 'TestDB','New_TestDB'

  GO

Above given example just renames the Database title (name); However if you want to rename the physical file, just Detach the database, Go to the file path which is currently located, rename both the LDF and MDF file, and now here Attach the database.

Below steps are given for renaming the database by using the TSQL statement – Just open SSMS Copy the below scripts and press F5.

USE TestDB

GO

è First of all set the database for a single user, it will disconnect all the currently connected users.

ALTER DATABASE TestDB

  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

è Now rename the Database title (Name).

EXEC master..sp_renamedb 'TestDB','NewTestDB'

GO

è Now the change the Logical file name, because above statement just change the name of the Database.

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB', NEWNAME=N'NewTestDB')

GO

ALTER DATABASE NewTestDB MODIFY FILE (NAME=N'TestDB_log', NEWNAME=N'NewTestDB_log')

GO

è Now Detach the Database for change the Physical file name, as we can't do anything with the physical file when the database is in active mode.

EXEC master.dbo.sp_detach_db @dbname = N'NewTestDB'

GO

è Now Change the SSMS settings if you haven’t set it.

è This is necessary because the below provided command will not run without set this.

sp_configure 'show advanced options'

GO

/* 0 = Disabled , 1 = Enabled */

sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE WITH OVERRIDE

GO

è Now rename your physical files with the use of command.

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB.mdf", "NewTestDB.mdf"'

GO

EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB_log.ldf", "NewTestDB_log.ldf"'

GO

è Now just attach the database. (Currently detached by us).

CREATE DATABASE NewTestDB ON

( FILENAME = N'F:\CD\Temporary Work\NewTestDB.mdf' ),

( FILENAME = N'F:\CD\Temporary Work\NewTestDB_log.ldf' )

FOR ATTACH

GO

è Now set the database as multi users can work on this.

ALTER DATABASE NewTestDB SET MULTI_USER

GO

è Finally you can see that, the physical files are renamed.

SELECT

  name AS [Logical Name],

  physical_name AS [DB File Path],

  type_desc AS [File Type],

  state_desc AS [State]

FROM

  sys.master_files

WHERE

  database_id = DB_ID(N'NewTestDB')

GO

Conclusion

Hence, we have learnt how we can rename the SQL Server Database along with its Logical & Physical using some simple TSQL statements.

Accessibility in C#

Each member of a class has an associated accessibility, which controls the regions of program text that are able to access the member. There are five possible forms of accessibility. These are summarized in the following table.

Accessibility - Meaning
public - Access not limited
protected - Access limited to this class or classes derived from this class
internal - Access limited to this program
protected internal - Access limited to this program or classes derived from this class
private - Access limited to this class

Clipboard Ring and Toolbox Snippets

Introduction

Today I show you my favorite feature in visual studio which is Clipboard Ring and Toolbox Snippets.

Implementation

1.    Clipboard Ring

Many times I wish I could go back to something I copied & pasted just a few minutes ago, then to have to go and copy the same text all over again.Turns out that Visual Studio already has this “hidden”feature.  It’s called the Clipboard Ring. 

If you copy a number of items to the clipboard, pressing [Ctrl] + [Shift] + [V] will paste the last text that was copied, but it will also highlight the pasted phrase.

Continue holding down [Ctrl] + [Shift] and press [V] again.  This will cause that highlighted section to cycle through the other items on the clipboard.  This is extremely useful when you have to copy multiple lines of separate text to a new document. 

You can also use [Ctrl] + [Shift] + [Insert]keys instead of [Ctrl] + [Shift] + [V]

2.    Toolbox Snippets

Now, you might be saying, “Great, but what if I want to save a piece of code that I use frequently in multiple places? It means samepiecesof code change in multiple files. I don’t want to have to cycle through the clipboard ring every time.”  Well, there’s also a feature for that! 

If you highlight a section of code (or text) and drag it to the Toolbox, VS will create a "snippet” (not to be confused with code snippets). When u want back that code then youdragged into the VS editor or double click on that snippet it will paste in VS editor.

And last but not leastthese snippets also available in next day.

Screen Sot

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

You know there is always an issue - the log file growing very fast and big. If you have plenty of storage, then this might not be a problem for you. Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

Implementation

1) Let’s first check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

2) Now truncate the log file.

USE tempdb;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1); --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

3) Let’s check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

Consider the following information when you plan to shrink a file:

• Make a full backup of your database before shrink the database file.
• From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx

Get numeric value from string using TSQL

Introduction
Many times while working on applications, we often land into a situation where we want to get the only numeric values from alpha numeric string. There are some approaches given, like you may achieve it with making a loop and without loop.


Approaches
I have prescribed as a simple query, but you will achieve same by making a function.

1. Get the numbers through loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValueIndex INT

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString)

WHILE @NumericValueIndex > 0

BEGIN

SET @AlphaNumericString = STUFF(@AlphaNumericString, @NumericValueIndex, 1, '' )

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString )

END

SELECT ISNULL(@AlphaNumericString,0) 'Numeric Values'

OUTPUT

2. Get the numbers without loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValues VARCHAR(MAX) = ''

SELECT

@NumericValues =

@NumericValues +

CASE WHEN NumericValue like '[0-9]' THEN NumericValue ELSE '' END FROM

(

SELECT

SUBSTRING(@AlphaNumericString,NumericValue,1) NumericValue

FROM

(

SELECT

number NumericValue

FROM

master..spt_values

WHERE 

TYPE = 'p' 

AND number BETWEEN 1 AND LEN(@AlphaNumericString)

) T

) T

SELECT ISNULL(@NumericValues,0) 'Numeric Values'

OUTPUT

Limitation
There is a limitation in 2nd approach, the table “master..spt_values” contains only 2048 rows value. So if you want to get the numbers from the very big text contains above 2048 characters, then this approach is not suitable.
To over come this limitation, you can use “Tally Table” instead of “master..spt_values”. Replace “master..spt_values” with “Tally Table”.
Want to get more information about Tally Table? Click here.


Conclusion
If you have the need of numeric values only from less characters of 2048, then no need to process for “Tally Table”. Just use the “master..spt_values”.