Rename a Database and its MDF and LDF files in SQL Server

In Developing a solution or rebuild a solution some time we need to change database rename and it's data file. I found couple of solution in online . I am prefer to use T-SQL .

 
USE [master]
--Set Database to Single-User Mode
ALTER DATABASE [OLD_DB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

--Rename Database
ALTER DATABASE [OLD_DB] MODIFY Name = [NEW_DB]

--Set Database to Multi-User Mode
ALTER DATABASE [CSLMS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE




--Rename Logical File Names
ALTER DATABASE [NEW_DB]
            MODIFY FILE (NAME=N'OLD_DB', NEWNAME=N'NEW_DB')
ALTER DATABASE [NEW_DB]
            MODIFY FILE (NAME=N'OLD_DB_log', NEWNAME=N'NEW_DB_log')


-- Checking Physical name
SELECT      name, physical_name
FROM        [CSLMS].sys.database_files


-- Detach Database
USE [master]
ALTER DATABASE [NEW_DB]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

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

Now we rename the database physical files using Windows Explorer;

 
--Attach Database
USE [master]
CREATE DATABASE [NEW_DB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEW_DB.mdf'),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEW_DB_log.ldf')
 FOR ATTACH
  

Successfully execute above sql we can easily rename database name and data files.

Read More

Restore Sql server Database from .bak file using (T-Sql)

Database  has full backup .bak file. It can be restored using following two steps.
Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:\BaakUpFile.bak'
GO

Step 2:
If you want replase this existing Database
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\BaakUpFile.bak'
WITH REPLACE
Or
Use the values in the LogicalName Column in following Step.
----Make Database to single user Mode
ALTER DATABASE DataBaseName
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE DataBaseName
FROM DISK = 'D:\BaakUpFile.bak'
WITH MOVE 'DataBaseName' TO 'database location ... DataBaseName.mdf',
MOVE 'DataBaseName_log' database location ... DataBaseName_log.ldf'
/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE DataBaseName SET MULTI_USER
GO

If you want make new
RESTORE DATABASE DataBaseName
FROM DISK = 'D:\BaakUpFile.bak'
WITH MOVE 'DataBaseName' TO 'database location ... DataBaseName.mdf',
MOVE 'DataBaseName_log' database location ... DataBaseName_log.ldf'


Collect from : blog.sqlauthority.com

Read More

SQL server Connecting from Another Computer

Configure SQL Server to listen on a specific port

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.
2. In the right pane, double-click TCP/IP.
3. In the TCP/IP Properties dialog box, click the IP Addresses tab.
4. In the TCP Port box of the IPAll section, type an available port number. For this tutorial, we will use 49172.
5. Click OK to close the dialog box, and click OK to the warning that the service must be restarted.
6. In the left pane, click SQL Server Services.
7. In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.

To open a port in the Windows firewall for TCP access

Step 1

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine. Type 1433 for the default instance. Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Setp 2:

1. On the Start menu, click Control Panel.
2. In Control Panel, click Network and Internet Connections, and then open Windows Firewall.
3. In Windows Firewall, click the Exceptions tab, and then click Add Port.
4. In the Add a Port dialog box, in the Name box, type SQL Server "instanceName".
5. In the Port number box, type the port number of the Database Engine instance. Type 1433 for the default instance. Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Verify that TCP is selected, and then click OK.

Read More

Microsoft SQL Server 2005 Database Encryption Step-by-Step

This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.

This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)

Creating Master Key

Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.

Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.

SELECT * FROM sys.symmetric_keys

If there has been created a master key, you

will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,

Note that in SQL2008 (CTP6 or February CTP) sys.symmetric_keys view has additional columns:
name

principal_id

symmetric_key_id

key_length

key_algorithm

algorithm_desc

create_date

modify_date

key_guid

key_thumbprint

provider_type

cryptographic_provider_guid

cryptographic_provider_algid

It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.
You can use the below t-sql script code in order to create a master key in the sql database.

/*************** CREATE MASTER KEY *********************************/

IF NOT EXISTS (

SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'

)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'

GO

It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.

I'm going to deal with master key backup later in this tutorial.
You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.

If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.

Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.

Creating a Certificate

The second step for using encryption in a SQL Server database is creating the certificates that will be used for creating symmetric keys and encrypting database table column fields.
So creating a certificate is still a preparation step in the encryption process.
Encrypting table column values or sql variables is still a few steps ahead.
You can also view existing certificates in a MS SQL Server database by running a select query over sys.certificates view.

SELECT * FROM sys.certificates

You can run the below t-sql script to create a certificate.

/*************** CREATE CERTIFICATE *********************************/

IF NOT EXISTS (

SELECT * FROM sys.certificates WHERE name = N'PasswordFieldCertificate'

)

CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = 'Password Fields';

GO

You can also drop or remove an existing certificate from a database using the DROP CERTIFICATE certificate_name tsql syntax.

If you try to drop a certificate that is used during the creation of a symmetric key, etc. the following error message is going to be thrown by the SQL Server engine.

Msg 15352, Level 16, State 1, Line 2
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Creating a Symmetric Key

After the certifates are created in the sql database, next the symmetric key is being generated by executing a CREATE SYMMETRIC KEY SQL Server command.
Again, you can check sys.symmetric_keys view name fields whether a key already exists and declared in the current database.
The following sql code script is enough to create a symmetric key which will be used for encryption and for decryption in the sql database.

/*************** CREATE SYMMETRIC KEY *********************************/

CREATE SYMMETRIC KEY PasswordFieldSymmetricKey

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;

If you try to create symmetric key with a name that exists among the sys.symmetric_keyssystem view, the following error message will be displayed:

Msg 15282, Level 16, State 1, Line 1
A key with name 'PasswordFieldSymmetricKey' or user defined unique identifier already exists or you do not have permissions to create it.

In the above CREATE SYMMETRIC KEY t-sql statement one important point is the algorithm parameter that is specified in the command.
Microsoft SQL Server can use the following algorithms in encryption sensitive data.
DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256
As system administrators or database admins, one important note for AES_128, AES_192 and AES_256 is AES encryption algorithm can not be used on Microsoft Windows 2000 Servers and Windows XP operating systems. If you have a MS SQL Server instance running on a Win2k server, then it is better to create the symmetric keys using the TRIPLE_DES algorithm, for instance. Otherwise, your script will fail when it is run on a sql server which is installed on Windows 2000 servers and Windows XP computers since AES is not supported on those operating systems. You should consider this point while choosing an encryption algorithm for your SQL Server database applications.

Preparing Database Tables to Store Encrypted Data

Now it is time to create a table column field which will store or keep the encrypted values in it for us.
Since I'm working on AdventureWorks database which I have downloaded and installed as a sample database for MS SQL Server 2008 CTP 6, I'll try to alter a table in AdventureWorks db and add a new column for storing encrypted data.
Since password fields is not open or visible in Person.Contact table, I give up searching for a password field in any of tables in AdventureWorks. I want to encrypt EmailAddress data column values in Person.Contact table.
I'm adding a table column named EncryptedEmailAddress which is declared as varbinary data type and 256 bytes in length using the below ALTER TABLE sql command.
If this varbinary field is not enough in length or size to store the resultant encrypted data, the encryption sql commands will generate an error.

ALTER TABLE Person.Contact

ADD EncryptedEmailAddress varbinary(256);

GO

Now run the below select query and observe that the Encrypted e-mail address column is null. We have not updated this encrypted field yet.

SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact

Encrypting Sensitive Data

Here is the final step for encrypting a table field using MS SQL Server encryption algoritms and methods.
An encryption can be done on a string or binary value (in nvarchar, varchar, varbinary, nchar, char, binary sql data types) in SQL Server using the EncryptByKey t-sql function.
EncryptByKey encrypts a given data by using a symmetric key.
The necessary symmetric key information can be passed to the EncryptByKey function using the Key_GUID Transact-SQL function. Key_GUID returns the uniqueidentifier (GUID) of the symmetric key whose key name is specified in the Key_GUID function.
The output of the encryption function EncryptByKey is a varbinary with a maximum lenth 8000 bytes.

Since EncryptByKey t-sql command requires a symmetric key, if you execute the EncryptByKey command without openning the symmetric key, the EncryptByKey function will return NULL values during the encrypting calls.
Once the symmetric key is opened in a session, EncryptByKey will function properly in that session. This means as a sql server developer after opening the symmetric key in a session, you can then call the encryption and also the decryption functions more than once successfully.
Also, if you plan to encrypt data and write it on a table or store encrypted data in a stored procedure or in a user defined function (udf), you can open the related symmetric key once at the beginning of the stored procedure or user-defined function t-sql codes, and then in the following lines of sql codes you can execute the EncryptByKey and the DecryptByKey Transact-SQL commands.

/*************** ENCRYPT SENSITIVE DATA *********************************/

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey

DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

UPDATE Person.Contact SET EncryptedEmailAddress = EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), EmailAddress);

SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact

GO

Decrypting Encrypted Data

Of course, if you are encrypting your data in Microsoft SQL Server 2005 or the new version SQL2008 aka Katmai, you may also want to decrypt the encrypted data.
As well as encrypting, decrypting can be handled using t-sql commands and functions in SQL Server. DecryptByKey t-sql function decrypts data using a symmetric key definen in the current sql database.
You can call the DecryptByKey function passing the encrypted data in varbinary data type. Just as similar to EncryptByKey requires an opened symmetric key in the current session, DecryptByKey function also requires a symmetric key that has been opened in the current session before it is called.

Therefore, the t-sql codes below are executed before making any call to EncryptByKey or DecryptByKey t-sql functions.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey

DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

/*************** DECRYPT *********************************/

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey

DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

SELECT

EmailAddress,

EncryptedEmailAddress,

CONVERT(nvarchar, DecryptByKey(EncryptedEmailAddress)) AS 'Decrypted Email Address'

FROM Person.Contact;

GO

One last important point for decrypting encrypted data on SQL Server is that as a sql programmer or administrator, you should take care for the original data type that is encrypted and the target data type that the decrypted data is going to be converted.
Since DecryptByKey function returns data in varbinary data type up to 8000 bytes, if you convert this decrypted varbinary data to nvarchar sql data type you get different result when compared to decrypted varbinary data converted to varchar.
So, if you are encrypting nvarchar data, decrypt and convert it back to nvarchar. Same for varchar data type also. if you are encrypting varchar data, decrypt and convert it back to varchar. Otherwise, you will not get expected results from implemented decryption algorithm.

Let's look at the following select script where encryption and decryption takes place for both varchar and nvarchar data types.
You will see if nvarchar data is converted back to nvarchar then result is correct. Same is true for also varchar data.

SELECT

-- Encrypt 'Varchar' string and then decrypt encrypted data

CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), 'Varchar'))),

CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), 'Varchar'))),

-- Encrypt N'NVarchar' string and then decrypt encrypted data

CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'NVarchar'))),

CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'NVarchar')))

Using Parameters in EncryptByKey and DecryptByKey T-SQL Functions

I have used string values or database table column names in the previous examples. But sql variables can also be used for encryption and decryption functions.

DECLARE @sample_data nvarchar(MAX)

SET @sample_data = N'This is a step-by-step guide summarizing SQL Server Encryption'

SELECT

CONVERT(nvarchar(MAX), DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), @sample_data)))

In this step-by-step guide or SQL Server encryption and decryption tutorial, we have implemented SQL Server data encryption and encryption metodologies with sample t-sql codes in a simple application in Microsoft SQL Server 2005 and MS SQL Server 2008. Encryption and Decryption is an important aspect for database security in sql database development

Read More

Naming Conventions and Style of T-SQL

1. Use upper case for all T-SQL constructs, except Types:

SELECT MAX(MyField) FROM MyTable

2. User lower case for all T-SQL Types and usernames:
DECLARE @MyVariable int

3. Use Camel casing for all UDO’s:
CREATE TABLE dbo.MyTable
(
MyField int
)

4. Avoid abbreviations and single character names
--Correct
DECLARE @Counter int

--Avoid
DECLARE @C int

5. UDO naming must confer to the following regular expression ([a-zA-Z][a-zA-Z0-9]+) - in short don’t use any special or language dependent characters to name objects. Constraints can use the underscore character.

--Avoid

CREATE TABLE dbo.[User Information]

6. Use the following prefixes when naming objects:

• usp - User Stored Procedures

• svf - Scalar Valued Functions

• tvf - Table Valued Functions

• vi - Views

• FK_ - Foreign keys

• DF_ - Default constraints

• IX_ - Indexes

CREATE PROCEDURE dbo.uspMyProcedure AS (...)

CREATE FUNCTION dbo.svfMyFunction

(...)

RETURNS int

AS

(...)

CREATE FUNCTION dbo.tvfMyFunction

(...)

RETURNS TABLE

AS

(...)

CREATE VIEW dbo.viMyView AS (...)

7. Name tables in the singular form:

--Correct

CREATE TABLE dbo.Address

--Avoid

CREATE TABLE dbo.Addresses

8. Tables that map many-to-many relationships should be named by concatenating the names of the tables in question, starting with the most central table’s name.

9. Primary and Foreign key fields are postfixed with ID.

--Correct

CREATE TABLE dbo.[User]

(

UserID int NOT NULL,

AddressID int NOT NULL –-Foreign key

)

--Avoid

CREATE TABLE dbo.[User]

(

UserID int NOT NULL,

AddressFK int NOT NULL –-Fieldname indicates its use as a foreign key

)

10. Avoid naming fields in a way that indicates its use as a foreign key.

--Avoid

CREATE TABLE dbo.[UserAddress]

(

UserFK int NOT NULL,

AddressFK int NOT NULL

)

11. Name Stored Procedures as [schema] .[usp][Object][Operation].

When creating Procedures to wrap single INSERT/UPDATE/DELETE statements, operation should be Insert, Update and Delete respectively.

12. Always assign schema to UDO’s when defining.

--Correct

CREATE TABLE dbo.MyTable (...)

--Avoid

CREATE TABLE MyTable (...)

13. Always include the schema when referencing an object:

--Correct

SELECT * FROM dbo.MyTable (...)

--Avoid

SELECT * FROM MyTable (...)

14. Properly arrange statements: Either use one-liners without indentation or multi-liners with indentation. Don’t mix the two.

--Correct one-liner

SELECT * FROM dbo.MyTable

--Correct multi-liner

SELECT *

FROM dbo.MyTable

WHERE MyTableID IN

(

SELECT MyForeignTableID

FROM dbo.MyForeignTable

)

AND MyColumn > 1

--Avoid

SELECT *

FROM dbo.MyTable --Missing indentation

WHERE MyField > 1 AND --Misplaced AND

Myfield <>

--Avoid mixing multiline and singleline expressions

SELECT * FROM dbo.MyTable

WHERE MyField > 1

15. When creating local scope always indent:

BEGIN

(...)

END

16. When using parenthesis around multi-line expressions, always put them on their own lines:

--Correct

RETURN

(

(...)

)

--Avoid

RETURN (

(...) )

17. When using IF statements, always BEGIN new scope:

--Correct

IF(1 > 2)

BEGIN

(...)

END

ELSE

BEGIN

(...)

END

--Avoid

IF(1 > 2)

(...)

ELSE

(...)

18. Always create scope when defining Procedures and multi statement Functio

--Correct

CREATE PROCEDURE dbo.uspMyProcedure

AS

BEGIN

(...)

END

--Avoid

CREATE PROCEDURE dbo.uspMyProcedure

AS

(...)

19. When joining always identify all columns with aliases and always alias using the AS keyword.

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON U.AddressID = A.AddressID

--Avoid

SELECT U.Surname,

Street –-Missing alias

FROM Users U –-Missing AS

JOIN dbo.Address ON U.AddressID = dbo.Address.AddressID –-Missing Alias

20. Avoid joining in the where clause, instead use ANSI syntax for joining. Include the reference key last:

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON A.AddressID = U.AddressID

--Avoid

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U,

dbo.Address AS A

WHERE U.AddressID = A.AddressID –-Joins in the WHERE clause

21. Avoid using RIGHT joins – rewrite to LEFT joins.

22. When doing INNER JOIN’s, avoid using the INNER keyword:

--Correct

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

JOIN dbo.Address AS A ON A.AddressID = U.AddressID

--Avoid

SELECT U.Surname,

A.Street

FROM dbo.[User] AS U

INNER JOIN dbo.Address AS A ON A.AddressID = U.AddressID

23. When defining Procedures and Functions, include a commented Test Harness. Declare used variables for usage in testing. In Procedures include a transaction which is properly rolled back after checking values. Skip this step if the Procedure is a simple INSERT/UPDATE/DELETE with no logic besides that.

--Correct

CREATE FUNCTION dbo.tvfMyFunction

(

@MyParameter int

)

AS

/* TEST HARNESS

DECLARE @MyParameter int

SET @MyParameter = 1

SELECT * FROM dbo.tvfMyFunction(@MyParameter)

*/

(...)

--Correct

CREATE PROCEDURE dbo.uspMyProcedure

(

@MyParameter int

)

AS

/* TEST HARNESS

DECLARE @MyParameter int

SET @MyParameter = 1

BEGIN TRAN

SELECT * FROM dbo.MyTable –-MyTable before operation

EXEC dbo.uspMyProcedure(@MyParameter)

SELECT * FROM dbo.MyTable –-MyTable after operation

ROLLBACK TRAN

*/

(...)

--Avoid

/* TEST HARNESS

SELECT * FROM dbo.tvfMyFunction(1) –-argument not declared

*/

24. If you use designers to generate DML – reformat it using the design styles defined here. In effect it is disallowed

to check in DML from designers into a project repository.

Using designers to generate DDL however is allowed and encouraged.

25. Use comments only to illuminate things that are not obvious from reading the code.

Read More