SQL Server 2000

Back to Basics: The BACKUP DATABASE command

Now that you have objects created within your database, and data within the tables you need to secure your database in case of a server failure or accidental data deletion.  This would be done by taking backups of the database.  Backups are taken by using the BACKUP DATABASE command.

The frequency that full backups are taken at should be determined by how much data changes within the databases.  On busier systems full backups should be taken daily.  On systems which are less busy the full backup can be taken less often, every few days, once per week, etc.

The syntax of the BACKUP DATABASE command is fairly basic.  You specify the name of the database to backup, and the destination that you will be backing up to.  If your database name is MyDatabase and your going to backup to a file named MyDatabase.bak on the E drive your BACKUP DATABASE command would look something like this.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak'

This command will backup all tables, views, procedures, functions, etc as well as all the data within the database.  Backing up the database in an online operation meaning that users can be connected to the database, continuing to make changes to the database while the backup database command is running.  The database is backed up in the same state that it was in when the command started.  All changes made during the backup are logged into the database and staged in memory.  Upon completion of the BACKUP DATABASE command these changes are destaged from memory to the physical file during a checkpoint operation.

If you have a full text index, and are using SQL Server 2000 the full text backup is not included as part of the backup which is created by the BACKUP DATABASE command.  After restoring the database you would need to recreate the full text indexes.  This issue is resolved in SQL Server 2005 when the full text indexes are added to the database backup.

If you wish to create two backups of the database so that your backups can survive a failure of the disk you backup the database to you can use the MIRROR TO clause of the BACKUP DATABASE command.  This clause creates two exact duplicate backups of the database.  This option is included in SQL Server 2005 and higher.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak' MIRROR TO DISK='F:\MyDatabase.bak'

If you find that your backups are taking to long do to a large database size and/or slow disk speed on the disk you are backing up to you can strip the database backup across database backups.  This is done by simply specifying additional destinations.

BACKUP DATABASE MyDatabase TO DISK='E:\Mydatabase.bak', DISK='F:\MyDatabase.bak'

Now striping your database can improve your backup performance, but it puts your backups at greater risk for a disk failure as each part of the backup disk is spread across two drives.  If either backup file is lost or damaged the entire backup is useless.  Because of this you can combine the striping for speed and the mirroring for safety.  To do this the number of backup devices specified in the MIRROR clause must be the same as the number of backup devices specified in the initial destination clause.

BACKUP DATABAES MyDatabase TO DISK='E:\MyDatabase.bak', DISK='F:\MyDatabase.bak' MIRROR TO DISK='G:\MyDatabase.bak', DISK='H:\MyDatabase.bak'

You can read up more about detailed backup techniques in the eZine article I wrote a few months back.

Check back in the coming weeks for information about log backups and restoring your database and log backups.

Denny

Article: SQL Server memory configurations for procedure cache and buffer cache

I’ve just published another tip over on SearchSQLServer.com.  This one is titled SQL Server memory configurations for procedure cache and buffer cache.

To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you’re using, whether it’s x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. I talk about how the system configures memory and how it allocates the amount of buffer and procedure cache that’s available.Denny

Back To Basics: The UPDATE Statement

After you’ve inserted the data into the table, it’s time to update the data.  We do this by using the UPDATE statement.  The update statment can be used in two ways.  The first is to update a record or set of records in a single table, by simply filtering the data in the table by using values in the table.

UPDATE TableName
SET Column1 = ‘Value’
WHERE AnotherColumn = ‘AnotherValue’

A more complex update uses another table as the source of the data. This makes the UPDATE statement look like a combination of the UPDATE statement and the SELECT statement.

UPDATE TableName
SET Column2 = AnotherTable.Column3
FROM AnotherTable
WHERE TableName.Column1 = TableName.Column1

We can add joins into this as well, so that we can update more than one column from different tables at the same time.

UPDATE TableName
SET Column2 = AnotherTable.Column3,
Column3 = ThirdTable.Column2
FROM AnotherTable
JOIN ThirdTable ON AnotherTable.Column5 = ThirdTable.Column4
WHERE TableName.Column1 = TableName.Column1

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the UPDATE statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: The INSERT Statement

While the SELECT statement is probably the most important command, the INSERT comes in handy.  The INSERT statement is used to do exactly what it sounds like, it inserts data into a table.

 There are two ways to insert data into a table.  The first is to pass in each of the values, and the second is to insert the data from a select statement.

For both commands we’ll be using a new table with this definition.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertTable')
DROP TABLE InsertTable
GO
CREATE TABLE InsertTable
(id INT,
name sysname)

First lets look at passing in the values. With this syntax we specify the names of the columns, and then specify each of the values.

INSERT INTO InsertTable
(id, name)
VALUES
(0, ‘test’)

Second we’ll look at the SELECT statement. There are two ways we can do this as well. The first is to load a single set of values with the select statement. When doing this you can optionally specify the column names or not.

INSERT INTO InsertTable
SELECT 0, ‘test’

The second option with the SELECT statement is to use a SELECT statement from a table. All of the functionally of the SELECT statement is available when using the SELECT statement as part of the INSERT statement.

INSERT INTO InsertTable
SELECT id, name
FROM sysobjects

We can also do this with some of the more advanced functions of the SELECT statement.

INSERT INTO InsertTable
(name, id)
SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the INSERT statement. It includes more examples, and some of the other options which are available to you.

Denny

Back To Basics: The SELECT Statement

There are four basic commands in databases.  They are SELECT, INSERT, UPDATE and DELETE.  Probably the most important of these is the SELECT command.  The SELECT command is how the data in the database is retrieved and displayed.

(All these code samples can be run on all versions of Microsoft SQL Server from 7.0 up.)

Like a regular sentence there are a few basic parts of the SELECT statement.  First there is a list of columns that you want to see.  Then there is the FROM portion of the statement.  This is the table or tables which you will be getting the data from.  Then is the WHERE portion of the statement which is the data filter.

When you put all this together the SELECT statement will look something like this.

SELECT id, name
FROM sysobjects
WHERE type = ‘U’

If you were going to read this as a normal sentence it would read something like:
I want to see the "id" and "name" columns, from the "sysobjects" table, where the "type" column has a value of "U".

Now if we need to look at data from two different tables which is combined together we add a JOIN statement between the FROM and WHERE portions of the SELECT statement.

SELECT sysobjects.name, syscolumns.name
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’

You can see from the above statement that we are getting the table names and column names for all the user tables. When you look at the JOIN command you see that we are matching up the id column from both of the tables. Now in this example the column name is the same in both tables. This isn’t always the case. If you don’t know how the tables relate to each other, you can usually look at the foreign key constraints to see how the tables relate to each other.

If you want to get counts of data you can use the GROUP BY clause along with the COUNT() function. In this next statement we will count the number of columns in each table.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name

When ever you use a mathematical function such as MIN(), MAX(), AVG(), COUNT(), etc you have to add the GROUP BY clause to the SELECT statement so that SQL Server knows how to roll up the data.

Now as a last part of the SELECT statement to look at today, we are going to filter our GROUP BY query further, by showing all the tables that have over 5 columns. To do this we use the HAVING clause. Without the HAVING clause we would have to do a very complex query as a sub-query in the WHERE clause which would be very inefficient.

SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns on sysobjects.id = syscolumns.id
WHERE sysobjects.type = ‘U’
GROUP BY sysobjects.name
HAVING count(*) > 5

The HAVING phrase is very simple in it’s syntax. You put the math function that you want to use (in this case COUNT(*)) then <, >, <> or = and what ever you want to compare it to.

I hope that you find this post useful. I’ve barely scratched the surface of the SELECT statement, and what it can do. I encourage everyone to open up Books OnLine and read through the information on the SELECT statement. It includes more examples, and some of the other options which are available to you.

Denny

T/SQL Back To Basics

I’ve seen a lot of posts online over the last few weeks with people asking about basic query syntax.  So over the next few posts I’m going to show some basic T/SQL queries as well as some more “advanced” features and syntaxes.

Hopefully you’ll find these syntaxes useful.

All the code syntaxes will cover SQL 2000 and SQL 2005.  Where code only works in one version or another it will be noted.

Denny

Guide to Installing SQL 2000 and SQL 2005 on the same machine

You can install SQL 2000 and SQL 2005 on the same machine.  I always recommend that they be installed in this order to try and get everything working as best as possible.

1. Install SQL 2000
2. Install SQL 2005
3. Install SQL 2000 SP4 (Or the latest service pack)
4. Install SQL 2005 SP2 (Or the latest service pack)

Denny