Archive for December, 2011

How to change Server Collation in SQL Server 2008

December 12, 2011 Leave a comment

How to change Server Collation in SQL Server 2008

Database Mail Configuration script

December 12, 2011 Leave a comment

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I’m sharing here.  

Setup Script

Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and

— Enable Database Mail for this instance

EXECUTE sp_configure ‘show advanced’, 1;


EXECUTE sp_configure ‘Database Mail XPs’,1;




— Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = ‘Primary Account’,

    @description = ‘Account used by all mail profiles.’,

    @email_address = ‘’,

    @replyto_address = ‘’,

    @display_name = ‘Database Mail’,

    @mailserver_name = ‘’;


— Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = ‘Default Public Profile’,

    @description = ‘Default public profile for all users’;


— Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = ‘Default Public Profile’,

    @account_name = ‘Primary Account’,

    @sequence_number = 1;


— Grant access to the profile to all msdb database users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = ‘Default Public Profile’,

    @principal_name = ‘public’,

    @is_default = 1;



–send a test email

EXECUTE msdb.dbo.sp_send_dbmail

    @subject = ‘Test Database Mail Message’,

    @recipients = ‘’,

    @query = ‘SELECT @@SERVERNAME’;


Categories: Mail Profile

Backup and restore command Status

December 12, 2011 Leave a comment

Whenever we are doing the restoration and backup task for big databases that time it could be tough for us to give a ETA to business owner. The below command help us to get the status at percent level of completion along with estimate completion_time.




select,percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time, Getdate() as now,

datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo,

start_time, command

from sys.dm_exec_requests req

inner join sys.sysdatabases d on d.dbid = req.database_id


Categories: Backup and Recovery

Shrinking all the databases log file at a moment.

December 12, 2011 Leave a comment

In our day to day DBA job log file full error is common mater bit it is a very tedious job if a SQL instance hold lots of databases  and it is quite tough to figure out which database log file is full. The impact of log file full is very pathetic cause database can be suspected due to less space. To shrink the log file below I used one command which could be truncate the inactive space for all database within a minute. The script is not destroying the log chain so you can use in replication server also and configure as a SQL job


Shrink Command for all databases:


sp_msforeachdb “use [?];  dbcc shrinkfile(2,TRUNCATEONLY) WITH NO_INFOMSGS ”


SQL server Job related some Important query which help us to get the details quickly.

December 12, 2011 Leave a comment

/*Failed jobs report. This query lists the name of all jobs that failed in their last attempt:*/


      SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0



/*Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!*/



      SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name


/*Running jobs. Need to know what jobs are still running?


Ever come into work on Monday morning to find several critical jobs having “piled up”,

running way to long, or hung up? This query lists those running queries (whether normally scheduled or not).

This procedure call is good for making sure your Distribution or Merge agent job is still running too.

I make a call to sp_get_composite_job_info (loading in a bunch of NULLS, and a “1” to indicate running jobs):*/



      msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

How we can use Varchar in encryption column

December 12, 2011 Leave a comment

This method is little different here we have to work with View and will send the one more method like encryption by stored procedure technique. Below I have mentioned the total demonstration.



The goal of the encryption process is to abstract the tables with cell level encryption by over-laying them with views.  This allows the application (with the proper permissions) to continue to work with the encrypted data seamlessly via the normal development methodologies without having to consistently write the code to decrypt / encrypt the columns (cells).  This process is the generally the same for new applications and existing applications that need to be modified to encrypt data with the key difference being the requirement to open the keys for new applications to provide additional security.


The SQL Server encryption functions return varbinary data, so all columns to be encrypted need to be changed to varbinary.  The use of Views to over-lay the tables provides an abstraction layer so the data types can be properly viewed for data modelers, developers, administrators, analysts and other end users.  To encrypt a SSN, varchar(9) field requires the column in the base table to be implemented as varbinary(200). 


Users that access the views, who do not have permissions to open the proper key(s) will not receive an error, but the column will not be decrypted and will be populated with nulls.


Database developers will need to work closely with the data modeling team and the administrators to ensure the data types, views, encryption keys, backup and restore of keys are implemented properly.  Data is not recoverable if the backup of keys and databases are not implemented properly.


Guidelines for implementing cell level encryption.


All tables that include an encrypted cell (column) follow the normal standard naming conventions with the addition of an underscore and the word base to the table name {table name}_base.  All columns follow the standard implementation with the exception of those to be encrypted, which must use a varbinary data type.  The length of the varbinary column is determined by the max length returned from the encryption function. 


A view is created that follows the normal standards, except the name is implemented off the base table by dropping the underscore and the word base that was used to name the table (this allows legacy code to continue to work).  The view will make use of the decryption function and the proper Cast and Convert statements to manipulate the varbinary column to the correct un-encrypted data type.


Inserts, updates and deletes are handled in the normal manner, with the tsql statements being executed against the view, NOT the underlying tables.  “Instead of Triggers” are created on each view to handle the Inserts, updates and deletes.  This provides an additional layer of abstraction so that consistency can be maintained with the code.


A short tsql script example:


–Database Creation


Create database encrypt_test;





use encrypt_test;




  –Created masterkey and Certificate


Create master key encryption by password = ‘knights12$gzmlauncher#1@%dmissionisclear*()’




Create certificate cert_sk with subject = ‘Certificate for accessing symmteric keys – for use by App’


–SQL SERVER can use the following algorithms in encryption:DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.




  CREATE SYMMETRIC KEY sk_encrypt_test






  —Table Creation


create table dbo.Client_Base


      ( client_Id int Identity(1,1) primary key,


            ssn varbinary(200),


            Amount_due varbinary(200),


            Comments varchar(1000)






  –Created View with a convert clause so we can use


create view dbo.Client




      Select Client_ID,




                  Null,SSN,1,convert(varchar(10),client_Id))) AS SSN,




                  Null,Amount_Due,1,convert(varchar(10),client_Id)))) AS Amount_Due,




      From dbo.Client_Base;






select * from dbo.client;




—Created Insert Trigger


create trigger dbo.trg_client_insert

      on dbo.Client










            Declare @Client_ID int, @SSN varchar(9), @Amount_Due money,


                  @Comments varchar(1000);


            open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk;


            DECLARE cur_Client CURSOR FOR

                  SELECT      SSN,Amount_Due,Comments FROM INSERTED;


            OPEN cur_Client;


            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments;


            WHILE @@FETCH_STATUS = 0




                  Insert into dbo.Client_Base (Comments) values (@Comments)


                  set @Client_Id = scope_identity()


                  Update dbo.Client_base


                        set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),@SSN,1,Convert(varchar(10),@Client_ID))


                              ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),@Amount_Due),1,Convert(varchar(10),Client_ID))


                        where Client_ID = @Client_ID




            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments




            CLOSE cur_Client


            DEALLOCATE cur_Client        


            CLOSE SYMMETRIC key sk_encrypt_test






  –Created Update Trigger


create TRIGGER trg_Client_Update ON dbo.Client










      open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk


      Update Client_base


            Set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),i.SSN,1,Convert(varchar(10),i.Client_ID))


                  ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),i.Amount_Due),1,Convert(varchar(10),i.Client_ID))


            FROM Client_Base


            inner join inserted i on Client_Base.Client_ID = i.Client_Id




      CLOSE SYMMETRIC key sk_encrypt_test






  –Created Delete Trigger


create trigger dbo.trg_Client_Delete

      on dbo.Client








      Delete Client_Base


      from Client_Base db


      inner join deleted d on db.Client_ID = d.Client_ID








— MUST OPEN KEY or all insert statements will FAIL




DECRYPTION BY PASSWORD = N’knights12$gzmlauncher#1@%dmissionisclear*()’;




–Insert Value


Insert into dbo.client (ssn,amount_due,comments) values


      (‘123456789′,256.01,’This is a test of encryption’)




select * from dbo.client;


select * from dbo.client_base;




  –Update the value


Update Client


      set ssn = 987654321,


            Amount_Due = 100


      where ssn = 123456789;




select * from Client;




Close Master Key;

Categories: Encryption

Is it possible that sql 2005 backup can restore in sql 2000?

December 12, 2011 Leave a comment



As per Microsoft there is no workaround. SQL Server 2005 does not support downgrades or restores  to SQL Server 2000. But if server contains SQL 2000 and 2005 both instances then we can restore the SQL 2005 backup on SQL 2000 instance.



There is no “Restore” functionality, but there is a workaround to copy 2005 databases to 2000:

Right-click on DB -> tasks -> generate scripts
select DB and click “script all objects…”, hit next
select any options you want, specifically changing “script for server version” to SQL Server 2000
next through and run the script

Now just export data from the 2005 database to the newly-created 2000 database.
Right-click on DB -> tasks -> export…
set source and hit next
set destination and hit next
select “copy data from one or more tables”, hit next
select all, check “optimize for many tables” and “run in a transaction”
you may have to edit each table mapping and check “enable identity insert”
next through to finish

Categories: Backup and Recovery