SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


Tip for Backing up User Databases

We all take a database backup as a first resort to recover from a damage occur during the disaster / server crash / for any other reason if require to recover or restore our data back to its previous stat as it was before the database server was crashed!! And for this we schedule a job or make use of database maintenance plan which will take a database backup be it Full / Differential or Log, and we all take a special care and consideration when we design the backup strategy!!

But what happened if one fine day when you need your database backup file to work well and it doesn’t!! What if it is corrupted!! And you have only a Full backup for that day?

Today I am going to discuss about the script that I have posted in the download section couple of days back; the script will not only perform a full database backup of all user databases but it will also verify the validation of the backup file!  It's a must to have a database backup strategy where in we do not keep multiple copies of database backup  and if one have one or two days retention policy. 

declare @int int ,@dbname varchar(22),@maxdbid int
declare @int int ,@dbname varchar(22),@maxdbid int
declare @bkpath varchar(25),@path varchar(50)
select @maxdbid= max(dbid) from master..sysdatabases
set @int = 0
set @bkpath='C:\SQLDB\backup\'
USE master
while (@int < @maxdbid)
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)  
if (@dbname != 'tempdb') 
  select @int as 'DBID',@dbname as 'Database'  
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path  
  print @path
  print @bkpath
  restore verifyonly from disk=@path
 set @int = @int + 1  

Check the line # 17, restore verifyonly from disk = @path is the line that will help us to overcome above issue. This is not a hidden code lying in a secret place somewhere but this is something that we may have missed in our code – I know most of you have already using this option for your database backup validation, how many of you are using this option? Or out of curiosity any other option!!

PS: This code is the very basic code that I have wrote initially, you may modify and use the script according to your requirement without any obligation.
Your suggestions welcome!

- Hemantgiri S. Goswami


SQL Server 2008 High Availability – A Book

I have no words to explain my excitement and happiness to share news with you all - my book on SQL Server 2008 High Availability is published!!
I have started authoring this book in May 2010 and as the time passes and gradually I move to the final chapter of the book, I was feeling like aha finally it is done!!! – Do you think so, it was this easy?
No, every publication house has their own set of rules to be followed while authoring a book and a process to be followed before the chapter or entire book is ready to be published i.e. 1st edit, 2nd edit of the chapter until final edit and the Packt Publication is no exception.
Since this is the first time I was authoring book I was having little or no idea about authoring a book. The editors from Packt Publication helped me a lot by helping me time to time with proper answers to my queries. I must admit that I could not be able to complete this book without support and encouragement from my family including my 2 year old daughter Dhruti.
I hope that you all will enjoy reading and learning from this book as I did while authoring and contributed my little part for the growth of my beloved SQL Server J
Let me tell you about this book:
1223EN_MockupCover The term High Availability means that the servers or systems that host or run the business-critical applications should be highly available 24 X 7. As the word it-self defines how important it is to make these applications and data available for end-users as well as business users, if this data is not available for a short time, it will be a big problem for both sets of users. Imagine a bank spread across the country and having a huge customer base. One fine day, their server crashes! If the bank relies only on backups, then it might end up losing approximately 15 to 30 minutes of data, depending on the backup strategy. Now the HA options related to SQL Server such as clustering, replication, log shipping, and database mirroring will help overcome this situation.
In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring, below are the chapters:
Chapter 1: Understanding Windows Domains, Domain Users, and SQL Server Security
Chapter 2: Implementing Clustering
Chapter 3: Snapshot Replication
Chapter 4: Transactional Replication
Chapter 5: Merge Replication
Chapter 6: Peer-to-Peer Replication
Chapter 7: Log Shipping
Chapter 8: Database Mirroring
Appendix A: Troubleshooting
Appendix B: External References
If you are interested to read more about the detailed / contents of each chapter you may find them here https://www.packtpub.com/toc/microsoft-sql-server-2008-high-availability-table-contents
And, here is the link for the sample chapter https://www.packtpub.com/sites/default/files/1223-chapter-1-understanding-windows-domains-domain.pdf

Hemantgiri S. Goswami


Download SQL Server Training Kit

Couple of months back I have wrote a blog article about the availability of Microsoft SQL Server Developer Training Kit which is very helpful resource for the SQL Server Developers, trainers and professionals to understand the improvements comes with the release of SQL Server 2008.
Recently, 18th January 2011 Microsoft has released the updated version of Microsoft SQL Server Developer Training Kit which has
  • # 31 Presentations (includes Slide decks, videos and transcripts)
  • # 27 Demos (includes installer scripts, videos and transcripts)
  • # 12 hands on labs (includes installer scripts)
The main highlight of the updated version is – BI Solution, yes you read it right. The version of developer training kit has the units for BI Solutions like:
  • Build your first Microsoft BI Solution with SQL Server 2008 R2
  • Introduction to BI Modelling Techniques
And all of this are free!!
Download the updated version of SQL Server Developer Training kit from here –> http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d
You may want to check the complete list of what is included in SQL Server Developer Training Kit and System Requirements before you begin download. 
Hemantgiri S. Goswami


Download Section for Script and free ebook

Dear Readers,

Good News!!

I have created a new section on this site from where you can download various scripts I have created and uploaded on my community website. Currently you can download scripts for:

  • To Find Missing Index
  • To get various SQL Server Property details
  • To search a given word from a string
  • Script which will take backup and verifies the backup set as well
  • Script which will give you an idea on how to perform transaction in smaller chunks
You may find them all at http://www.sql-server-citation.com/p/downloads.html, and I promise that I will surely upload some more scripts in coming future.

Hemantgiri S. Goswami | http://www.sql-server-citation.com/


welcome 2011

Dear Readers,

Wishing you all very happy and prosperous new year, may the new day bring you joy and happiness