:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Jun 09 / 2010
  • 0
Backup and Restore, dbDigger, Encryption, Security and Permissions, T-SQL Scripts

The backup set holds a backup of a database other than the existing ‘*’ database.

Recently i applied password protected backups for my SQL Server databases as explained in my article Using passwords with SQL Server database backup files. Backups and file removal task worked successfully in test environment. Before moving the encrypted backups setup to production systems i just performed a hands on activity to be smooth with restore process of password protected backups.
I created password protected full backup of AdventureWorks with following script to work with restore scenarios.

After creating backup successfully in ‘D:Testing’ directory, i planned it to restore over an existing database AdvTest.

 

It is notable that password protected backups may not be restored through SSMS, so we have to use T-SQL scripts for this purpose.

I got password used for backup by following select statement

select replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips'
GO

Using following script, i tried to restore AdventureWorks backup on existing AdvTest database

Use master
GO
RESTORE DATABASE AdvTest
FROM DISK='D:TestingAdventureWorks-2010-05-31 15-04-53.bak'
WITH MEDIAPASSWORD='2010-05-31-MSSQLTips'
GO

Restoration of password protected backup failed on existing database failed with following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdvTest’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

After some research on issue i came to know a work around. It is required to use WITH REPLACE option for restore of backup on another database with different name. So i modified my script as

Use master
GO

RESTORE DATABASE AdvTest
FROM DISK='<span style="color: #3333ff; font-family: courier new; font-size: small;">D:TestingAdventureWorks-2010-05-31 15-04-53.bak</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">'
WITH REPLACE, MEDIAPASSWORD='</span><span style="color: #3333ff; font-family: courier new; font-size: small;">2010-05-31-MSSQLTips</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">',
MOVE 'AdventureWorks_data' TO 'D:DB FilesAdvTest.mdf',
MOVE 'AdventureWorks_log' TO 'D:DB FilesAdvTest_log.ldf'

Here i have specified the data and log files of source and target. It is important to note that if database is being restored on same database to which it belongs then move option is not required to be specified.
For example if we are required to restore our AdventureWorks backup on AdventureWorks database then following script would work

Use master
GO

RESTORE DATABASE AdventureWorks
FROM DISK=’D:TestingAdventureWorks-2010-05-31 15-04-53.bak‘
WITH REPLACE, MEDIAPASSWORD=’2010-05-31-MSSQLTips‘,
GO

To get logical and physical file names of any database for use in above scripts, use following command

Use AdvTest
GO

sp_helpfile
GO
  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to delete duplicate rows from a table

A DBA may be often required to delete duplicate rows from a SQL Server database table. Several methods can be used keeping in view the resources and exact situation. Here I will create a specific scenario. After going through this scenario, you will be able to remove the duplicate rows in any given scenario on ground.
Run following script to generate duplicate data in a table named “dups” created in any database that you select. Here I will use “pubs”.

 
-- Create table in any database  
 create table Dups  
 (col1 varchar(50),  
 col2 varchar(50),  
 col3 varchar(50))  
 GO  
 -- Populate the table for duplicate rows  
 INSERT INTO dups  
 SELECT 'Val1','val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val1','Val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val6','Val7','Val6'  
 GO  

Table is created and populated with duplicate rows in it now.
Total rows are 9
Distinct rows are 5, which we have to preserve at the end.
Now we have to remove all duplicate rows, leaving behind just 5 distinct rows in three columns.

Execute the following script to complete the operation

 
-- Remove duplicate rows  
 ALTER TABLE dups  
 ADD ser SMALLINT PRIMARY KEY identity(1,1)  
 GO  
 DELETE FROM dups  
 WHERE ser not in (SELECT max(ser) FROM dups  
 GROUP BY col1,col2,col3)  
 GO  

At the end now we have all duplicate rows removed and our table is with an identity column “ser”. You may also drop this column by using

 -- Drop the temporary column created  
 ALTER TABLE dups  
 DROP COLUMN ser  
 GO  

but for that you have to first drop the constraint attached with it. As in my case it is as follows

 -- Drop PK constraint  
 ALTER TABLE dups  
 DROP PK__dups__286302EC  
 GO  

So it is over. You may get include any number of columns in your distinct rows wish list. You may modify the ‘group by’ parameters to meet any number of columns to consider in duplicate row . Query uses the magic of ‘group by.’ Here it is appropriate to mention that “distinct” and “group by” both produce same result and almost same resource utilization. But one of important difference between both is that group by makes you able to apply an aggregate function on each group of data separately. That fact has used here with “max” in our duplicate removal query.

Click here to read an excellent article on this topic by Arshad Ali. It covers different ways to deal with duplicate rows.

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger, T-SQL Scripts

Analyze all identity column values in SQL Server database

Identity columns are created to auto generate keys for records. These keys are best suitable for primary keys. If any value crosses the max value for identity column data type then following over flow error may occur, as here in case of tiny int

Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting IDENTITY to data type tinyint.

Here is a script to generate list of all identity columns in database along with their data types and maximum value consumed

 
Use AdventureWorks  
 GO  
 SELECT  
 SCHEMA_NAME( OBJECTPROPERTY( c.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME],  
 OBJECT_NAME( c.OBJECT_ID ) AS [TABLE NAME], c.NAME AS [COLUMN NAME],  
 t.name as [Column data Type],seed_value,  
 increment_value,last_value as CurrentMaxValue  
 FROM SYS.identity_COLUMNS c inner join sys.types t on c.user_type_id = t.user_type_id  
 WHERE COLUMNPROPERTY(OBJECT_ID, c.NAME, 'IsIdentity') = 1  
 AND last_value IS NOT NULL  
 order by last_Value Desc  
 GO  

Analyze identity columns in SQL Server database
Now you may analyze the list of identity columns. And any column that is near to fully consume its maximum value may easily be tracked here.

  • Jul 02 / 2009
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Triggers

Enable or disable a DML Trigger through T-SQL and SSMS

I have created triggers to track changed data in various fields of several tables. Such triggers work at table level and are attached with table. While performing schedule updates through SQL Server agent jobs, i disable the triggers in the stored procedure that is performing given updates. It prevents the extra usage of triggers and also optimizes the space by skipping entries by such triggers.
Here is syntax to disable and enable trigger1 on table1

-- Disable the trigger  
 ALTER TABLE table1 DISABLE TRIGGER trigger1  
 GO  
 -- Enable the trigger  
 ALTER TABLE table1 ENABLE TRIGGER trigger1  
 GO  

Similarly another syntax may be used to enable and disable our imaginary trigger trigger1 on imaginary table table1 in this case

 
-- Disable the trigger  
 DISABLE TRIGGER trigger1 on table1  
 GO  
 -- Enable the trigger  
 ENABLE TRIGGER trigger1 on table1  
 GO  

Same task may be performed very easily through SSMS. Suppose trigger trigger1 is created on table table1. And we have to disable and enable it through SSMS

  • Open SSMS
  • Go to table on which trigger is defined
  • Open triggers folder under table

Disable and enable trigger through SSMS

  • Right click on trigger and click on Disable.

Disable trigger through SSMS

Confirmation dialog will be shown and icon of trigger will be slightly changed showing the change in trigger status. To enable the trigger through SSMS, again right click on disabled trigger and click Enable.

Enable trigger through SSMS

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status.

  • Jun 26 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

How to disable or enable SQL Server logon trigger

In my previous post we discussed process to implement logon trigger for different purposes. Later some time it may be required to skip the customizations implemented through logon trigger. So for this you may disable the logon trigger rather than to drop it.
If we want to disable the trigger [RestrictSSMSLogIn] that we created during previous post, following is T-SQL command to disable the logon trigger

 
DISABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  

You may use following T-SQL command to enable the logon trigger later at any time

 
ENABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  
  • Jun 25 / 2009
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict SQL Server login to connect from out side the application through logon trigger

There is a situation that I have a SQL Server login with rich rights in some of databases. It is used for connection through a .Net application. It is not assigned to any one for use in SSMS etc. I want to make sure that this login would not be used for connection to SQL Server out side the application. While looking at various possible options i ca,e to know LogOn triggers in SQL Server.
Logon triggers in SQL Server are used to carry out logon related customization and tasks. From onwards to SQL Server 2005 SP2, we can create logon triggers that are meant to be fired every time when a session is established to SQL Server. If a login is not successfully authenticated then logon trigger is not fired in that case.
Consider following example to implement the above requirement. We will restrict the user AppUser to connect to SQL Server through .Net application only.

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 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)  
 &lt;&gt;'.Net SqlClient Data Provider'  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

Trigger has been created and user ‘AppUser’ is no more able to connect to SQL Server through any means other than .Net application.
Logic implemented in if block may be modified to implement a large number of other customizations that are required.
Logon triggers are created in master database. And may be viewed in SSMS in server objects folder.

LogOn Trigger in SSMS

While trying to log in SSMS through logon trigger following message is generated, showing that created logion trigger is working fine.

LogOn Restricted through LogOn trigger

It is important to mention that be attentive while working with logon trigger, some wrong or missing conditions may block all SQL Server logins to log in to SQL Server. Best solution is that be logged in through any user in SSMS while testing logon trigger. If any problem occurs due to trigger you can use already logged in user to manage the situation.
Even if you do not have a log in connected and a problem occurs due to logon trigger then work according to this article.
Click here
to get T-SQL commands to enable or disable the SQL Server logon trigger

Consult us to explore the Databases. Contact us