SQL Server copy_only backup and restore

Previously I wrote an article listing information on SQL Server backups. I want to expand a little on the practical use of the new and useful copy_only backup available in SQL Server 2005 and 2008. You can’t get to copy_only backups/restores through the Management Studio for SQL Server 2005 (you have to use T-SQL) but you can using the new Management Studio for SQL Server 2008.

This is an ideal way to test production db’s on a development server without disrupting your normal backup schedule. In the following example I am doing a full db backup to the production backup drive:

BACKUP DATABASE [my_db]
TO DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
NOFORMAT,
INIT,
copy_only,
NAME = N'my_db-Full Database CopyOnly Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO


But when I restore it I would want to be sure that I’m moving the db files to the correct location on my development SQL Server, hence the WITH MOVE subcommands below.   I always want it to have a completely new name:

RESTORE DATABASE [my_db_copy]
FROM DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
MOVE 'my_db_dat' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copydat.mdf',
MOVE 'my_db_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copylog.ldf',
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO


So now I have an up-to-date version of the production db on my system. Bear in mind that any logins for applications or users that are necessary would need to be recreated to be able work with this new copy. I keep a special login/user script handy and run it to recreate these.

Autocompletion and Intellisense in SQL Server

I understand you can use third party tools to add autocompletion and intellisense to Management Studio 2005 (for example, Redgate offers SQL Prompt Pro) but I haven’t tried it yet. I have installed SQL Server Express 2008 to play with.  I tried the new intellisense feature and I have to say I’m impressed with how nicely it’s built in to Management Studio.

For example, if you wait long enough after entering ‘use ‘ (don’t forget the space) it’ll give you a drop down of available db’s. That’s cool.
But even more nifty is typing ‘exec usp’ and then picking one of my own stored procedures from the list. Then hit space and there’s a list of the necessary parameters.  Wow!
And if you name all your tables with a ‘tbl’ prefix you can try ’select * from tbl’ and you should see a list of the tables. And, again if you use a standard naming convention you can pick a table and then a likely prefix for a column in a where clause like ’select * from tblnappy where str’ and get all the string columns you have listed. Or functions close to that spelling.
For more information access BOL on the MS website and look at the following topics:

• Transact-SQL Syntax Supported by Intellisense
  
• Using Intellisense

It certainly makes be eager to get the new computer that’s coming to me at work so I can install the Developer edition and start working with it.

American Political Lampoon

Of course this campaign is different this year: more substance than I would have believed possible although not as much as I could have hoped… but there’s always room for humour. Here are some links that tickled my fancy:

• The Dance of the Politicos.

• Political Pokemon.

• Late night one liners.

• And, of course, the Onion.

Anyone seen better ones?