tstapenhorst (2/18/2010)

---

I would like to request another set of eyes to see an issue that I'm missing. I using a Windows Command file from our "Management" server (non-SQL) to backup all our SQL databases on six different servers. I have a one Windows Command file (BackupAllSQL.cmd) that calls a SQL script file (BackupDatabase.sql) over and over again with input parameters coming from BackupAllSQL.cmd that looks like the following:

Call BackupSQL.cmd GA06 DatawiseRocklin_LIVE

(GA06 is the server name and DatawiseRocklin_LIVE is the name of the database)

The main parts of the BackupSQL.cmd script looks like the following with SQLServer holding the GA06 and DDBName holding DatawiseRocklin_LIVE respectfully, and the SQLCMD call:

SET SQLServer=%1

SET DBName=%2

SQLCMD -S %SQLServer% -d %DBName% -i BackupDatabase.sql -v BDBName = %DBName% BUServer = %SQLServer%

Now inside BackupDatabase.sql I even pass in the server name (BUServer) and the database (BDBName) just to try and narrow down the issue. The runtime error I copied is below the GO. If I make this a stored procedure with a few modifications, it works, but not from SQLCMD, and that is my issue. Any ideas and suggestions are sure appreciated.

DECLARE @BackupDest VARCHAR(200);

DECLARE @BackupDBPath VARCHAR(200);

DECLARE @FNTime VARCHAR(4);

DECLARE @Holdtime VARCHAR(12);

DECLARE @INServer VARCHAR(10);

DECLARE @INBDBName VARCHAR(100);

SET @Holdtime = CONVERT(VARCHAR, GETDATE(), 114);

/* holds the current system time) */

SET @FNTime = CAST(LEFT(@Holdtime, 2) + SUBSTRING(@Holdtime, 4, 2) AS VARCHAR(4));

/* holds the HHMM to be used as part of the file name */

SET @INServer = $(BUServer);

SET @INBDBName = $(BDBName);

SET @BackupDBPath = '\D$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\';

SET @BackupDest = CASE

WHEN @INServer = 'GA06' THEN '\\' + @INServer

WHEN @INServer = 'GQ03' THEN '\\' + @INServer

END

+ @BackupDBPath + @INBDBName + CONVERT(VARCHAR, GETDATE(), 112)

+@FNTime + '.BAK';

BACKUP DATABASE @INBDBName TO DISK = @BackupDest;

GO

Here is runtime error message (I don't understand the "Invalid column name 'GA06' and 'DatawiseRocklin_LIVE'):

C:\Data\Backups>SQLCMD -S GA06 -d DatawiseRocklin_LIVE -i BackupDatabase.sql -v

[font="Arial Black"][highlight]BDBName = DatawiseRocklin_LIVE BUServer = GA06[/highlight][/font]

Msg 207, Level 16, State 1, Server GA06, Line 114

Invalid column name 'GA06'.

Msg 207, Level 16, State 1, Server GA06, Line 115

Invalid column name 'DatawiseRocklin_LIVE'.

Thank you to anyone taking time out of their day to solve my issue. My eyes just don't see it.