osql: generating scripts with line-breaks | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

osql: generating scripts with line-breaks


I have an job on sql-server, that consist of two steps:
Step 1. Generating scripts
osql -E -Sservername -Q"mybase.dbo.[myproc1]" -o "d:ackupMSSQLMyScr1.sql"
Step 2. Executing generated scripts
osql -E -Sservername -i "d:ackupMSSQLMyScr1.sql" So, after step 1 I have MyScr1.sql: EXEC master.dbo.sp_addumpdevice ‘disk’, ‘Base_base001’, ‘d:ackupMSSQLase001servername-base001.bak’
BACKUP DATABASE base001 TO Base_base001 WITH RETAINDAYS = 0, INIT
EXEC master.dbo.xp_cmdshell ‘@d:ackupArhiveIt
ar.exe a -ep1 -r -dh -m1 -md4096 -o+ D:ackupChangerase001.rar d:ackupMSSQLase001*.bak’
EXEC master.dbo.sp_dropdevice ‘Base_base001’
But executing xp_cmdshell doesn’t work because this command too long – there are 2 line-breaks between ‘@d: and *.bak’ How must i modify my scripts to fix this problem?
Why are you using osql? If I was trying to do this kind of things, I’d look at building the string INSIDE SQL Server, then executing xp_cmdshell directly from an SP, to go out to the OS. Then you bypass all this osql line break etc nonsense and save yourself effort. Tom Pullen
DBA, Oxfam GB

This procedure generates sql-script – i use PRINT operator inside trigger for all bases. CREATE PROCEDURE [dbo].[!EveryBase-Maint] AS [email protected]_name varchar (100)
[email protected] varchar(200)
SET @BackupRoot=’d:ackupMSSQL’
DECLARE @ReceptionList varchar (200)
SET @ReceptionList = ‘[email protected]‘
DECLARE MyCursor CURSOR FOR
SELECT name FROM master..sysdatabases
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘EXEC master.dbo.xp_cmdshell ”mkdir ‘ + @BackupRoot + @db_name + ””
PRINT ‘EXEC master.dbo.sp_addumpdevice ”disk”, ”Base_’ + @db_name + ”’, ”’ + @BackupRoot + @db_name + ” + @@SERVERNAME + ‘-‘ + @db_name + ‘.bak”’
–PRINT ‘EXEC master.dbo.xp_sendmail’
–PRINT ‘@recipients=”’ + @ReceptionList + ””
–PRINT ‘,@query = ”BACKUP DATABASE ‘ + @db_name + ‘ TO Base_’ + @db_name + ‘ WITH RETAINDAYS = 0, INIT”’
–PRINT ‘,@subject=”’ + CONVERT ( varchar (30) , GETDATE() , 120 ) + ‘ – ‘ + @@SERVERNAME + ‘ backup áàçû ‘ + @db_name + ””
PRINT ‘BACKUP DATABASE ‘ + @db_name + ‘ TO Base_’ + @db_name + ‘ WITH RETAINDAYS = 0, INIT’
PRINT ‘EXEC master.dbo.xp_cmdshell ”@d:ackupArhiveIt
ar.exe a -ep1 -r -dh -m1 -md4096 -o+ D:ackupChanger’ + @db_name + ‘.rar ‘ + @BackupRoot + @db_name + ‘*.bak”’
PRINT ‘EXEC master.dbo.sp_dropdevice ”Base_’ + @db_name + ””
PRINT ‘ ‘
FETCH NEXT FROM Mycursor INTO @db_name
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
Can you show a diferent (better) way?
why aren’t you using backup database directly? i don’t get it? why all the script generation, etc? what are you trying to achieve?<br /><br />if i was you i would write a script, using date variable manipulation (DATEPART, GETDATE(), etc etc) that generated backup file names for each db of the type<br /><br />master_db_200502070939.BAK<br /><br />similar to the maintenance plan wizard.<br /><br />Then write a separate script to construct an xp_cmdshell string to do an OS-level DELETE on the command line to get rid of the ones older than your desired retention period.<br /><br />Want me to write it for you? My fees start at… <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br /><br /><br />Tom Pullen<br />DBA, Oxfam GB
<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">why aren’t you using backup database directly? <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />because i want to automate this dirty work… <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> Just "write script and forget"<br />I have 20-30 databases at server, with maximum size 7 Gb (usually – 1 Gb). <br />In my script i can run DBCC checking (results – to my e-mail), automatically create backup device and back up databases (results – to my e-mail), and run extend archivator.<br /><br />Why not an maintainance plan? Because for an new database i must create maintplan manually. In my way, script make everything automatically, i must (manually) only verify the results.<br />I’m only one sysdba in our company, and my spare admin isn’t a sysdba, but he might call me when fing errors in e-mail reports…<br /><br />
Right. Then create a proc in msdb (on each target server) to do this dirty work. Have a job on a central server which you can point at all the target servers in turn (or all at once), to kick off their own self-contained jobs. you’d have to set up all the linked servers, etc, but it may be worth the effort, no? then you could centrally manage it and look good! Tom Pullen
DBA, Oxfam GB
quote:Then create a proc in msdb (on each target server)
Mmmmm… Something like this?
CREATE PROCEDURE [dbo].[!EveryBase-Maint1] AS [email protected]_name varchar (100)
[email protected] varchar(200)
[email protected] varchar (200)
[email protected] varchar (200)
SET @BackupRoot=’d:ackupMSSQL’
DECLARE @ReceptionList varchar (200)
SET @ReceptionList = ‘[email protected]‘
DECLARE MyCursor CURSOR FOR
SELECT name FROM master..sysdatabases
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
—Make folder for each base (for backup device and for DBCC Log)
SET @TmpStr1 = ‘mkdir ‘ + @BackupRoot + @db_name
EXEC master.dbo.xp_cmdshell @TmpStr1
—Âûïîëíåíèå ïðîâåðîê DBCC ñ îòïðàâêîé ïî ïî÷òå
–PRINT ‘EXEC master.dbo.xp_cmdshell ”osql -d’ [email protected]_name + ‘ -Q"DBCC Checkdb" -E >’ + @BackupRoot + @db_name + ‘DBCCresult.txt”’
–PRINT ‘EXEC master.dbo.xp_sendmail ‘
–PRINT ‘@recipients=”’ + @ReceptionList + ””
–PRINT’,@attachments= ”’ + @BackupRoot + @db_name + ‘DBCCresult.txt”’
–PRINT ‘,@subject=”’ + CONVERT ( varchar (30) , GETDATE() , 120 ) + ‘ ðåçóëüòàò DBCC ñåðâåð ‘ + @@SERVERNAME + ‘ áàçà ‘ + @db_name + ””
— Create Backup device
SET @TmpStr1 = ‘Base_’ + @db_name
SET @TmpStr2 = @BackupRoot + @db_name + ” + @@SERVERNAME + ‘-‘ + @db_name + ‘.bak’
EXEC master.dbo.sp_addumpdevice ‘disk’, @TmpStr1, @TmpStr2
— Backup Database
—PRINT ‘EXEC master.dbo.xp_sendmail’
—PRINT ‘@recipients=”’ + @ReceptionList + ””
—PRINT ‘,@query = ”BACKUP DATABASE ‘ + @db_name + ‘ TO Base_’ + @db_name + ‘ WITH RETAINDAYS = 0, INIT”’
—PRINT ‘,@subject=”’ + CONVERT ( varchar (30) , GETDATE() , 120 ) + ‘ – ‘ + @@SERVERNAME + ‘ backup áàçû ‘ + @db_name + ””
BACKUP DATABASE @db_name TO @TmpStr1 WITH RETAINDAYS = 0, INIT
— Compress backup files
SET @TmpStr2 = ‘@d:ackupArhiveIt
ar.exe a -ep1 -r -dh -m1 -md4096 -o+ D:ackupChanger’ + @db_name + ‘.rar ‘ + @BackupRoot + @db_name + ‘*.bak’
EXEC master.dbo.xp_cmdshell @TmpStr2
— Dropping backup device
EXEC [email protected]
— Next base
FETCH NEXT FROM Mycursor INTO @db_name
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
Without writting into *.sql file on disk?
There you go… just like that!
Tom Pullen
DBA, Oxfam GB
]]>