Database backup and restore failed on mapped network drive

During my job at National Database and Registration Authority of Pakistan (NADRA), i was accustomed to create database backups directly on mapped network drives. We have a domain configured at NSRCs with SQL Server 2000. Fore servers with single disk, it becomes very efficient by preventing the local server disk IO. Now while working with SQL Server 2005 installed on windows server 2003 work group. i was required to create an adhoc backup during peak hours. For optimized IO i tried to use mapped network drive for creating backup on it directly. But surprisingly mapped network drive was neither available in SSMS backup location explorer nor through T-SQL backup statement. Error message was being generated.

Same statement was working successfully for any location other than mapped network drive. Keeping in view my previous experience with SQL Server 2000 on domain i was not expecting any problem in this task. However after some time i realized that network drive is not available as backup device.

Next option was BOL and Google. There i came to know that mapped network drives are available for backups only when your SQL Server service is running under domain account that has access to mapped network drive.

Conclusion
Without a privileged domain account as start up account of SQL Server service, mapped network drives cannot be accessed from within SQL Server context for backup or restore process.  

Select and Insert rows in same table with some different values

A requirement may arise when we have to select specific rows and insert these in same table with some values different. There may be several scenarios for it. Following example would cover the requirement and process involved to complete the task.

We have a table with information about language skills of employees. We are required to select records of an employee and insert these for different employee. All other field values are same except the employee number that we would provide explicitly in script. Consider following script

-- Craete table for demo

CREATE TABLE EmpLanguages

(ID SMALLINT IDENTITY(1,1),

CNIC CHAR(13), Lang VARCHAR(50),

OralSkill TINYINT, WrittenSkill TINYINT)

GO

-- Populate table with sample data

INSERT INTO EmpLanguages

SELECT '1111111111111','Urdu', 4, 4

UNION ALL

SELECT '1111111111111','Arabic', 3, 2

UNION ALL

SELECT '1111111111111','English', 3, 3

UNION ALL

SELECT '1111111111111','Pashto', 3, 2

GO

-- verify the data

SELECT * FROM EmpLanguages

GO

We have values for employee with CNIC 1111111111111, that we have to insert for employee with CNIC 2222222222222. First of all get columns list instead of manually writing these in query

-- Get columns

SP_HELP EmpLanguages

GO

Now copy the columns and construct following script.

-- Insert same records for different employees

INSERT INTO EmpLanguages

SELECT '2222222222222' ,Lang, OralSkill, WrittenSkill

FROM EmpLanguages

WHERE CNIC = '1111111111111'

GO

-- Again verify the data

SELECT * FROM EmpLanguages

GO

Verification of data shows that task is complete. This method may be applied for various other scenarios with little modification.

Role management tool to install or configure .Net framework on Windows Server 2008

While installing SQL Server 2008 on Windows Server 2008 R2, i got following error message at initial stage in process.

Windows was up to date and required version of .Net framework was installed on it. Opened Server Manager for Windows Server 2008 and clicked on features in left panel. Then by clicking Add Features link in right panel another panel was opened with several features available there for installation. I checked the box for my required .Net framework feature and Clicked on Next button to proceed with the installation.

The .Net feature was installed and SQL Server 2008 installation ended successfully.

Not able to connect SQL Server 2008 on network

Recently i deployed SQL Server 2008 along with SP3 on Windows Server 2008 R2. All installation and later configuration were managed through Remote Desktop. Later while trying to connect the instance on LAN from my desktop PC i got following error message

The generated error message is related to network access of instance. Back through remote desktop, first of all i checked for firewall issues but every thing was fine for that. Then after little exploration of SQL Server configuration manager i observed that TCP is disabled. I enabled the TCP in configuration manager as shown in snap

A warning box appeared suggesting the SQL Server service restart for changes to take effect.

I restarted the service from configuration manager and again tried login from desktop PC on LAN. This time login was successful without any error message.
So make sure to enable TCP-IP for accessing the SQL Server 2008 instance on LAN. TCP-IP is by default disabled in SQL Server 2008 installation.

Copy and paste among multiple lines in SSMS

SQL Server Management Studio (SSMS) is my favorite tool and m fan of it right from its start with SQL Server 2005. With every new release SSMS got more coll features for DBAs. Recently i came across a very useful feature in SSMS query pane. It allows to select specific content from multiple lines in query pane. This feature is extremely helpful when we are required to select table names, column names or even values from the script. The selected values may be deleted or copied as required. And may be paste in same alignment any where among the text.

Basically the technique is to hold the ALT key and drag the mouse on required text in SSMS query pane. Following is an example of its usage and you may use it for many tasks in more creative manner.

Suppose i am required to select just table names given in three seperate T-SQL statements. I would press ALT key and start draging the mouse on required data. The selection would be as shown in following snap

Now we may copy data for onwards use. It is notable that during the paste process data would preserve its alignment that was during the copy process. Spend some time to play with this cool feature and make use of it.

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.

However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

1. Login is requesting to connect from inside the application code
2. Login is requesting from no where else but web server (application server)

 My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

CREATE TRIGGER [RestrictSSMSLogIn]

ON ALL SERVER WITH EXECUTE AS 'AppUser'

FOR LOGON

AS

BEGIN

DECLARE @ip VARCHAR(16)

SELECT @ip = client_net_address

FROM sys.dm_exec_connections WHERE session_id = @@SPID

IF ORIGINAL_LOGIN()= 'AppUser' AND

((SELECT TOP 1 Program_Name

FROM sys.dm_exec_sessions

WHERE is_user_process = 1

AND original_login_name = 'AppUser'

Order By Session_Id Desc)

<>'.Net SqlClient Data Provider'

or @ip <> '192.168.1.202') -- Provide IP address of web server here

ROLLBACK;

END

GO

ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER

GO

In this way the login used for application is restricted to access only from web server through application code only.

Multiple identity columns specified for table %'. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.

Multiple identity columns specified for table '%'. Only one identity column per table is allowed

SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

CREATE TABLE identTest

(ID1 SMALLINT IDENTITY(1,1), 

ID2 SMALLINT IDENTITY(1,1))
GO

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

1. Use trigger to insert values in second column
2. Configure second column as computed column and reflect values from identity column