Basic backup question

I'm doing a full backup of a SQL Server database with SSMS. When I try to
restore it I get the following exception:
*******
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "MYBACKUP" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*******
What is this message?
Should I backup the log before backing up the database?
Is the database backup enought to recover data?
Thanks!
GasparA couple of questions first:
Are you doing regular transaction log backups?
If not, is there a reason for you to have the database in full recovery model?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
> following exception:
> *******
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or
> WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> *******
> What is this message?
> Should I backup the log before backing up the database?
> Is the database backup enought to recover data?
> Thanks!
> Gaspar
>|||Yes. I have schedules to backup the transaction log every 6 hours. I don't
know if this is a good practice.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>A couple of questions first:
> Are you doing regular transaction log backups?
> If not, is there a reason for you to have the database in full recovery
> model?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to
>> restore it I get the following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up.
>> Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you
>> do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the
>> RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>|||> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a good
> practice.
This we can't answer since we don't know how much data your organization can tolerate to lose in
case of a disaster...
Anyhow, I do now know you do log backups. OK, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if you
perform the restore. The error is SQL Server's way of reminding you that you probably want to do a
log backup first:
9 Backup log
When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do RESTORE
*without* doing this last log backup, you can use the REPLACE parameter for the RESTORE command.
This behavior (the "reminder") is new for 2005, btw.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a good
> practice.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
>> following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
>> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE
>> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>|||So, this reminder "The tail of the log for the database "MYBACKUP" has not
been backed up" is just letting me know that after I backuped up the data,
some changes where done.
Suppose I don't care about this changes (my compain can "recover" from a
one-day data loss):
1) Can I restore by only having the database backup?
2) How to avoid this message from appearing?
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I
>> don't know if this is a good practice.
> This we can't answer since we don't know how much data your organization
> can tolerate to lose in case of a disaster...
> Anyhow, I do now know you do log backups. OK, consider below:
> 1 Backup db
> 2 Users doing modifications
> 3 Backup log
> 4 Users doing modifications
> 5 Backup log
> 6 Users doing modifications
> 7 Backup log
> 8 Users doing modifications
> And you now want to do RESTORE. But what about the modifications in step
> 8? This will be lost if you perform the restore. The error is SQL Server's
> way of reminding you that you probably want to do a log backup first:
> 9 Backup log
> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If
> you *want* to do RESTORE *without* doing this last log backup, you can use
> the REPLACE parameter for the RESTORE command.
> This behavior (the "reminder") is new for 2005, btw.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Gaspar" <noreply@.nospamplease.com> wrote in message
> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I
>> don't know if this is a good practice.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery
>> model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try
>> to restore it I get the following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up.
>> Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work
>> you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of
>> the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>>
>|||> So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> letting me know that after I backuped up the data, some changes where done.
Yes, the message states that after your last backup (database or log backup) there has been
modifications in the database.
> Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> 1) Can I restore by only having the database backup?
Yes. Again, consider below:
1 Backup db
2 Users doing modifications
3 Backup log
4 Users doing modifications
5 Backup log
6 Users doing modifications
7 Backup log
8 Users doing modifications
You can restore from only 1. Of course you will lose all modifications done after 1.
> 2) How to avoid this message from appearing?
Specify the REPLACE option for your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gaspar" <noreply@.nospamplease.com> wrote in message news:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.gbl...
> So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> letting me know that after I backuped up the data, some changes where done.
> Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> 1) Can I restore by only having the database backup?
> 2) How to avoid this message from appearing?
> Thanks again
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a
>> good practice.
>> This we can't answer since we don't know how much data your organization can tolerate to lose in
>> case of a disaster...
>> Anyhow, I do now know you do log backups. OK, consider below:
>> 1 Backup db
>> 2 Users doing modifications
>> 3 Backup log
>> 4 Users doing modifications
>> 5 Backup log
>> 6 Users doing modifications
>> 7 Backup log
>> 8 Users doing modifications
>> And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if
>> you perform the restore. The error is SQL Server's way of reminding you that you probably want to
>> do a log backup first:
>> 9 Backup log
>> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do
>> RESTORE *without* doing this last log backup, you can use the REPLACE parameter for the RESTORE
>> command.
>> This behavior (the "reminder") is new for 2005, btw.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Yes. I have schedules to backup the transaction log every 6 hours. I don't know if this is a
>> good practice.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
>>A couple of questions first:
>> Are you doing regular transaction log backups?
>> If not, is there a reason for you to have the database in full recovery model?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Gaspar" <noreply@.nospamplease.com> wrote in message
>> news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm doing a full backup of a SQL Server database with SSMS. When I try to restore it I get the
>> following exception:
>> *******
>> Msg 3159, Level 16, State 1, Line 1
>> The tail of the log for the database "MYBACKUP" has not been backed up. Use BACKUP LOG WITH
>> NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE
>> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
>> Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> *******
>> What is this message?
>> Should I backup the log before backing up the database?
>> Is the database backup enought to recover data?
>> Thanks!
>> Gaspar
>>
>>
>>
>|||This may help, it helped me.
http://www.associatedcontent.com/article/267055/how_to_setup_sql_server_2005_backups.html
On Dec 21, 6:44 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> > letting me know that after I backuped up the data, some changes where done.
> Yes, the message states that after your lastbackup(database or logbackup) there has been
> modifications in the database.
> > Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> > 1) Can I restore by only having the databasebackup?
> Yes. Again, consider below:
> 1Backupdb
> 2 Users doing modifications
> 3Backuplog
> 4 Users doing modifications
> 5Backuplog
> 6 Users doing modifications
> 7Backuplog
> 8 Users doing modifications
> You can restore from only 1. Of course you will lose all modifications done after 1.
> > 2) How to avoid this message from appearing?
> Specify the REPLACE option for your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Gaspar" <nore...@.nospamplease.com> wrote in messagenews:ux4xwO9QIHA.1164@.TK2MSFTNGP02.phx.gbl...
> > So, this reminder "The tail of the log for the database "MYBACKUP" has not been backed up" is just
> > letting me know that after I backuped up the data, some changes where done.
> > Suppose I don't care about this changes (my compain can "recover" from a one-day data loss):
> > 1) Can I restore by only having the databasebackup?
> > 2) How to avoid this message from appearing?
> > Thanks again
> > "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in message
> >news:O%23K04v8QIHA.3516@.TK2MSFTNGP02.phx.gbl...
> >> Yes. I have schedules tobackupthe transaction log every 6 hours. I don't know if this is a
> >> good practice.
> >> This we can't answer since we don't know how much data your organization can tolerate to lose in
> >> case of a disaster...
> >> Anyhow, I do now know you do log backups. OK, consider below:
> >> 1Backupdb
> >> 2 Users doing modifications
> >> 3Backuplog
> >> 4 Users doing modifications
> >> 5Backuplog
> >> 6 Users doing modifications
> >> 7Backuplog
> >> 8 Users doing modifications
> >> And you now want to do RESTORE. But what about the modifications in step 8? This will be lost if
> >> you perform the restore. The error is SQL Server's way of reminding you that you probably want to
> >> do a logbackupfirst:
> >> 9Backuplog
> >> When you now RESTORE (1, 3, 5, 7 *and* 9), you will have no data loss. If you *want* to do
> >> RESTORE *without* doing this last logbackup, you can use the REPLACE parameter for the RESTORE
> >> command.
> >> This behavior (the "reminder") is new for 2005, btw.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Gaspar" <nore...@.nospamplease.com> wrote in message
> >>news:ORsgVp8QIHA.5400@.TK2MSFTNGP04.phx.gbl...
> >> Yes. I have schedules tobackupthe transaction log every 6 hours. I don't know if this is a
> >> good practice.
> >> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in message
> >>news:%23aEpff8QIHA.5988@.TK2MSFTNGP02.phx.gbl...
> >>A couple of questions first:
> >> Are you doing regular transaction log backups?
> >> If not, is there a reason for you to have the database in full recovery model?
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Gaspar" <nore...@.nospamplease.com> wrote in message
> >>news:eSEAac8QIHA.4196@.TK2MSFTNGP04.phx.gbl...
> >> I'm doing a fullbackupof a SQL Server database with SSMS. When I try to restore it I get the
> >> following exception:
> >> *******
> >> Msg 3159, Level 16, State 1, Line 1
> >> The tail of the log for the database "MYBACKUP" has not been backed up. UseBACKUPLOG WITH
> >> NORECOVERY tobackupthe log if it contains work you do not want to lose. Use the WITH REPLACE
> >> or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
> >> Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >> *******
> >> What is this message?
> >> Should Ibackupthe log before backing up the database?
> >> Is the databasebackupenought to recover data?
> >> Thanks!
> >> Gaspar

Bad page ID

<<Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:6195408) has invalid
PFS_PAGE page header values.
Type is 0. Check type, object ID and page ID on the page.>>
The above error seems to indicate that I can check the
header values of this page.
How do I do that?
Many thanks.The message should really be "Have your support provider check the ..."
You can search for DBCC PAGE on the Net.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Karl" <kekurzenknabe@.hotmail.com> wrote in message news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> <<Server: Msg 8946, Level 16, State 12, Line 2
> Table error: Allocation page (1:6195408) has invalid
> PFS_PAGE page header values.
> Type is 0. Check type, object ID and page ID on the page.>>
> The above error seems to indicate that I can check the
> header values of this page.
> How do I do that?
> Many thanks.|||Looks like you've got a corrupt PFS page. This problem cannot be
automatically repaired (even with REPAIR_ALLOW_DATA_LOSS) so you need to
restore your database from a backup after working out why the problem
occurred (check your event logs, the errorlog, run hardware diagnostics) -
or call PSS to help you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eH7TZYhdDHA.416@.tk2msftngp13.phx.gbl...
> The message should really be "Have your support provider check the ..."
> You can search for DBCC PAGE on the Net.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Karl" <kekurzenknabe@.hotmail.com> wrote in message
news:2f6f01c373d6$f67b09c0$a601280a@.phx.gbl...
> > <<Server: Msg 8946, Level 16, State 12, Line 2
> > Table error: Allocation page (1:6195408) has invalid
> > PFS_PAGE page header values.
> > Type is 0. Check type, object ID and page ID on the page.>>
> >
> > The above error seems to indicate that I can check the
> > header values of this page.
> >
> > How do I do that?
> >
> > Many thanks.
>

Backup trouble with system database model

Hello all,
Al of a sudden I cannot backup my model database.
When I try to backup the database I get the following message:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'model' is full. Back up the transaction log for t
he database to free up some log space.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Could not write a CHECKPOINT record in database ID 3 because the log is out
of space.
I have 2 questions:
(1) To my knowledge the model database has not been changed since the SQL se
rver installation (+/- 1 year ago), accept a weekly "Optimization". The mode
l database has recovery model "Simple". How did the log file get filled?
(2) How do I empty the log file? I have tried to empty the log file via:
--> DUMP TRANSACTION model WITH NO_LOG
--> BACKUP LOG model WITH NO_LOG
However with no success.
Any suggestion is welcome, thanks,
Annick van den BroekHi,
Have a look into this article;
http://support.microsoft.com/defaul...kb;en-us;317375
Thanks
Hari
MCDBA
"Annick van den Broek" <anonymous@.discussions.microsoft.com> wrote in
message news:5F6BC6C8-BFCD-4A7C-96A4-753A3F27AF12@.microsoft.com...
> Hello all,
> Al of a sudden I cannot backup my model database.
> When I try to backup the database I get the following message:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'model' is full. Back up the transaction log for
the database to free up some log space.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> Could not write a CHECKPOINT record in database ID 3 because the log is
out of space.
> I have 2 questions:
> (1) To my knowledge the model database has not been changed since the SQL
server installation (+/- 1 year ago), accept a weekly "Optimization". The
model database has recovery model "Simple". How did the log file get filled?
> (2) How do I empty the log file? I have tried to empty the log file via:
> --> DUMP TRANSACTION model WITH NO_LOG
> --> BACKUP LOG model WITH NO_LOG
> However with no success.
> Any suggestion is welcome, thanks,
> Annick van den Broek

backup to tape failed

I was trying to do a backup to tape but it failed with the following message:

Msg 3229, Level 16, State 2, Line 2

Request for device '\\.\Tape0' timed out.

Msg 3013, Level 16, State 1, Line 2

BACKUP DATABASE is terminating abnormally.

Any clues why this happened?

Any help is appreciated.

Take a look at the system event log and sql errorlogs.

You may have a hardware issue with the tape drive (or the connection to it...).

If the OS returned an error code to sql server, it will have been logged in the errorlog.

That 3229 is also raised if a tape mount was pending but a new tape was not loaded prior to the timeout. However, that instance should have "state = 1" and not 2 as you report.

|||

Thanks for the help. The problem was with the MEDIANAME. i was specifying a new Medianame.

If MEDIANAME is specified, it must match the previously specified media name already existing on the backup volume.

Thanks Anyways