Hi Franz,
I am pretty new to SQL Server, and have been tasked with setting up backups for all the SQL Server on our network (approx 50 servers).
I have come across this issue where because a DB is in Simple recovery mode, the T-log will not be backed up.
I understand what you are saying about the system DB's, but what about User DB's?
If they are set to Simple, is it a good idea to change them to Full?? Is there any reasons, in general, that a User DB should not be set to Full??
Regards, John
-----Original Message-----
From: Franz Zoister
[mailto:mssqldba-ezmlmshield-x70673744.[Email address protected]
Sent: 30 March 2005 21:20
To: LazyDBA Discussion
Subject: RE: system t-log errors out
Hi Brian,
I think you should not alter the recovery model of the system databases unless you have a really good reason to do this.
Its perfectly fine to have master and msdb and especially the tempdb in simple recovery model.
The model database should not be changed unless you want to have other default settings when creating a new database. The model database is only used as a template when creating a new empty database (it looks like the model database).
You cannot backup transaction log of databases in simple mode because they dont have transaction log history.
The transaction log file of such databases contains only information to rollback currently running transactions.
In general it is highly recommendet to make a full backup of master and msdb on a daily basis !
If you have changed the model database you should have have a backup plan that at least garantees you that you have a correct model db when you need it (after losing the current one or after installation of a new db server), if you dont change the model database (most people dont change it) you dont need a backup of it.
The tempdb needs no backup.
I hope this helps you,
regards Franz
-----Original Message-----
From: Trevor Weir
[mailto:mssqldba-ezmlmshield-x72894726.[Email address protected]
Sent: Mittwoch, 30. März 2005 20:25
To: LazyDBA Discussion
Subject: RE: system t-log errors out
Sorry Brian, but I should have seen this one coming. If you look in the BOL
under "backing up databases", subgroup "master database" it says (and I
forgot all about it) "Note Only full database backups of master can be
created."
Trevor
-----Original Message-----
From: Zelli Brian
[mailto:mssqldba-ezmlmshield-x52802457.[Email address protected]
Sent: Wednesday, March 30, 2005 11:43 AM
To: LazyDBA Discussion
Subject: RE: system t-log errors out
I set the msdb, master and model to full. When I run it again and look in
the logs, model and msdb seem to work but I get nothing for master and the
job errors.
ciao,
Brian
-----Original Message-----
From: Trevor Weir
[mailto:mssqldba-ezmlmshield-x26336689.[Email address protected]
Sent: Wednesday, March 30, 2005 10:19 AM
To: LazyDBA Discussion
Subject: RE: system t-log errors out
Are they set as simple mode? If so you cannot do a log backup. You would
need to go to into Enterprise Manager, right click on the database, go to
properties, options tab and set the recovery mode to full or bulk-logged in
order to perform log backups (I would recommend full).
-----Original Message-----
From: Zelli Brian
[mailto:mssqldba-ezmlmshield-x95497460.[Email address protected]
Sent: Wednesday, March 30, 2005 10:09 AM
To: LazyDBA Discussion
Subject: system t-log errors out
I set up a job to backup the transaction logs of the master, model, and msdb
databases. It errors but I can't see why. I've looked in the logs. Also
when I add the transaction logs to the full backup, it errors. Still can't
find why. Do I not need to back them up or won't the system let me or do I
have to do each individually?
ciao,
Brian
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
The information contained in this e-mail may be confidential and is intended solely for the use of the named addressee.
Access, copying or re-use of the e-mail or any information contained therein by any other person is not authorized.
If you are not the intended recipient please notify us immediately by returning the e-mail to the originator.(16b)
MS Sql Server LazyDBA home page