Getting the E-mail name from Windows Active Directory (AD) code

Getting the E-mail name from Windows Active Directory (AD) code.

Recently I needed to get the e-mail address for a person to be outputted on a SSRS report. The user table did not have the actual address and the name would not be possible to concatenate somehow. What did I have? It was only the long Active Directory (AD) data. How can I parse through this to get the name and concatenate on the address for this report? We will want to use T-SQL to extract the “CN” which stands for “Common Names”.

Please walk through this example with me!

Say you have the field “distinguished Name” in your table with:

DistinguishedName
------------------------
CN=joe.smith,OU=factory,OU=Email,DC=companynet,DC=company,DC=com

The code you can use to extract ”joe.smith” and concatenate ‘@company.com” is:

SELECT SUBSTRING((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)),4,LEN((LEFT(U.DistinguishedName, CHARINDEX(',', U.DistinguishedName + ',') -1)))) + '@company.com’ AS "Email Address"

FROM (table)

Your result will equal:

Email Address
-----------------
joe.smith@company.com

Moving SQL Server System Databases to New Drives

It may become necessary to move an already installed SQL Server from one file storage location to another. It is necessary to move the system and other databases. In this case, we are moving the databases from the M:\ drive LUN to three drives, separated by function. This equates to:

• M:\sqlserver\backup to B:\
• M:\sqlserver\data to D:\
• M:\sqlserver\log to L:\

While this is entirely possible, it is necessary to follow a few steps below to cleanly move the SQL Server instance to have storage in a different location. This example uses SQL Server 2008R2, so be sure to take note of the version when clicking through the registry.

• Start the SQL Server Configuration Manager located in the programs section , under the version of SQL Server.
• Shutdown SQL Server Instance.
• In Configuration Tools for SQL Server Instance, put this in for startup parameters:

-dD:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;

-eD:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;

-lL:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• In the registry:

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer

Set default paths to:

B:\, L:\, D:\

o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Replication

Set Working Directory to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

o Change default location in registry for Data Root:

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server/100/MSSQL10_50.MSSQLSERVER/Setup/

Set SQLDataRoot to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL

Set FullTextDefaultPath to:

D:\MSSQL10_50.MSSQLSERVER\MSSQL\FTData

• Change default location for SQL Agent error file:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent/

Set ErrorLogFile:

M:\sqlserver\data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

To

D:\ MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT

• Update Dump Directory to :

D:\MSSQL10_50.MSSQLSERVER\MSSQL

• Check all Directories in the SQL Configuration manager. As an example, Dump Directory should be set to: D:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\

• Start upSQL Server Instance
• Move Other System Databases:

USE master;

GO

--test change startup script in SQL Config MGR to point to new database/location.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

--MOVE SYSTEM DBs

--MOVE MODEL

USE master;

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf' )

ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

USE master;

GO

--MOVE MSDB

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

--MOVE TEMPDB

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf');

GO

• Restart SQL Server Instance!
• Check locations

--Check

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'model');

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

• Update Backups/Maintenance Jobs…they all point to the old location.
• Remaining Databases, such as the Report Server databases, can be detached from the old location, copied to new location and attached.

Moving a SQL Server Database with SSIS's Transfer SQL Server Objects Task

On a recent client project, I was asked to move a backup file of a SQL Server 2008 database to another database. On the surface, that is about as simple of a task a SQL Server DBA can be asked to do. There were ten databases. No big deal I thought.

The databases were all for websites and were to be moved from one hosting server to another. The hosts will remain nameless to protect the innocent!

On the destination server, the issue became evident very soon. I created the blank database and I uploaded the .bak file and tried to restore. No dice, it can only restore its own backups, when a database is backed up using the web tool. I then tried creating the blank database then going in through SSMS. This was possible using the connection parameters. I figured I would outsmart the system by restoring the backup file with a replace. It said I had no rights to do so.

I was stuck with no answer. I decided to see if I could import tables somehow or doing some other task. I then though I would try SSIS (SQL Server Integration Services), where I hit pay dirt! The data was more important than the actual database information, so that is what I concentrated on.

SSIS offers a control flow item called “Transfer SQL Server Objects Task.” The obvious question is why not use a “Transfer Database Task”? Simple, the database task is good, but requires destination file source which I did not have. The “Transfer SQL Server Objects Task” was perfect.

First, you choose the source and destination connections. Next, choose the following options to move up the database contents.

I cleared out the connection information, but this is no simpler than any other SQL connection. You can choose more items than my example, but if all you need is the data transferred, this should do it.

So in summary, this task will help you move databases when you do not have the rights to move the data files (attach database) or restore (restore database).

SQL Server Restore Issue

Cannot open backup device 'C:\PATH\*.BAK'.

Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

I added “NETWORK SERVICE” to the file root with the following permissions and the resore worked fine.