Update datatype of primary key in SQL Server table


To generate the tsql commands that we can use to alter the primary key datatype.

My way of achieving it

Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script.  The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).

The script requires the instance name, database name and table name

$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”

The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.

IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.

Very important is to check the script output before executing it.

The script can be downloaded from here

SQL Server database backup – generating backup commands to multiple locations


To generate the backup commands for a full backup or a differential backup

My way of achieving it

Using xp_cmdshell and powershell. More often than we would like due to growth of the databases and creation of other databases the backup of our databases fails because of insufficient free space. Although most of the time the solution is to increase the disk where we save our backups there are exceptions from this rule

  • we really need to take a full backup or a differential one because we need to create a new log chain.
  • we really need to take a backup as a prerequisite of a change
  • we really need to take a full backup in order to free the space that other backups used to recover the database occupies on disk.  Some of the backup retention implementation will keep on disk all the backups since the last full backup.
  • we really need to take the backup according to the configured schedule, this schedule is created in such a way that will allow us to recover the database in some specific terms.

The script is generating the backup commands for locations found in backup history but also on the locations where SQL server keeps its database files.

The script requires

  • database name
  • backup type, full or differential
  • native or other type of backup, the script has syntax for litespeed but it can be modified to generate the commands using the syntax of your backup tool

The script identifies the size of the last full backup or differential one and uses that size as an estimation for the next backup.

The output of the script is shown below.



The script can be downloaded from here