Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automating Backups using SQLMD Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:10 PM
Points: 8, Visits: 131
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.
Post #868155
Posted Thursday, February 18, 2010 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Can I ask a different question.. Why aren't you using maintenance plans?

CEWII
Post #868227
Posted Thursday, February 18, 2010 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:10 PM
Points: 8, Visits: 131
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.
Post #868283
Posted Thursday, February 18, 2010 11:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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
Post #868354
Posted Thursday, February 18, 2010 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:10 PM
Points: 8, Visits: 131
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.
Post #868567
Posted Thursday, February 18, 2010 3:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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
Post #868621
Posted Thursday, February 18, 2010 4:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #868649
Posted Thursday, February 18, 2010 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150
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
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.


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #868734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse