Monday, October 10, 2011

Introduction to Databases - Stanford online course

Introduction databases online course has been officially launched today by Stanford. This will be the closest I will ever get to be in a Stanford class, though virtual. I won't miss this opportunity :)

Thursday, July 14, 2011

Delete backup files older than X days - SQL Server Backups

We use Litespeed for backup compression on one of our production servers and our backup file can only store 2 days worth of full backups and log backups. I have tried scheduling a job to delete the backup files using SQL Server maintenance plan - while it works well with the native backup files, for some reason the job doesn't delete litespeed backups. I did some search and it seems that I am not alone.

I found this thread which has an alternative way of doing this. It is simple. Create a bat file with the below -

forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"
 If you would like to delete files that are older than 2 days, then it would look like this
forfiles -p "E:\Backups" -s -m *.* -d -2 -c "cmd /c del @path"
For this to work, you need to have forfiles.exe in WINDOWS\systems32 folder.

There are a couple of other ways to delete files older than x days - powershell and VB script.

EDIT: Ah, How did I miss this before? here is a detailed article on mssqltips:

Saturday, June 11, 2011

PHP and SQL Server

Via Pinal's Blog:

The PHP on Windows and SQL Server Training Kit includes a comprehensive set of technical content including demos and hands-on labs to help you understand how to build PHP applications using Windows, IIS 7.5 and SQL Server 2008 R2. This release includes the following:

PHP & SQL Server Demos

Integrating SQL Server Geo-Spatial with PHP
SQL Server Reporting Services and PHP

Friday, February 18, 2011

Primary Key Constraint - duplicate values

Msg 1505, Level 16, State 1, Line 1The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PTO_CHATTER' and the index name 'pk_chatter'. The duplicate key value is (100283).Msg 1750, Level 16, State 0, Line 1  Could not create constraint.   See previous errors.The statement has been terminated.

One of our developers was trying to make a certain column in a table a primary key and encountered the above error.  The column had duplicate values in it. A column which has duplicates can not be a primary key. Here is a query to find whether the column has duplicate values.

SELECT EMPLID,
COUNT(*)
FROM
dbo.PTO_CHATTER
GROUP BY EMPLID
HAVING
COUNT(*) > 1