Archive for February, 2009

How to verify the backup

Posted: February 23, 2009 in Sqlserver

Please use below query to validate the backup is good or not.

This is very important to make your job easy and safe, verify all your backups every week. There are so many methods to do that other then sql native method.

This query can help you to verify the backup are valid or not.


RESTORE VERIFYONLY FROM  DISK = N’d:\perfmonitor_backup_2009_02_23_133557_9980470.bak’


Below query can be helpful in finding a particular stored procedure along with its definition in any database.


Select routine_name as Proc_Name,Specific_Schema,routine_definition as Procedure_Code from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE=’PROCEDURE’ order by routine_name





I was asked to create the database growth script which can help us to forecast the growth of all the database of our productions server..

Main intention to build this script is to get the growth rate of the database which can be used to do space management.


/*Create a table where you want to manage the data*/


Use ABCDatabase

Create table tblDataBaseSizes 


 dbname sysname, 

 dbsize  nvarchar(13) null, 

 AsOnDate datetime 



/* Create a Stored procedure which can get all the database information and store in the table*/

Use ABCDatabase

Create procedure sp_GetAllDBSize 

@dbname sysname = NULL   — database name 


declare @SqlQuery_exec nvarchar(625) 

declare @ListDB bit 

declare @name sysname 

declare @low nvarchar(11) 

declare @tblDataBaseSizes varchar(600) /* the total description for the db */ 


set nocount on 


/*  List all database name */ 


if @dbname is null 

 select @ListDB = 0 

else select @ListDB = 1 


select @low = convert(varchar(11),low) from master.dbo.spt_values 

   where type = N’E’ and number = 1 


/*   Initialize tblDataBaseSizes from sysdatabases  */ 


insert into tblDataBaseSizes (dbname) 

  select name from master.dbo.sysdatabases 

   where (@dbname is null or name = @dbname) 


/* Check database access level */ 


  select @low = convert(varchar(11),low) from master.dbo.spt_values 

   where type = N’E’ and number = 1 


declare db_Cursor cursor global for 

 select dbname from tblDataBaseSizes 

open db_Cursor 

fetch db_Cursor into @name 

while @@fetch_status >= 0 


 if (has_dbaccess(@name) <> 1) 


   delete tblDataBaseSizes where current of db_Cursor 

   raiserror(15622,-1,-1, @name) 





   /* Insert row for each database */ 


   select @SqlQuery_exec = ‘update tblDataBaseSizes 

        set dbsize = (select str(convert(dec(15),sum(size))* ‘ + @low + ‘/ 1048576,10,2)+ N” MB” from ‘ 

         + quotename(@name, N'[‘) + N’.dbo.sysfiles) WHERE current of db_Cursor’ 


    /*Update insertion Data insertion date*/


    update tblDataBaseSizes set AsOnDate=getdate() where AsOnDate is null


   execute (@SqlQuery_exec) 


 fetch db_Cursor into @name 


deallocate db_Cursor 

/*Below queries can be used to execute the above created stored procedure*/

Exec  sp_GetAllDBSize

Select dbname, dbsize, asondate from tblDataBaseSizes


Create a sql job and schedule to run as per your requirement. In my case we have schedule it to run every week.



The tablediff.exe utility is used to compare the data in two tables, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:
  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema. 
  • Compare destination tables at multiple destination servers simultaneously. 
  • Perform column-level comparisons. 
  • Generate Transact-SQL scripts to fix discrepancies at the destination server to bring the source and destination tables into convergence. 
  • Log results to an output file or into a table in the destination database. 
C:\Program Files\Microsoft SQL Server\90\COM>tablediff  -sourceserver H1A-5F201-FDG3 -sourcedatabase perfmonitor -sourcetable counterdata -destinationserver H1A-5F201-FDG3 -destinationdatabase ReplicationDB -destinationtable counterdata
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
-sourceserver H1A-5F201-FDG3
-sourcedatabase perfmonitor
-sourcetable counterdata
-destinationserver H1A-5F201-FDG3
-destinationdatabase ReplicationDB
-destinationtable counterdata
Table [perfmonitor].[dbo].[counterdata] on H1A-5F201-FDG3 and Table [Replication
DB].[dbo].[counterdata] on H1A-5F201-FDG3 are identical.
The requested operation took 2.5 seconds.


Microsoft SQL Server 2005 supports up to 50 instances of the Database Engine on a single computer. The following features are installed when you select SQL Server Database Engine on the Components to Install page of the SQL Server 2005 Installation Wizard:

  • Database Engine 
  • Replication
  • Full-Text Search

The following additional features are options for many typical user scenarios:

  • Integration Services
  • Connectivity Components
  • Programming Models
  • Management Tools
  • SQL Server Management Studio 
  • AdventureWorks sample databases and samples 
  • Books Online