Recovery Model Changing

Hello,
We have numerous SQL Servers that we backup using Veritas Backup Exec with
the SQL Server Agents. We have changed all of our database backup models to
FULL. We backup the transaction logs a few times through the day, and do a
full database backup once a night.
A few of our servers, the msdb database specifically, switch their recovery
mode to simple every once in a while. There doesnt seem to be a time pattern
we can follow, but it does seem to occur after one of our transaction log
backups.
Any ideas if this is a known problem with the msdb database, or if this is
caused by Backup Exec or by SQL Server?
Thanks,
JBaileyThat is the design of SQL Server and the Master and MSDB databases. There
is no real reason to take log backups of these when a Full backup can be
done in a matter of seconds.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have numerous SQL Servers that we backup using Veritas Backup Exec with
> the SQL Server Agents. We have changed all of our database backup models
to
> FULL. We backup the transaction logs a few times through the day, and do a
> full database backup once a night.
> A few of our servers, the msdb database specifically, switch their
recovery
> mode to simple every once in a while. There doesnt seem to be a time
pattern
> we can follow, but it does seem to occur after one of our transaction log
> backups.
> Any ideas if this is a known problem with the msdb database, or if this is
> caused by Backup Exec or by SQL Server?
> Thanks,
> JBailey
>|||So do the Master and MSDB databases change infrequently enough that
transaction logs backups are unnecessary?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> That is the design of SQL Server and the Master and MSDB databases. There
> is no real reason to take log backups of these when a Full backup can be
> done in a matter of seconds.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > We have numerous SQL Servers that we backup using Veritas Backup Exec
with
> > the SQL Server Agents. We have changed all of our database backup models
> to
> > FULL. We backup the transaction logs a few times through the day, and do
a
> > full database backup once a night.
> >
> > A few of our servers, the msdb database specifically, switch their
> recovery
> > mode to simple every once in a while. There doesnt seem to be a time
> pattern
> > we can follow, but it does seem to occur after one of our transaction
log
> > backups.
> >
> > Any ideas if this is a known problem with the msdb database, or if this
is
> > caused by Backup Exec or by SQL Server?
> >
> > Thanks,
> > JBailey
> >
> >
>|||Master rarely changes unless you add a new db or Login. MSDB stores a lot
of history for things like scheduled jobs, backups etc. so it can have
regular changes to it. A lot of people aren't worried as much about loosing
a few hours of history but it is up to each individual to determine that.
What I was getting at though is that to backup Master or MSDB fully usually
only takes a second or two so why bother to schedule log backups. If you
want to back up the db's every hour then just do a full backup. The only
thing it doesn't give you is point in time recovery but again that is rarely
what someone is seeking on these dbs.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> So do the Master and MSDB databases change infrequently enough that
> transaction logs backups are unnecessary?
> Thanks,
> JBailey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > That is the design of SQL Server and the Master and MSDB databases.
There
> > is no real reason to take log backups of these when a Full backup can be
> > done in a matter of seconds.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > We have numerous SQL Servers that we backup using Veritas Backup Exec
> with
> > > the SQL Server Agents. We have changed all of our database backup
models
> > to
> > > FULL. We backup the transaction logs a few times through the day, and
do
> a
> > > full database backup once a night.
> > >
> > > A few of our servers, the msdb database specifically, switch their
> > recovery
> > > mode to simple every once in a while. There doesnt seem to be a time
> > pattern
> > > we can follow, but it does seem to occur after one of our transaction
> log
> > > backups.
> > >
> > > Any ideas if this is a known problem with the msdb database, or if
this
> is
> > > caused by Backup Exec or by SQL Server?
> > >
> > > Thanks,
> > > JBailey
> > >
> > >
> >
> >
>|||Understood. Thanks for the insight.
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urOvXl6zDHA.1688@.TK2MSFTNGP10.phx.gbl...
> Master rarely changes unless you add a new db or Login. MSDB stores a lot
> of history for things like scheduled jobs, backups etc. so it can have
> regular changes to it. A lot of people aren't worried as much about
loosing
> a few hours of history but it is up to each individual to determine that.
> What I was getting at though is that to backup Master or MSDB fully
usually
> only takes a second or two so why bother to schedule log backups. If you
> want to back up the db's every hour then just do a full backup. The only
> thing it doesn't give you is point in time recovery but again that is
rarely
> what someone is seeking on these dbs.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> > So do the Master and MSDB databases change infrequently enough that
> > transaction logs backups are unnecessary?
> >
> > Thanks,
> > JBailey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > > That is the design of SQL Server and the Master and MSDB databases.
> There
> > > is no real reason to take log backups of these when a Full backup can
be
> > > done in a matter of seconds.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "JBailey" <abc@.123.com> wrote in message
> > > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > We have numerous SQL Servers that we backup using Veritas Backup
Exec
> > with
> > > > the SQL Server Agents. We have changed all of our database backup
> models
> > > to
> > > > FULL. We backup the transaction logs a few times through the day,
and
> do
> > a
> > > > full database backup once a night.
> > > >
> > > > A few of our servers, the msdb database specifically, switch their
> > > recovery
> > > > mode to simple every once in a while. There doesnt seem to be a time
> > > pattern
> > > > we can follow, but it does seem to occur after one of our
transaction
> > log
> > > > backups.
> > > >
> > > > Any ideas if this is a known problem with the msdb database, or if
> this
> > is
> > > > caused by Backup Exec or by SQL Server?
> > > >
> > > > Thanks,
> > > > JBailey
> > > >
> > > >
> > >
> > >
> >
> >
>

recovery mode

we back our network up using veritas backup exec. the
backup fails; it wants me to change the recovery mode from
simple to full. Will this cause problems on my SQL server?You should read in Books Online about backup and recovery models. This is a too big topic to handle
in a newsgroup post. Most probably it will be OK, but if you don't backup log often enough, it might
become quite large.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?|||Hi ,
It wont create any issues in your SQL Server, Only thing is
take a backup of your Transaction logs based on the transaction volume.
Otherwise since FULL recovary model logs all transaction happening in the
database, which will cause your Log file to grow like any thing.
Thanks
Hari
MCDBA
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?|||To add to the other responses, note that you'll need to perform a full
database backup after changing the recovery model from SIMPLE to FULL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9c01c3af60$edc3a8d0$a301280a@.phx.gbl...
> we back our network up using veritas backup exec. the
> backup fails; it wants me to change the recovery mode from
> simple to full. Will this cause problems on my SQL server?sql

Recovering a DB using sp_attach_db

Hi, All!
I'am trying to recover a database using sp_attach_db. I just have the .MDF
file. I tried this command, and receive this result:
EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
Server\MSSQL\Data\Contax.mdf'
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'contax'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
I don't have the LDF file, but I suppose that SQL Server would create a new
one. Besides, the physical path in the error message has two slashes (after
"Logs" directory), and I found it weird...
Could somebody help me?
Thanks a lot.
MarcosTry using sp_attach_single_file_db ... for the syntax look at BOL
Thanks
GYK
"Marcos Federicce" wrote:
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcos|||GYK,
I already tried sp_attach_single_file_db, but I got the same error message :)
Thanks anyway
Marcos
"GYK" wrote:
> Try using sp_attach_single_file_db ... for the syntax look at BOL
> Thanks
> GYK
> "Marcos Federicce" wrote:
> > Hi, All!
> >
> > I'am trying to recover a database using sp_attach_db. I just have the .MDF
> > file. I tried this command, and receive this result:
> >
> > EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> > Server\MSSQL\Data\Contax.mdf'
> >
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'contax'. CREATE DATABASE is aborted.
> > Device activation error. The physical file name
> > 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> >
> > I don't have the LDF file, but I suppose that SQL Server would create a new
> > one. Besides, the physical path in the error message has two slashes (after
> > "Logs" directory), and I found it weird...
> >
> > Could somebody help me?
> >
> > Thanks a lot.
> >
> > Marcos|||> I don't have the LDF file, but I suppose that SQL Server would create a new
> one.
It can, but only under certain circumstances, like db being detached first, only having one log file
etc. If those are not met, you need luck to get it to work. Apparently, it doesn't for you so I
recommend using your most recent backup(s) to get the database back.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marcos Federicce" <MarcosFedericce@.discussions.microsoft.com> wrote in message
news:EB863B21-0C15-4684-B985-CB0ABDD3DC93@.microsoft.com...
> Hi, All!
> I'am trying to recover a database using sp_attach_db. I just have the .MDF
> file. I tried this command, and receive this result:
> EXEC sp_attach_db 'Contax', 'C:\Arquivos de programas\Microsoft SQL
> Server\MSSQL\Data\Contax.mdf'
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'contax'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'D:\Bancos\Logs\\contax_log.LDF' may be incorrect.
> I don't have the LDF file, but I suppose that SQL Server would create a new
> one. Besides, the physical path in the error message has two slashes (after
> "Logs" directory), and I found it weird...
> Could somebody help me?
> Thanks a lot.
> Marcossql