Scripting a change in the recovery model

Hi,
Is there a way that I can automatically switch my database recoverymodel
from Simple to Full and vice versa so that I can plan this swict through
Scheduled tasks?Hi
Execute a T-SQL task in a agent job.
Just be aware, changing the recovery mode affects your ability to do point
in time restores, so after you set the mode back to full, do a full DB
backup otherwise your subsequent transaction log backups are worthless.
Why would you want to do such a thing as it does not affect how SQL Server
uses the log? Everything is still logged, you just throw away
recoverability.
Full recovery:
ALTER DATABASE <dbname> SET RECOVERY FULL
and back to simple:
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"altrust" <altrust@.discussions.microsoft.com> wrote in message
news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> Hi,
> Is there a way that I can automatically switch my database recoverymodel
> from Simple to Full and vice versa so that I can plan this swict through
> Scheduled tasks?|||Thx Mike
I want to change it because every weekend between 02:00 and 04:00 the
customer sends a huge job that completely eats out my transaction log disk
(60GB)
I will try your solution.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Execute a T-SQL task in a agent job.
> Just be aware, changing the recovery mode affects your ability to do point
> in time restores, so after you set the mode back to full, do a full DB
> backup otherwise your subsequent transaction log backups are worthless.
> Why would you want to do such a thing as it does not affect how SQL Server
> uses the log? Everything is still logged, you just throw away
> recoverability.
> Full recovery:
> ALTER DATABASE <dbname> SET RECOVERY FULL
> and back to simple:
> ALTER DATABASE <dbname> SET RECOVERY SIMPLE
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "altru?st" <altrust@.discussions.microsoft.com> wrote in message
> news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
>
>|||altrust wrote:
> Thx Mike
> I want to change it because every weekend between 02:00 and 04:00 the
> customer sends a huge job that completely eats out my transaction log disk
> (60GB)
>
You could create another maintenance plan that only did transaction log back
ups and schedule it to run very frequently
between 02:00 and 04:00 on weekends only.|||Hi
If it is one job and one batch, it might be one big transaction, and in that
case, until the transaction is committed or rolled back, the log info stays
in the log and can't be released. Check the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:haepf.39012$L7.9595@.fe12.lga...
> altrust wrote:
> You could create another maintenance plan that only did transaction log
> backups and schedule it to run very frequently between 02:00 and 04:00 on
> weekends only.
>|||I tried this option but it didn't work...
It filled up anyway.
"Ed Enstrom" wrote:

> altru?st wrote:
> You could create another maintenance plan that only did transaction log ba
ckups and schedule it to run very frequently
> between 02:00 and 04:00 on weekends only.
>

Scripting a change in the recovery model

Hi,
Is there a way that I can automatically switch my database recoverymodel
from Simple to Full and vice versa so that I can plan this swict through
Scheduled tasks?
Hi
Execute a T-SQL task in a agent job.
Just be aware, changing the recovery mode affects your ability to do point
in time restores, so after you set the mode back to full, do a full DB
backup otherwise your subsequent transaction log backups are worthless.
Why would you want to do such a thing as it does not affect how SQL Server
uses the log? Everything is still logged, you just throw away
recoverability.
Full recovery:
ALTER DATABASE <dbname> SET RECOVERY FULL
and back to simple:
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"altrust" <altrust@.discussions.microsoft.com> wrote in message
news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> Hi,
> Is there a way that I can automatically switch my database recoverymodel
> from Simple to Full and vice versa so that I can plan this swict through
> Scheduled tasks?
|||Thx Mike
I want to change it because every weekend between 02:00 and 04:00 the
customer sends a huge job that completely eats out my transaction log disk
(60GB)
I will try your solution.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Execute a T-SQL task in a agent job.
> Just be aware, changing the recovery mode affects your ability to do point
> in time restores, so after you set the mode back to full, do a full DB
> backup otherwise your subsequent transaction log backups are worthless.
> Why would you want to do such a thing as it does not affect how SQL Server
> uses the log? Everything is still logged, you just throw away
> recoverability.
> Full recovery:
> ALTER DATABASE <dbname> SET RECOVERY FULL
> and back to simple:
> ALTER DATABASE <dbname> SET RECOVERY SIMPLE
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "altru?st" <altrust@.discussions.microsoft.com> wrote in message
> news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
>
>
|||altrust wrote:
> Thx Mike
> I want to change it because every weekend between 02:00 and 04:00 the
> customer sends a huge job that completely eats out my transaction log disk
> (60GB)
>
You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
between 02:00 and 04:00 on weekends only.
|||Hi
If it is one job and one batch, it might be one big transaction, and in that
case, until the transaction is committed or rolled back, the log info stays
in the log and can't be released. Check the job.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:haepf.39012$L7.9595@.fe12.lga...
> altrust wrote:
> You could create another maintenance plan that only did transaction log
> backups and schedule it to run very frequently between 02:00 and 04:00 on
> weekends only.
>
|||I tried this option but it didn't work...
It filled up anyway.
"Ed Enstrom" wrote:

> altru?st wrote:
> You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
> between 02:00 and 04:00 on weekends only.
>

Scripting a change in the recovery model

Hi,
Is there a way that I can automatically switch my database recoverymodel
from Simple to Full and vice versa so that I can plan this swict through
Scheduled tasks?Hi
Execute a T-SQL task in a agent job.
Just be aware, changing the recovery mode affects your ability to do point
in time restores, so after you set the mode back to full, do a full DB
backup otherwise your subsequent transaction log backups are worthless.
Why would you want to do such a thing as it does not affect how SQL Server
uses the log? Everything is still logged, you just throw away
recoverability.
Full recovery:
ALTER DATABASE <dbname> SET RECOVERY FULL
and back to simple:
ALTER DATABASE <dbname> SET RECOVERY SIMPLE
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"altruïst" <altrust@.discussions.microsoft.com> wrote in message
news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> Hi,
> Is there a way that I can automatically switch my database recoverymodel
> from Simple to Full and vice versa so that I can plan this swict through
> Scheduled tasks?|||Thx Mike
I want to change it because every weekend between 02:00 and 04:00 the
customer sends a huge job that completely eats out my transaction log disk
(60GB)
I will try your solution.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Execute a T-SQL task in a agent job.
> Just be aware, changing the recovery mode affects your ability to do point
> in time restores, so after you set the mode back to full, do a full DB
> backup otherwise your subsequent transaction log backups are worthless.
> Why would you want to do such a thing as it does not affect how SQL Server
> uses the log? Everything is still logged, you just throw away
> recoverability.
> Full recovery:
> ALTER DATABASE <dbname> SET RECOVERY FULL
> and back to simple:
> ALTER DATABASE <dbname> SET RECOVERY SIMPLE
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "altruïst" <altrust@.discussions.microsoft.com> wrote in message
> news:F410A58B-B052-4E34-B21A-4E2DB8498A7A@.microsoft.com...
> > Hi,
> >
> > Is there a way that I can automatically switch my database recoverymodel
> > from Simple to Full and vice versa so that I can plan this swict through
> > Scheduled tasks?
>
>|||altruïst wrote:
> Thx Mike
> I want to change it because every weekend between 02:00 and 04:00 the
> customer sends a huge job that completely eats out my transaction log disk
> (60GB)
>
You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
between 02:00 and 04:00 on weekends only.|||Hi
If it is one job and one batch, it might be one big transaction, and in that
case, until the transaction is committed or rolled back, the log info stays
in the log and can't be released. Check the job.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:haepf.39012$L7.9595@.fe12.lga...
> altruïst wrote:
>> Thx Mike
>> I want to change it because every weekend between 02:00 and 04:00 the
>> customer sends a huge job that completely eats out my transaction log
>> disk (60GB)
> You could create another maintenance plan that only did transaction log
> backups and schedule it to run very frequently between 02:00 and 04:00 on
> weekends only.
>|||I tried this option but it didn't work...
It filled up anyway.
"Ed Enstrom" wrote:
> altruïst wrote:
> > Thx Mike
> >
> > I want to change it because every weekend between 02:00 and 04:00 the
> > customer sends a huge job that completely eats out my transaction log disk
> > (60GB)
> >
> You could create another maintenance plan that only did transaction log backups and schedule it to run very frequently
> between 02:00 and 04:00 on weekends only.
>

Script to automatically schedule SSIS tasks?

Hi,

I have a SQL 2000 script which I use to automatically schedule various backup tasks. This script adds and schedules a full backup once a week, differential backups nightly, and log backups hourly, in addition to a couple other maintenance tasks such as rebuilding indexes.

The idea is that a less technically savvy person can set a few variables at the top of the script (such as DB name and backup file folders) and click 'execute' to run the script and schedule the backups etc all in one go, for different clients.

Since some of the stored procs I use in this script are deprecated in SQL 2005, I am trying to replicate this functionality in SSIS but am having trouble figuring out how I can get all of this functionality encapsulated in the same 'click and go' manner where the user can simply execute the package and all the jobs will be scheduled without any user interaction.

Is this even possible? Where should I be looking for examples of how to do this?

Thanks!

dtexecui.exe is the closest thing there is to a "click-and-go" interface. I suggest you evaluate that.

-Jamie

|||Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.
|||

graemeo wrote:

Yes, I was assuming that's how I would run the package, but what I am trying to figure out is how to set up scheduling of the different jobs within the package so that when I run it, they are all scheduled at the correct times (like one job is scheduled for nightly, one for weekly, etc etc).

There does not seem to be a 'scheduler' task in the toolbox of VS.

If you want to use a scheduler then use SQL Server Agent. There is no scheduler within a SSIS package and nor should there be.

-Jamie

|||Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?
|||

graemeo wrote:

Is there any way to do what I am attempting (provide the user with a simple script to schedule different backup jobs at varying times?) in SQL 2005 without using deprecated SP's?

Yes. This is a question about SQL Server agent (i.e. the scheduler). There are a bunch of sprocs dedicated to maintenance of SQL Server Agent jobs.

http://search.live.com/results.aspx?q=agent+stored+procedures&form=QBRE&q1=macro%3Asql_server_user_education.booksonline

-Jamie

Script SP with permission automatically

Hi,

In enterprise manager, there is a option to automatically script any trigger, permission on a table or store procedure. It seems that this feature is gone from new the SQL management studio. Is there a way to easy script this easily just like before?

thanks,

Bernie

Hi there,

Well, you can script triggers by going to the specific table in the Object Explorer and expanding the node for that table....There should be a sub-node for triggers which you can expand to see the triggers defined on the table. Right click on the desired trigger and select "Script Trigger As" from the context menu that appears.

Note that you can take the same action for most of the other sub-nodes you see under the table such as Constraints, Indexes and Keys.

As for scripting objects with permissions, I believe that you will need to use the Script Wizard to do this. To access the wizard you right click on a database and from the context menu that appears you select: Tasks > Generate Scripts

The Script Wizard will now appear. You can run through it as follows:

1) Click the "Next>" button on the initial screen

2) Select the database you want to script and then click the "Next>" button. You can also tick the "Script All Objects In The Selected Database" option if you want to script absolutely everything

3) You will be prompted to select scripting options such as scripting object permissions, table triggers and constraints etc. Review the options and select the ones that are right for you

4) If you did not select the "Script All Objects In The Selected Database" option, you will now get to choose which database objects (e.g. stored procedures) to script and will run through a series of screens in order to make your selection

It's pretty much smooth sailing after that.

Hope that helps a bit, but sorry if it doesn't

|||

sql server management studio 2005 has a better scripting support than sql server enterprise manager 2000.

you can right click the object and the choose "script object to ; alter ; create ; delete;"

yet another way to do it is to right click the object and then choose properties

on the top portion of the properties tab there's a clcikable script dropdown which allows you to script to clipboard, file, jobs or to new query window. on the left hand side of the properties window there's a listbox with the following item geneneral,permission, extended properties. to script permission, choose permission from the list and then click the script dropdown

here's another

You can also right click the database then task then choose then choose generate scripts. this will lunch the scripts wizard which is somewaht similar to those of the EM