If you would like to create an ad-hoc backup without disrupting your current backup jobs you will have to use the new copy only option for SQL 2005. This is command line only operation that you can’t do within the SQL gui window.

We will assume the following:

• old database name: DataBase1
• old database logical name: Database1
• old database transaction log name: Database1_log
• new database name: DataBase2

To create the back up open a query windows and excute a statement as follows:

backup database DataBase1 to disk=’c:\database1.bak’ with copy_only

To restore it do the following in the query window:

restore database [DataBase2]
from disk = N’c:\Database2.bak’
with file = 1
,move N’Database1′
to N’c:\Database1.mdf’
,move N’Database1_log’
to N’C:\Database1_log.ldf’
, nounload, stats = 10

This is place a new database called DataBase2 with the MDF and LDF in the c: directory. You can of course place it wherever you desire but I shortened this for clarification.

One of the most common reason I have seen for SQL slow down is improper maintenance of the databases indexes. While I won’t get into the specifics of an index. I’ll just say that the closest analogy is a table of contents for a book.

Once I have a idea of the database that is being used I will run the following in a query window:

exec sp_MSforeachtable @command1=”print ‘?’ DBCC showcontig (‘?’)”

It will return alot of information. The important information to focus on is the page density percentage. If the values falls within the 85-100% range then you can probably getting away with a reindex of the SQL index. If the range falls below the 85% then I would recommend a full off-line rebuild of the indexes.

You can find a definition and creation here:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx

You can find a much more detail index maintenance explination here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx