automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Automatic Job Monitoring

I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

How do you do your checks every morning?

Thanks,

MarcoYou can look up master SQL Server Agent job in SQL BOL. I personally have never used it except for a long time ago when I was experimenting with it early on.

You can also use SQL Agent Mail to flag when a job has failed and send out an alert. SQL Agent Mail is a pain to set up (have to install Outlook), is not fully supported by MS in an clustered environment (Outlook is not cluster aware) and when it goes South in can really go south in a bad way.

I personally manage about a dozen SQL servers. I use a combination of SQL Agent mail on a "management" SQL server (to which all the other servers forward their alerts) and a web site that pulls from a database on the "management" database. This database is in turn populated by a DTS package that pulls from the sysjobhistory table in the other servers. It was a fair amount of work to set up, but it's been running for almost three years now with very few changes.

Regards,

hmscott|||u can build query on system tables for job history in msdb database like : sysjobs, sysjobhistory, sysjobschedules. to make it automated, just schedule the query, then send a notification email, or store to a table, or however you want.
to make a complete check, notice the schedule, coz only already run steps (succeeded, failed, or cancelled) are logged in sysjobhistory.

Automated restart of Analysis Services

Hello,

I need to put the Analysis server in local mode only while my jobs process cubes on Saturday morning.

I can script the property but I do not know how to automatically restart the service.

Any idea?

Thanks

Philippe

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object />

<ObjectDefinition>

<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>SMDEV</ID>

<Name>SMDEV</Name>

<ServerProperties>

<ServerProperty>

<Name>Network\ListenOnlyOnLocalConnections</Name>

<Value>true</Value>

</ServerProperty>

</ServerProperties>

</Server>

</ObjectDefinition>

</Alter>

You can create a simple batch file and use there commands "net start MSSQLSErverOlapservice"

and "net stop MSSQLSErverOlapservice".

Another way, you can write a simple C# application using ServiceController inteface. Take a look here; http://msdn2.microsoft.com/en-us/library/sywbez17.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Automated Dump/Load

Hi
Does anyone have any scripts/jobs that automate the load from prod to test
environments. Prod already has dump jobs running. What I am looking for is a
sp or job which will ready the backup history table on prod and get the
latest dump files, xcopy them to test server and load test databases having
first killed users related to the db's being loaded.
Thanks
Hi
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:D7C7807A-2833-4880-A01E-B285D2A2584F@.microsoft.com...
> Hi
> Does anyone have any scripts/jobs that automate the load from prod to test
> environments. Prod already has dump jobs running. What I am looking for is
> a
> sp or job which will ready the backup history table on prod and get the
> latest dump files, xcopy them to test server and load test databases
> having
> first killed users related to the db's being loaded.
> Thanks

Automated Dump/Load

Hi
Does anyone have any scripts/jobs that automate the load from prod to test
environments. Prod already has dump jobs running. What I am looking for is a
sp or job which will ready the backup history table on prod and get the
latest dump files, xcopy them to test server and load test databases having
first killed users related to the db's being loaded.
ThanksHi
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:D7C7807A-2833-4880-A01E-B285D2A2584F@.microsoft.com...
> Hi
> Does anyone have any scripts/jobs that automate the load from prod to test
> environments. Prod already has dump jobs running. What I am looking for is
> a
> sp or job which will ready the backup history table on prod and get the
> latest dump files, xcopy them to test server and load test databases
> having
> first killed users related to the db's being loaded.
> Thanks

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIAHI
Use SQL-DMO:
'_______________________________________________________
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjelölve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
letöröljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'__________________________________________________________________
Andras Jakus MCDBA
"pk" wrote:
> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
> HI
> Use SQL-DMO:
> '_______________________________________________________
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjelölve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> letöröljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '__________________________________________________________________
> Andras Jakus MCDBA
> "pk" wrote:
> > In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> > I can manaully script out all exisiting sql agent jobs, and keep for backup.
> >
> > How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> >
> > TIA
> >

automate sql scripting of jobs in sql agent

In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
I can manaully script out all exisiting sql agent jobs, and keep for backup.
How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
TIA
HI
Use SQL-DMO:
'_________________________________________________ ______
Dim conServer
Dim fso, fsoF
Dim iFile, iFolder
Dim oJB
Dim strJob
Dim strCat
Dim strFilename
Dim strPath
Const ioModeAppend = 8
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
található
strPath = "\\<<Your UNC path>>\Jobs\"
strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
dátummal megjel?lve
For Each oJB In conServer.JobServer.Jobs
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
strJob = strJob & "----" &
vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "----" &
vbCrLf
strJob = strJob & oJB.Script(4) & vbCrLf
strCat = oJB.category
strCat = instr(1,strCat, "REPL-")
End If
Next
Set conServer = Nothing
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoF = CreateObject("Scripting.FileSystemObject")
Set iFolder = fsoF.getfolder(strPath)
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing
Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
let?r?ljük.
For Each f1 in FileContainer
if (f1.DateCreated < (Date - 30)) Then
f1.delete
end if
Next
Set fsoF = Nothing
'_________________________________________________ _________________
Andras Jakus MCDBA
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>
|||PK, you may use that script provided by Andras to script out SQL jobs on qa
monthly basis or/and you may also want to look at other options:
You may also use SCRPTXFR that comes along with SQL Server 2000 which will
allow you to script out all the object in text files.
You may also backup MSDB database on a monthly basis (This will ensure not
just Jobs but alos DTS packages, alerts, etc to be backed-up)
You may also BCP out all the SysJobs table every month and incase of issues
you may just load back the data in the MSDB tables.
HTH.
Saleem@.sqlnt.com
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> Use SQL-DMO:
> '_________________________________________________ ______
> Dim conServer
> Dim fso, fsoF
> Dim iFile, iFolder
> Dim oJB
> Dim strJob
> Dim strCat
> Dim strFilename
> Dim strPath
> Const ioModeAppend = 8
> Set conServer = CreateObject("SQLDMO.SQLServer")
> conServer.LoginSecure = True
> conServer.Connect "<>Your Server Name><" 'Minden felhasználói Job itt
> található
> strPath = "\\<<Your UNC path>>\Jobs\"
> strFilename = strPath & "JOBS-00l1.sql" 'Naponta egy állomány készül a
> dátummal megjel?lve
> For Each oJB In conServer.JobServer.Jobs
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> if strCat =0 then 'A replikációs Job-ok nem kerülnek a szkriptbe
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
> strJob = strJob & "----" &
> vbCrLf
> strJob = strJob & oJB.Script(4) & vbCrLf
> strCat = oJB.category
> strCat = instr(1,strCat, "REPL-")
> End If
> Next
> Set conServer = Nothing
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set fsoF = CreateObject("Scripting.FileSystemObject")
> Set iFolder = fsoF.getfolder(strPath)
> Set iFile = fso.CreateTextFile(strFilename, True)
> iFile.Write (strJob)
> iFile.Close
> Set fso = Nothing
> Set FileContainer = iFolder.Files 'A harminc napnál régebbi állományokat
> let?r?ljük.
> For Each f1 in FileContainer
> if (f1.DateCreated < (Date - 30)) Then
> f1.delete
> end if
> Next
> Set fsoF = Nothing
> '_________________________________________________ _________________
> Andras Jakus MCDBA
> "pk" wrote:
|||Have look at
http://www.nigelrivett.net/DMOScriptAllDatabases.html
It shows how to schedule scripting most objects in the database and save
them in sourcesafe (although I haven't got round to automating that yet -
suspect I will soon).
"pk" wrote:

> In SQL Ent. Mgr -> Mgt -> SQL Svr Agent -> Jobs -> right click- > All Task ,
> I can manaully script out all exisiting sql agent jobs, and keep for backup.
> How can I automate this to run at a fixed schedule (eg, monthyl etc..) ?
> TIA
>