February 18, 2010 at 9:29 am
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
BDBName = DatawiseRocklin_LIVE BUServer = GA06
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.
February 18, 2010 at 10:10 am
Can I ask a different question.. Why aren't you using maintenance plans?
CEWII
February 18, 2010 at 10:54 am
Good question Elliott. We wanted to have one management server manage all the SQL backups from one central server. That management server only has SQL Sever Client Tools installed; we are a school district with budget constraints on SQL licensing costs along with other costs. I didn't want to put a stored procedure on one of our SQL servers, and have to keep calling it to do the backups of all of our other SQL servers. Am I approaching this from the wrong direction?
Thanks, Ted.
February 18, 2010 at 11:38 am
You don't have to use SQLCMD to perform backups on the other servers. You can use linked server, openrowset, or Multi-Server jobs to manage all of your SQL Servers from one centralized server. Another method would be to use a single SSIS package that executes all the backups and/or maintenance code on all the sql servers.
For the master jobs solution in BOL look at "multiserver administration" under jobs [SQL Server Agent]
For the open rowset solution you would essentially by making remote T-SQL calls to the other servers from your central server. You would need to enable the configuration option "Ad Hoc Distributed Queries"
For the SSIS solution the easiest way to hand write the code that you want to deliver to each of the SQL Servers. If you wanted to get fancy you could save the SQL Server names in a table and loop through them executing the same code for each one in the SSIS package.
Hope this helps and if you are interested in more specifics of either of these options please let me know.
Regards,
Toby
February 18, 2010 at 2:42 pm
Hi Toby,
I would be more interested in your description of "hand writing" the code, as I need to further develop my coding skills in SQL. I can search through the Scripts section to see if I can find something along those lines. If you have any suggestions or more specifies along the lines of the SSIS solution, I would sure appreciate it. Thank you for taking the time to read and answer my original post.
Thank you,
Ted.
February 18, 2010 at 3:25 pm
I am happy to help. I have gotten a lot of good help from this site over the years. I have all maintenance tasks in my environments configured with custom stored procedures that are called by jobs. Your last post revealed something new to me though, which is that the management server you want to use doesn't have the SQL engine and agent installed.
I am not sure why you don't want to place stored procedures on databases in any of your existing SQL Servers. Just create a management database like "DBA" and create your procedures there. Even if you don't want to do that you could still create a job that has all the code you need in it. Personally I have several database management jobs on every one of my SQL Servers, which is recommended over having one centralized management server.
All that being said, you can still issue sql commands to affect maintenance tasks from a server that does not have the sql engine or agent installed, it's just that I can't think of a good reason why you would want to when you have an SQL Agent service available to you on your existing sql server instances. If you are resolved to use just one server can you select a server with the sql engine and agent on it? If so then we can discuss what you need to do from there, if not then you will be forced to issue sqlcmd calls to the other servers for your maintenance tasks.
Regards,
Toby
February 18, 2010 at 4:10 pm
tstapenhorst (2/18/2010)
Good question Elliott. We wanted to have one management server manage all the SQL backups from one central server. That management server only has SQL Sever Client Tools installed; we are a school district with budget constraints on SQL licensing costs along with other costs. I didn't want to put a stored procedure on one of our SQL servers, and have to keep calling it to do the backups of all of our other SQL servers. Am I approaching this from the wrong direction?Thanks, Ted.
Ted,
I think another poster posted some good ideas. I *personally* would probably setup backup jobs on each server and use the management server to check and make sure that the backups occured. And I would probably use SSIS to do that check. But that is me.. Mileage may vary.
CEWII
February 18, 2010 at 8:05 pm
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.
You're missing quotes around the string values to the right of the equals signs and I believe you're also missing a comma.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply