Estimate Backup Size In SQL Server

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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 nothing will be 100% accurate, SQL Server has a built in stored procedure for calculating the used space in a database. A full database backup only stores the actual data/objects in the database. The unused space is not stored.

In the example above we call the sp_spaceused stored procedure from the database that we would like the size from. This is important… you need to execute this from the database that you want to get the size for. This will return a dataset with multiple columns. The column that you will look at is the reserved column. This will show the amount of space without the unused space.

 
Note
The @updateusage parameter tells the stored procedure to update the space usage info before returning the used size. Passing TRUE in to this parameter will give us the most accurate size estimate.

If Statement In An Update

Facebooktwittergoogle_plusredditpinterestlinkedinmail

If you are updating multiple rows in one statement, but only want to update a value in a column if a condition is satisfied, then you need to perform some sort of if logic inside your update. Here’s a straight forward way of doing this.

In the example below, I am just going to use 2 temp table variables. I’ll update the @SourceTable with the data from the @UpdateTable… but only where the UpdateFlag field is set to 1.

 
In the example above, you can see that the AnimalName is only updated when the UpdateFlag column is set to 1. The 2nd record “Cat” is not updated with the value “Cow” because it has a 0 for the UpdateFlag. This is done using a case statement on the SET operation. The example above is very simple. This case logic in the SET operation becomes much more helpful in more complex queries.

Estimate Backup Size In Sql Server

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms188351(v=sql.90).aspx

How To Run SQL Scripts Via Command Prompt

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx