SQL Server Backup Command with a Variable

A few weeks back I blogged how to do CONTAINSTABLE() and FREETEXTTABLE() using SQL variables.  This weekend, I was working on creating a backup script for a client and ran into a similar problem where the SQL Server 2005 documentation says you can use a variable but it didn’t actually work because of a crucial missing detail.

For this script:

declare @databaseName nvarchar

select @databaseName = ‘DbProTesting’

backup database @databaseName
to disk = ‘c:tempDbProTesting.bak’

I got the following weird SQL error:

Msg 911, Level 16, State 11, Line 5
Could not locate entry in sysdatabases for database ‘D’. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 5
BACKUP DATABASE is terminating abnormally.

Of course there’s no database named “D” in sysdatabases but why does SQL Server 2005 think that that’s what the database name should be?!

Solution: If you change the variable data type from “nvarchar” to “nvarchar(1024)” it works fine.

Here’s the working script:

declare @databaseName nvarchar(1024)

select    @databaseName = ‘DbProTesting’

backup database @databaseName
to disk = ‘c:tempDbProTesting.bak’

-Ben