Estimate Backup Size In Sql Server


When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.

Although it won’t be 100% accurate, SQL Server has a built in stored procedure that will get you pretty close. You can use the sp_spaceused stored procedure to show you the amount of space used in your database. Now keep in mind that a full database backup only stores the actual data/objects in the database. The unused space is not stored in the backup.

Estimate Backup Size In SQL Server

In the example above we call the sp_spaceused stored procedure from the database that we want to get the backup size for. This stored procedure will return a couple datasets with multiple columns. The column that you will look at is named reserved. This will show you a good estimate of how large your full backup file will be.

More Info
The @updateusage parameter, in the example above, tells the stored procedure to update the space usage statistics before returning the database size information. Passing TRUE in to this parameter will give us the most accurate size estimate.

SQL Server Rename Column


This article will cover how to rename a column in SQL Server. The function of a SQL Server rename column is not performed too often. Usually we do this when we roll out a new product and we want to make a column more generic.

SQL Server Rename Column (SQL Server 2005 and beyond)

They make this super simple. Just remember to put to single quotes around the parameters.

The above SQL Server rename column code snippet has a few parts to it. Here is the breakdown:

  • sp_rename – This is a system stored procedure will rename different types of objects in SQL Server.
  • ‘Animals.AnimalName’ – This is the tablename and column that I would like to rename.
  • ‘AnimalRealName’ – This is the new name that I would like to call the column.
  • ‘COLUMN’ – This is the type of object that you would like to rename is. To rename a column, always leave this value ‘COLUMN’.

If you would like to know how to do a SQL Server rename column for SQL Server versions prior to 2005, please leave a note in the comments.


How To Run SQL Scripts Via Command Prompt


Usually, when we run SQL scripts, it is done on an ad hoc basis.  We usually run one file at a time just once.  From time to time there is the need to run a set of SQL script files.  One common reason I’ve seen for doing this is to rebuild a database for testing.  If you had all of your create tables/stored procedure commands stored in a series of SQL script files, then you could rebuild your database fairly easily by executing the SQL files in sequence.

SQL Server provides a tool to do this called SQLCMD.

The sqlcmd utility lets you run entire SQL script files full of t-sql via command prompt.  Leveraging this tool, you can create a batch file to run the sql script files in sequence.

How To Execute A SQL Script File Via Command Prompt With SQLCMD

Although there are TONS of command line options for executing a sql script.  Just executing a sql script file with the basic options is very easy.

The above command uses the -i option to specify the input file of DropTables.sql.  Everything inside this file will be execute on the current database.

NOTE: When you create your SQL script files, I always recommend putting a USE statement at the top of your script.  This will ensure that you are using the correct database when your dropping and creating tables.

Run A Batch Of SQL Script Files

Now that you know how to run one SQL script file… running multiple should be easy.  All you need to do is create a batch file and run one script after the other.

Your batch file will look something like this:

What About Errors?

So what happens if the SQL script file throws an error?  The above commands I showed you will just ignore any errors.  If you want to capture your errors, you need to add the command line option /b.  This flag will tell the sqlcmd utility to stop processing the script and output a DOS ERRORLEVEL value.  You can then check the DOS ERRORLEVEL value after each execution.



SQL Server Trim


SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

Trim Text Inline

The functions to trim text are RTrim() and LTrim().

If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

You can use this new fancy SQL Server Trim function by calling it like this:

Option Recompile


Option recompile is one of those hidden gems when it comes to increasing stored procedure performance.  When used in the right spot, this can speed up a never ending stored procedure call to instantaneous.  If you don’t know what option recompile is… it’s just a query hint that can be added to the end of a sql query.

What Option Recompile Does

When option recompile is added to the query hints, it does a couple things.  The first thing that it does is it tells SQL Server to discard any stored execution plan for that query.  This will cause SQL Server to rebuild the execution plan for this query.  The next thing it does is the magical part.  When SQL Server goes to build the execution plan, it will replace the local variables with the actual values in those variables.  This allows SQL Server to pull the data SUPER fast.


Keep in mind that many times the slowdown in a stored procedure call is just one statement inside the stored procedure.

The easiest way that I have found to figure out if this will help a slow stored procedure is to decompose the stored procedure in to separate sql statements.  I then run the sql statements to see what one is the slow one.  If I run a statement that is slow and uses a local variable, this may be a good candidate.  Next I replace the local variables in that statement with the explicit values.  If I rerun the statement and it performs fast, I know that this could use option recompile.