question re: maintenance

Ok, I want to write a script that

1. backups my sql database
2. commits the transactionlog
3. shrinks the db
4. shrinks the db log

does anyone have a script that will do this or can show me the light?

thank you! (sorry, i'm a programmer and not a very good dba regarding maintenance!)SQL Server 2000?

I would recommend creating a maintenance plan in Enterprise Manager, as it is easier than scripting the whole thing out. Open the database in question and set to TaskPad view is the easiest. You can then click on the dropdown second from the bottom and choose maintenance plan.

I will have to look, as I am not sure this does 100% of what you want. If not, you can set up a job with multiple steps and run the stored procedures necessary to backup, et al. The SQL Books Online (installed with client tools) is a great source of knowledge.

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.

I have to set up database maintenance scripts, db and log backups, optimizations etc in batch files so that they can be run by an external scheduler.

When I produce a script from the SQL Server database maintenance wizards and save it off in a bat file, the script will not work when run manually, probably due to the difference in syntax.

I have some examples of backup scripts using sqlmaint.exe, but can't find anything on how to script optimization/reorg jobs so that they can be run by an external scheduler.

Can anybody point me to any docs or guidance on how to produce backup and reorg scripts that can be run externally?

Thanks in advance.
GerryIt sounds like you want to use the osql utility. Go to BOL and look it up uner the index tab. When you double-click on the index utility, chose the "osql utility" under the Command Pronpt Utilities.

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
Gerry
Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>
|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegr oups.com...
|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instea
d of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly
. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just l
et the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execu
te that package.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So inst
ead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe direct
ly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just
let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to exe
cute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has tha
t feature. Can you
elaborate?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has t
hat feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE T
ABLE or DELETE command
using the /Q switch for OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE
TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on db maintenance scripts

Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has that feature. Can you
elaborate?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
>> better control and options.
>> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>>
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:
> > How would you approach a db maintenance job that was job dependent?
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> > Tibor:
> >
> > Thanks for the advice. I will do that.
> >
> > On 2005 we have a scheduler (Control M) running business processing
> > jobs outside of SQL Server and I will have to schedule jobs that are
> > dependent on these jobs finishing.
> >
> > How would you approach a db maintenance job that was job dependent?
> >
> >
> > Tibor Karaszi wrote:
> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> >> better control and options.
> >>
> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
> >>
> >>
> >|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
using the /Q switch for OSQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>> > How would you approach a db maintenance job that was job dependent?
>>
>> Not sure I understand your question, as Control M apparently already has that feature. Can you
>> elaborate?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
>> > Tibor:
>> >
>> > Thanks for the advice. I will do that.
>> >
>> > On 2005 we have a scheduler (Control M) running business processing
>> > jobs outside of SQL Server and I will have to schedule jobs that are
>> > dependent on these jobs finishing.
>> >
>> > How would you approach a db maintenance job that was job dependent?
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you
>> >> much
>> >> better control and options.
>> >>
>> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>> >>
>> >>
>> >
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
Gerry
Hi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegr oups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi

> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.

Question on data optimizations

The database maintenance plan wizards present the ability to do either
data/index page reorgs or update statistics.
Is there any reason you can't do both?
Is it because an update of the statistics is automatically done on a
reorg?
Thanks in advance.
GerryHi
> Is it because an update of the statistics is automatically done on a
> reorg?
When SQL Server rebuild indexes ,statistics ares automatically to be
updated
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||DataPro wrote:
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>
Reindexing will update the statistics...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hello,
You are correct; Statistics will be updated as part of reindex.
Thanks
Hari
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164806696.954151.173330@.l39g2000cwd.googlegroups.com...
> The database maintenance plan wizards present the ability to do either
> data/index page reorgs or update statistics.
> Is there any reason you can't do both?
> Is it because an update of the statistics is automatically done on a
> reorg?
> Thanks in advance.
> Gerry
>|||Thanks much to all.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME => N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>> Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
>> know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
>> the database(s) selected in the plan.
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
.xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BCRun a Profiler trace and you will see the TSQL commands submitted from the m
aint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted d
oes a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.ph
x.gbl...
>I have a backup of a database that has been running for quite a while, succ
essfully.
> I went in to the Database Maintenance plan, and created a new plan to opti
mize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job
section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to ha
ving a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH IN
IT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyI
fClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, bu
t
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.|||I realized that after posting. I was confused by the missing BACKUP DATABASE
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.ph
x.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

Question on backups

I have a backup of a database that has been running for quite a while,
successfully.
I went in to the Database Maintenance plan, and created a new plan to
optimize, check the integrity and do a complete backup (this created 3
new jobs out in the job section of SQL).
Here's the TSQL code from both.
Can anyone tell me if they're doing much the same thing with regards to
having a restore-able backup?
[sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT
, NOUNLOAD , NAME = N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
in the Tsql in the job.
the new one (the backup only) is:
..xp_sqlmaint N'-PlanID abcdefgh-ijkl-mnop-qrst-uvwxyz012345
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK
-BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
Thanks,
BC
Run a Profiler trace and you will see the TSQL commands submitted from the maint wizard. I don't
know what [sfa] is supposed to symbolize, but the maint plan as posted does a database backup for
the database(s) selected in the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:OIWww4mdFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a backup of a database that has been running for quite a while, successfully.
> I went in to the Database Maintenance plan, and created a new plan to optimize, check the
> integrity and do a complete backup (this created 3 new jobs out in the job section of SQL).
> Here's the TSQL code from both.
> Can anyone tell me if they're doing much the same thing with regards to having a restore-able
> backup?
> [sfa] TO DISK = N'\\CORP-SISDB\MSSQL7\BACKUP\SFA backup.BAK' WITH INIT , NOUNLOAD , NAME =
> N'BACKUP SFA DB', SKIP , STATS = 10, NOFORMAT
> in the Tsql in the job.
> the new one (the backup only) is:
> .xp_sqlmaint N'-PlanID
> abcdefgh-ijkl-mnop-qrst-uvwxyz012345 -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB "d:\MSSQL\BACKUP" -DelBkUps 1DAYS -BkExt "BAK"'
> Thanks,
> BC
|||Tibor Karaszi wrote:
> Run a Profiler trace and you will see the TSQL commands submitted from
> the maint wizard. I don't know what [sfa] is supposed to symbolize, but
> the maint plan as posted does a database backup for the database(s)
> selected in the plan.
>
sfa in our case is the database name.
|||I realized that after posting. I was confused by the missing BACKUP DATABASE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Blasting Cap" <goober@.christian.net> wrote in message news:elODCendFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> sfa in our case is the database name.

Question of SQLAgentMail and SQL Mail

Hi,
I need monitoring scheduling jobs, alerts and database
maintenance from daily manually checks to automation. If
any of them failed while execute, it should be
automatically email to operator. I knew it can be done by
configuring SQLAgentMail and SQL Mail, but how to
configuring? Any particular software need install such as
MS exchange server to the production SQL Server or other
requests? Also can anyone tell which link I should to
follow?
Thanks for all the help!
-Jennyhttp://www.aspfaq.com/2403
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jenny" <jey@.iseoptions.com> wrote in message
news:601201c474df$220c7fb0$a401280a@.phx.gbl...
> Hi,
> I need monitoring scheduling jobs, alerts and database
> maintenance from daily manually checks to automation. If
> any of them failed while execute, it should be
> automatically email to operator. I knew it can be done by
> configuring SQLAgentMail and SQL Mail, but how to
> configuring? Any particular software need install such as
> MS exchange server to the production SQL Server or other
> requests? Also can anyone tell which link I should to
> follow?
> Thanks for all the help!
> -Jenny
>|||Have a look at
SQL Mail
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315886
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321183
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q263556
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311231
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q281293
Support Web Cast
http://support.microsoft.com/servicedesks/webcasts/wc091301/wcblurb091301.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jenny" <jey@.iseoptions.com> wrote in message
news:601201c474df$220c7fb0$a401280a@.phx.gbl...
> Hi,
> I need monitoring scheduling jobs, alerts and database
> maintenance from daily manually checks to automation. If
> any of them failed while execute, it should be
> automatically email to operator. I knew it can be done by
> configuring SQLAgentMail and SQL Mail, but how to
> configuring? Any particular software need install such as
> MS exchange server to the production SQL Server or other
> requests? Also can anyone tell which link I should to
> follow?
> Thanks for all the help!
> -Jenny
>