Is there away to generate a script from DB Maintenance plans creation? This will allow me to use the script to create many other DB maintenance plans. I support a SQL Server 2000 instance with a ton of databases on it.
Thanks ,
Brent
Brent,
You should look up "SQLMAINT", which has a command line option for recurring
tasks such as database backup.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Another link you may mind useful follows:
http://msdn.microsoft.com/library/de...html/smsdb.asp
Regards,
Keith Wilson
==
sqlmaint Utility
New Information - SQL Server 2000 SP3.
The sqlmaint utility performs a specified set of maintenance operations on
one or more databases. Use sqlmaint to run DBCC checks, back up a database
and its transaction log, update statistics, and rebuild indexes. All
database maintenance activities generate a report that can be sent to a
designated text file, HTML file, or e-mail account.
Syntax
sqlmaint
[-?] |
[
[-S server_name[\instance_name]]
[-U login_ID [-P password]]
{
[ -D database_name | -PlanName name | -PlanID guid ]
[-Rpt text_file]
[-To operator_name]
[-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
[-RmUnusedSpace threshold_percent free_percent]
[-CkDB | -CkDBNoIdx]
[-CkAl | -CkAlNoIdx]
[-CkCat]
[-UpdOptiStats sample_percent]
[-RebldIdx free_space]
[-WriteHistory]
[
{-BkUpDB [backup_path] | -BkUpLog [backup_path] }
{-BkUpMedia
{DISK [ [-DelBkUps <time_period>]
[-CrBkSubDir ] [ -UseDefDir ]
]
| TAPE
}
}
[-BkUpOnlyIfClean]
[-VrfyBackup]
]
}
]
<time_period> ::=
number[minutes | hours | days | weeks | months]
Note The parameters and their values must be separated by a space. For
example, there must be a space between -S and server.
Arguments
-?
Specifies that the syntax diagram for sqlmaint be returned. This parameter
must be used alone.
-S server_name[\instance_name]
Specifies the target instance of Microsoft SQL ServerT 2000. Specify
server_name to connect to the default instance of SQL Server 2000 on that
server. Specify server_name\instance_name to connect to a named instance of
SQL Server 2000 on that server. If no server is specified, sqlmaint connects
to the default instance of SQL Server 2000 on the local computer.
-U login_ID
Specifies the login ID to use when connecting to the server. If not
supplied, sqlmaint attempts to use Windows Authentication. If login_ID
contains special characters, it must be enclosed in double quotation marks
("); otherwise, the double quotation marks are optional.
Security Note When possible, use Windows Authentication.
-P password
Specifies the password for the login ID. Only valid if the -U parameter is
also supplied. If password contains special characters, it must be enclosed
in double quotation marks; otherwise, the double quotation marks are
optional.
Security Note The password will not be masked. When possible, use Windows
Authentication.
-D database_name
Specifies the name of the database in which to perform the maintenance
operation. If database_name contains special characters, it must be enclosed
in double quotation marks; otherwise, the double quotation marks are
optional.
-PlanName name
Specifies the name of a database maintenance plan defined using the Database
Maintenance Plan Wizard. The only information sqlmaint uses from the plan is
the list of the databases in the plan. Any maintenance activities you
specify in the other sqlmaint parameters are applied to this list of
databases. You can get the plan name from SQL Server Enterprise Manager.
-PlanID guid
Specifies the globally unique identifier (GUID) of a database maintenance
plan defined using the Database Maintenance Plan Wizard. The only
information sqlmaint uses from the plan is the list of the databases in the
plan. Any maintenance activities you specify in the other sqlmaint
parameters are applied to this list of databases. This must match a plan_id
value in msdb.dbo.sysdbmaintplans.
-Rpt text_file
Specifies the full path and name of the file into which the report is to be
generated. The report is also generated on the screen. The report maintains
version information by adding a date to the file name. The date is generated
as follows: at the end of the file name but before the period, in the form
_yyyyMMddhhmm. Yyyy = year, MM = month, dd = day, hh = hour, mm = minute.
If you run the utility at 10:23 A.M. on December 1, 1996, and this is the
text_file value:
c:\Program Files\Microsoft SQL Server\Mssql\Backup\Nwind_maint.rpt
The generated file name is:
c:\Program Files\Microsoft SQL
Server\Mssql\Backup\Nwind_maint_199612011023.rpt
The full UNC file name is required for text_file when sqlmaint accesses a
remote server.
-To operator_name
Specifies the operator to whom the generated report will be sent through SQL
Mail. The operator can be defined by using SQL Server Enterprise Manager.
-HtmlRpt html_file
Specifies the full path and name of the file into which an HTML report is to
be generated. sqlmaint generates the file name by appending a string of the
format _yyyyMMddhhmm to the file name, just as it does for the -Rpt
parameter.
The full UNC file name is required for html_file when sqlmaint accesses a
remote server.
-DelHtmlRpt <time_period>
Specifies that any HTML report in the report directory be deleted if the
time interval after the creation of the report file exceeds <time_period>.
-DelHtmlRpt looks for files whose name fits the pattern generated from the
html_file parameter. If html_file is c:\Program Files\Microsoft SQL
Server\Mssql\Backup\Nwind_maint.htm, then -DelHtmlRpt causes sqlmaint to
delete any files whose names match the pattern c:\Program Files\Microsoft
SQL Server\Mssql\Backup\Nwind_maint*.htm and that are older than the
specified <time_period>.
-RmUnusedSpace threshold_percent free_percent
Specifies that unused space be removed from the database specified in -D.
This option is only useful for databases that are defined to grow
automatically. Threshold_percent specifies in megabytes the size that the
database must reach before sqlmaint attempts to remove unused data space. If
the database is smaller than the threshold_percent, no action is taken.
Free_percent specifies how much unused space must remain in the database,
specified as a percentage of the final size of the database. For example, if
a 200-MB database contains 100 MB of data, specifying 10 for free_percent
results in the final database size being 110 MB. Note that a database will
not be expanded if it is smaller than free_percent plus the amount of data
in the database. For example, if a 108-MB database has 100 MB of data,
specifying 10 for free_percent will not expand the database to 110 MB; it
will remain at 108 MB.
-CkDB | -CkDBNoIdx
Specifies that a DBCC CHECKDB statement or a DBCC CHECKDB statement with the
NOINDEX option be run in the database specified in -D. For more information,
see DBCC CHECKDB.
A warning is written to text_file if the database is in use when sqlmaint
runs.
-CkAl | -CkAlNoIdx
Specifies that a DBCC NEWALLOC statement or a DBCC NEWALLOC statement with
the NOINDEX option be run in the database specified in -D. For more
information, see DBCC NEWALLOC.
-CkCat
Specifies that a DBCC CHECKCATALOG statement be run in the database
specified in -D. For more information, see DBCC CHECKCATALOG.
-UpdOptiStats sample_percent
Specifies that the following statement be run on each table in the database:
UPDATE STATISTICS table WITH SAMPLE sample_percent PERCENT
For more information, see UPDATE STATISTICS.
-RebldIdx free_space
Specifies that indexes on tables in the target database should be rebuilt by
using the free_space percent value as the inverse of the fill factor. For
example, if free_space percentage is 30, then the fill factor used is 70. If
a free_space percentage value of 100 is specified, then the indexes are
rebuilt with the original fill factor value.
-WriteHistory
Specifies that an entry be made in msdb.dbo.sysdbmaintplan_history for each
maintenance action performed by sqlmaint. If -PlanName or -PlanID is
specified, the entries in sysdbmaintplan_history use the ID of the specified
plan. If -D is specified, the entries in sysdbmaintplan_history are made
with zeroes for the plan ID.
-BkUpDB [backup_path] | -BkUpLog [backup_path]
Specifies a backup action. -BkUpDb backs up the entire database. -BkUpLog
backs up only the transaction log.
[backup_path] specifies the directory for the backup. [backup_path] is not
needed if -UseDefDir is also specified, and is overriden by -UseDefDir if
both are specified. The backup can be placed in a directory or a tape device
address (for example, \\.\TAPE0). The file name for a database backup is
generated automatically as follows:
dbname_db_yyyyMMddhhmm.BAK
where
a.. dbname is the name of the database being backed up.
b.. yyyyMMddhhmm is the time of the backup operation with yyyy = year, MM
= month, dd = day, hh = hour, and mm = minute.
The file name for a transaction backup is generated automatically with a
similar format:
dbname_log_yyyymmddhhmm.BAK
If you use the -BkUpDB parameter, you must also specify the media by using
the -BkUpMedia parameter.
-BkUpMedia
Specifies the media type of the backup.
DISK
Specifies that the backup medium is disk.
-DelBkUps <time_period>
Specifies that any backup file in the backup directory be deleted if the
time interval after the creation of the backup exceeds the <time_period>.
-CrBkSubDir
Specifies that a subdirectory be created in the [backup_path] directory or
in the default backup directory if -UseDefDir is also specified. The name of
the subdirectory is generated from the database name specified
in -D. -CrBkSubDir offers an easy way to put all the backups for different
databases into separate subdirectories without having to change the
[backup_path] parameter.
-UseDefDir
Specifies that the backup file be created in the default backup directory.
UseDefDir overrides [backup_path] if both are specified. With a default SQL
Server 2000 setup, the default backup directory is c:\Program
Files\Microsoft SQL Server\Mssql\Backup.
TAPE
Specifies that the backup medium is tape.
-BkUpOnlyIfClean
Specifies that the backup occur only if any specified -Ck checks did not
find problems with the data. Maintenance actions run in the same sequence as
they appear in the command prompt. Specify the
parameters -CkDB, -CkDBNoIdx, -CkAl, -CkAlNoIdx, -CkTxtAl, or -CkCat before
the -BkUpDB/-BkUpLog parameter(s) if you are also going to
specify -BkUpOnlyIfClean, or the backup will occur whether or not the check
reports problems.
-VrfyBackup
Specifies that RESTORE VERIFYONLY be run on the backup when it completes.
number[minutes | hours | days | weeks | months]
Specifies the time interval used to determine if a report or backup file is
old enough to be deleted. number is an integer. Valid examples are 12weeks,
3months, and 15days. If only number is specified, the default date part is
weeks.
Remarks
sqlmaint performs maintenance operations on one or more databases. If -D is
specified, the operations specified in the remaining switches are performed
only on the specified database. If -PlanName or -PlanID are specified, the
only information sqlmaint retrieves from the specified maintenance plan is
the list of databases in the plan. All operations specified in the remaining
sqlmaint parameters are applied against each database in the list obtained
from the plan. sqlmaint does not apply any of the maintenance activities
defined in the plan itself.
The sqlmaint utility returns 0 if it runs successfully, or 1 if it fails.
Failure is reported:
a.. If any of the maintenance actions fail.
b.. If -CkDB, -CkDBNoIdx, -CkAl, -CkAlNoIdx, -CkTxtAl, or -CkCat checks
find problems with the data.
c.. If a general failure is encountered.
For information about where to find or how to run this utility, see Getting
Started with Command Prompt Utilities.
Examples
A. Perform DBCC checks on the Northwind database
sqlmaint -S MyServer -U "sa" -P "SaPwd" -D Northwind -CkDB -CkAl -CkCat -Rpt
C:\MyReports\Nwind_chk.rpt
B. Update statistics using a 15% sample in all databases in a plan. Also,
shrink any of the database that have reached 110 MB to having only 10% free
space
sqlmaint -S MyServer -U "sa" -P "SaPwd" -PlanName MyUserDBPlan -UpdOptiStats
15 -RmUnusedSpace 110 10
C. Backup all the databases in a plan to their individual subdirectories in
the default x:\Program Files\Microsoft SQL Server\Mssql\Backup directory.
Also, delete any backups older than 2 weeks
sqlmaint -S MyServer -U "sa" -P "SaPwd" -PlanName
MyUserDBPlan -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks
This posting is provided "AS IS" without express or implied warranty,
guarantee, or rights.
"Brent" <anonymous@.discussions.microsoft.com> wrote in message
news:65260CDD-BA18-413D-800D-F47E7360FF10@.microsoft.com...
> Is there away to generate a script from DB Maintenance plans creation?
This will allow me to use the script to create many other DB maintenance
plans. I support a SQL Server 2000 instance with a ton of databases on it.
> Thanks ,
> Brent
>
begin 666 update_topic.gif
M1TE&.#EA$@.`6`/<`````````A ``_P!"0@."$A #_`$*$A(0`A(2$`(2$A(2$
M_\;&QO\``/__`/______________________________________________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_____________________RP`````$@.`6```(? `="!188*#!@.PX*$ARH$.'"
MA \=,H384"+#!046:-RHD6&!CQ@.3?E2XP$')@.P4K"CQYDF!(E0IBQEP8$B+"
M!0ILAI3)<V7.@.34EXC1XDJ=,GT0M(@.4JT.A,DS]7*H6:U('3GT.9*LTJU:K3
3I5TM<C4Y=2S'LQRC>KUJ-" `.P``
`
end
begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end
begin 666 coe.gif
M1TE&.#EA# `)`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9 O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $``*P`+ `````,``D`0 @.G`"T)'#B0E4&"
@." VRLK20X$*%# ]"G"BQXD.%%"]JC+@.Q(T>,' ,"`#L`
`
end
begin 666 securitynote.gif
M1TE&.#EA# `+`/<`````_\;&QO__________________________________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_________________________________________________ ___________
M_____________________R'Y! $```$`+ `````,``L```@.O``,('$B0(("#
H" $$."@.P(4.$#14.A!AQ(D.+$25B7*A0(\>%%3>&S.C0H,.+`0$` .P``
`
end
begin 666 coeb.gif
M1TE&.#EA# `)`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9 O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $``*P`+ `````,``D`0 @.G`",('#B0E4&"
@." VRBK"0X$*%# ]"G"BQXD.%%"]JC+@.Q(T>,' ,"`#L`
`
end
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment