REcovery model for a backup/restore operation

Hello,
I want to do a backup and restore operations of my base, using SQLCMD.exe.
For that, I use backup database mybase to disk='File'
Then, I try to do a backup of transact log but like this:
backup log mybase to disk='File'
But I have this message : Cannot do a backup log on database which is a
simple recovery model.
I read the BOL and I saw there are to type of recovery model: Simple and
full. But I didn't understand very well explications in BOL.
My questions are:
What are this models?
When are they used ?
ThanksIn simple mode, not very much is logged in the transaction log. In full,
almost everything is logged in the transaction log. Thats the basic
difference. Theres a whole lot more to it, so it wouldn be a bad idea to
read up on it.
oh yeah, theres also bulk logged recovery model :)
MC
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>|||Hi, bubixx.
http://vyaskn.tripod.com/ sql_serve...ices
.htm
In this URL you will find a very good white paper about backups and other
administration practices.
Good luck.
Pau.
"bubixx" <bubixx@.discussions.microsoft.com> escribi en el mensaje
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>|||> In simple mode, not very much is logged in the transaction log. In full, almost everythin
g is
> logged in the transaction log.
IMO, that is too much of a simplification. For all but a few special operati
ons, the amount logged
is the same for all recovery models. In simple, SQL Server will by itself em
pty the log each time it
does a checkpoint. In full mode, you have to empty the log using BACKUP LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uOAqxL07FHA.2092@.TK2MSFTNGP12.phx.gb
l...
> In simple mode, not very much is logged in the transaction log. In full, a
lmost everything is
> logged in the transaction log. Thats the basic difference. Theres a whole
lot more to it, so it
> wouldn be a bad idea to read up on it.
> oh yeah, theres also bulk logged recovery model :)
>
> MC
> "bubixx" <bubixx@.discussions.microsoft.com> wrote in message
> news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
>|||Well, thank you for that.
I thought to simplify as much as I can, since I didnt think that I should go
into explaining the transaction log as such. You managed to write a really
short explanation though, I'll have to use it next time someone asks me to
explain the difference ;).
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDetSr17FHA.2816@.tk2msftngp13.phx.gbl...
> IMO, that is too much of a simplification. For all but a few special
> operations, the amount logged is the same for all recovery models. In
> simple, SQL Server will by itself empty the log each time it does a
> checkpoint. In full mode, you have to empty the log using BACKUP LOG.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uOAqxL07FHA.2092@.TK2MSFTNGP12.phx.gbl...
>|||> I thought to simplify as much as I can, since I didnt think that I should go into explain
ing the
> transaction log as such.
I figured that was the case. :-)

> , I'll have to use it next time someone asks me to explain the difference ;).[/col
or]
Feel free... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:%23Sk2Iv17FHA.3660@.TK2MSFTNGP09.phx.
gbl...
> Well, thank you for that.
> I thought to simplify as much as I can, since I didnt think that I should
go into explaining the
> transaction log as such. You managed to write a really short explanation t
hough, I'll have to use
> it next time someone asks me to explain the difference ;).
>
> MC
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eDetSr17FHA.2816@.tk2msftngp13.phx.gbl...
>|||Are you the admin of this server? I assume since you are performing backups.
If you are not sure about the difference between 'full' and 'simple'
recovery and how it affects backups, then it would be best to leave the
recovery model at it's default of 'full'.
SQL Server 2000 Administrator's Pocket Consultant: Database Backup and
Recovery
http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
SQL Server 2000 Operations Guide: System Administration
http://www.microsoft.com/technet/pr...in/sqlops4.mspx
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:A0AF25DC-B42D-4AF4-9C3E-0F5722B689F8@.microsoft.com...
> Hello,
> I want to do a backup and restore operations of my base, using SQLCMD.exe.
> For that, I use backup database mybase to disk='File'
> Then, I try to do a backup of transact log but like this:
> backup log mybase to disk='File'
> But I have this message : Cannot do a backup log on database which is a
> simple recovery model.
> I read the BOL and I saw there are to type of recovery model: Simple and
> full. But I didn't understand very well explications in BOL.
> My questions are:
> What are this models?
> When are they used ?
> Thanks
>

Recovery model backup and restore database

I want to do a backup and restore operations of my base, using SQLCMD.exe.

For that, I use backup database mybase to disk='File'

Then, I try to do a backup of transact log but like this:

backup log mybase to disk='File'

But I have this message : Cannot do a backup log on database which is a simple recovery model.

I read the BOL and I saw there are to type of recovery model: Simple and full. But I didn't understand very bien explications in BOL.

My questions are:

What is this models?

When are they used ?

ThanksRecovery model determines how the transaction log files are handled in the database. This is in turn affects performance, log file usage, backup/recovery, amount of data loss etc. Please refer to topic below for overview:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/606e0487-0c76-4875-af44-ef763cc5a73a.htm

You should also look at the transaction log architecture topic and why it is necessary in a relational database system.

recovering deleted table

Hi all
Is there a way to reconstruct a table which was deleted without restoring
the whole data base?
Database recovery model is simple. Sqlserver version is 2000 sp3.
TIA
ReaYou can restore the database from the backup to a different location with a
new name, and then copy the table with DTS from the restored database to
your production database.
--
Jacco Schalkwijk
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:eys9$rjQEHA.2132@.TK2MSFTNGP11.phx.gbl...
> Hi all
> Is there a way to reconstruct a table which was deleted without restoring
> the whole data base?
> Database recovery model is simple. Sqlserver version is 2000 sp3.
> TIA
> Rea
>|||The restore you do could also be a partial restore if you have the database
in multiple files ( See Restore in BOL).
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OIJuPzjQEHA.1388@.TK2MSFTNGP09.phx.gbl...
> You can restore the database from the backup to a different location with
a
> new name, and then copy the table with DTS from the restored database to
> your production database.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:eys9$rjQEHA.2132@.TK2MSFTNGP11.phx.gbl...
> > Hi all
> > Is there a way to reconstruct a table which was deleted without
restoring
> > the whole data base?
> > Database recovery model is simple. Sqlserver version is 2000 sp3.
> > TIA
> > Rea
> >
> >
>