SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating Backups using SQLMD


Automating Backups using SQLMD

Author
Message
tstapenhorst
tstapenhorst
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 138
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.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23858 Visits: 5314
Can I ask a different question.. Why aren't you using maintenance plans?

CEWII
tstapenhorst
tstapenhorst
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 138
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.
Toby White
Toby White
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 639
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
tstapenhorst
tstapenhorst
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 138
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.
Toby White
Toby White
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 639
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
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23858 Visits: 5314
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213779 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search