Backing Up Sys Databases

I currently back up the Master and MSDB sys databases, why do some people say
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing Up Sys Databases

I currently back up the Master and MSDB sys databases, why do some people sa
y
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing up log when recovery set to Simple

I have a job that I created that backs up transaction logs on a server that has many databases on it. If a db recovery model is set to simple, I want the job to skip over that database, rather then trying to backup the log.

It would be easy to just let it try, and fail, however, the job status will show failed, even if it backs up all database logs, and only fails on one. Is there a way to easily programatically detect if a db is set to simple, and then I can skip backing up that db log?

Thanks
TommySELECT DATABASEPROPERTYEX('Northwind','Recovery')|||Thanks brett, that's what I needed, in case anyone else is interested, here's the syntax I'm using:

Declare @.Recovery_Model nvarchar(20),
Select @.Recovery_Model = CONVERT(nvarchar(20), DATABASEPROPERTYEX( @.Database_Name , 'Recovery' ))
if (@.Recovery_Model = 'SIMPLE')
PRINT 'Simple Mode so skip'
else
PRINT 'do the needful'

Avoid splitting trees by value missing

Hi, could anyone kindly let me know how to prevent the Decision Trees model from using value missing as a criterion to split trees?

Thanks,

hz

Decision trees only splits on missing if you have sparse data. Are you using nested tables possibly? If not, then you have a column in your table with enough NULLs that are correlated with your target that there is enough information gain to cause a split. You can use NOT NULL, but this will just prevent the model from processing altogether if nulls are present|||

Yes, unfortunately, there are a lot of null values. I am not sure what you mean by "use NOT NULL". I have many predictor columns. Their null values are not in sync. If "NOT NULL" is used as a filter for every predictor, there may be no case left.

|||Unfortunately there's no way to not split by a value that appears in the data in SQL 2005. It's something for us to think about for future versions (expecially the "NULL" case)

Avoid splitting trees by value missing

Hi, could anyone kindly let me know how to prevent the Decision Trees model from using value missing as a criterion to split trees?

Thanks,

hz

Decision trees only splits on missing if you have sparse data. Are you using nested tables possibly? If not, then you have a column in your table with enough NULLs that are correlated with your target that there is enough information gain to cause a split. You can use NOT NULL, but this will just prevent the model from processing altogether if nulls are present|||

Yes, unfortunately, there are a lot of null values. I am not sure what you mean by "use NOT NULL". I have many predictor columns. Their null values are not in sync. If "NOT NULL" is used as a filter for every predictor, there may be no case left.

|||Unfortunately there's no way to not split by a value that appears in the data in SQL 2005. It's something for us to think about for future versions (expecially the "NULL" case)