Re: Restoring SQL Server Backups

Ekrem,

Thanks for your timely response!

Actually there are two different backup and restore senarios:

1. ORIGINAL BACKUP & RESTORE (MSDE Database)
This is the old MSDE backup and restore code before the database was
converted to SQL Server 2005 Express Edition. It worked without any problems
before the conversion.

2. MODIFIED BACKUP & RESTORE (SQL Server 2005 Express Edition)
This is the backup and restore code we tried to use once the database was
converted. This is were we are having the problem.

We can backup the database and log file without any problem. We are having
difficulty mainly with restoring database then restoring the log file.

Thanks in advance for your help!

- Glenn

"Ekrem Önsoy" wrote:

Ahh, you made me confused... Why would you run a "backup log ... with
truncate_only" after running a backup log command? I couldn't see the logic
here...

Why are you changing the Recovery Model of the database twice?

Also, I think you have a missing SPACE before 'WITH REPLACE'... It should
have been ' WITH REPLACE' I guess? Otherwise it would be joined with
datDevice variable...

ADOCommand1.CommandText := 'Restore Database ' + dbName + ' ' + #13 +
'From ' + datDevice + 'WITH REPLACE';

--
Ekrem Önsoy - SQL Server MVP



"gwhite" <gwhite@xxxxxxxxxxxxxxxxxxxxxxxxx>, iletisinde şunu yazdı,
news:3FBFFE8F-5A68-485B-8A84-7835A64C135F@xxxxxxxxxxxxxxxx
Uri,

Thanks for your timely response. Below is the information you requested:

ORIGINAL BACKUP & RESTORE (MSDE Database)
ADOCommand1.CommandText := 'Alter Database ' + dbName + ' SET RECOVERY
FULL';
ADOCommand1.Execute;
pbBackup.Position := 10;
ADOCommand1.CommandText := 'Backup Database ' + dbName + ' TO ' + dbName +
'_DAT With INIT';
ADOCommand1.Execute;
pbBackup.Position := 25;
ADOCommand1.CommandText := 'Backup LOG ' + dbName + ' TO ' + dbName +
'_LOG';
ADOCommand1.Execute;
pbBackup.Position := 50;
ADOCommand1.CommandText := 'BACKUP LOG ' + dbName + ' WITH
TRUNCATE_ONLY';
ADOCommand1.Execute;
pbBackup.Position := 75;

'Restore Database ' + dbName + ' ' + #13 +
'From ' + datDevice;


MODIFIED BACKUP & RESTORE (SQL Server 2005 Express Edition)
ADOCommand1.CommandText := 'Alter Database ' + dbName + ' SET RECOVERY
FULL';
ADOCommand1.Execute;
pbBackup.Position := 10;
ADOCommand1.CommandText := 'Backup Database ' + dbName + ' TO ' + dbName +
'_DAT With INIT';
ADOCommand1.Execute;
pbBackup.Position := 25;
ADOCommand1.CommandText := 'Backup LOG ' + dbName + ' TO ' + dbName +
'_LOG';
ADOCommand1.Execute;
pbBackup.Position := 50;
ADOCommand1.CommandText := 'BACKUP LOG ' + dbName + ' WITH NO_LOG';
ADOCommand1.Execute;
pbBackup.Position := 75;

ADOCommand1.CommandText := 'Restore Database ' + dbName + ' ' + #13 +
'From ' + datDevice + 'WITH REPLACE';

if cbforce.Checked = true then
begin
ADOCommand1.CommandText := 'Restore Database ' + dbName + ' ' +
#13 +
'From ' + datDevice + ' with REPLACE,
NORECOVERY';
end;

These ADO commands are being run inside of a Delphi application.

The error I receive when I try the second restore is as follows:

Error Restoring from file:
The tail of the log for the database 'dbname' 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

Below is the contents of the ERRORLOG file:

2009-07-23 08:34:44.94 spid51 Setting database option RECOVERY to
FULL
for database AIM_LGN.
2009-07-23 08:35:49.21 Backup Database backed up. Database: AIM_LGN,
creation date(time): 2009/06/29(13:00:34), pages dumped: 15235, first LSN:
8090:127:136, last LSN: 8090:183:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'AIM_LGN_DAT'}). This is an
informational
message only. No user action is required.
2009-07-23 08:35:49.46 Backup Log was backed up. Database: AIM_LGN,
creation date(time): 2009/06/29(13:00:34), first LSN: 8089:449:1, last
LSN:
8090:188:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'AIM_LGN_LOG'}). This is an informational message only. No
user
action is required.
2009-07-23 08:35:49.49 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH
NO_LOG is deprecated. The simple recovery model should be used to
automatically truncate the transaction log.
2009-07-23 08:39:09.30 spid51 Error: 3633, Severity: 16, State: 1.
2009-07-23 08:39:09.30 spid51 The operating system returned the error
'3(The system cannot find the path specified.)' while attempting
'DeleteFile'
on
'C:\PROGRA~1\AIM\SQLSERVER\Data\MSSQL$AIMSQL2000MSDE\Backup\AIM_LGN.CKP'
at 'storage.cpp'(875).
2009-07-23 08:39:09.31 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part
of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-23 08:39:09.32 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-23 08:39:09.32 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part
of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-23 08:39:09.40 Backup Error: 18272, Severity: 16, State: 1.
2009-07-23 08:39:09.40 Backup During restore restart, an I/O error
occurred on checkpoint file
'C:\PROGRA~1\AIM\SQLSERVER\Data\MSSQL$AIMSQL2000MSDE\Backup\AIM_LGN.CKP'
(operating system error 3(The system cannot find the path specified.)).
The
statement is proceeding but cannot be restarted. Ensure that a valid
storage
location exists for the checkpoint file.
2009-07-23 08:39:22.11 spid51 Starting up database 'AIM_LGN'.
2009-07-23 08:39:22.16 spid51 The database 'AIM_LGN' is marked
RESTORING and is in a state that does not allow recovery to be run.
2009-07-23 08:39:22.25 spid51 Starting up database 'AIM_LGN'.
2009-07-23 08:39:22.88 Backup Database was restored: Database:
AIM_LGN,
creation date(time): 2009/06/29(13:00:34), first LSN: 8089:407:102, last
LSN:
8089:449:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'C:\PROGRA~1\AIM\Restore\AIM_LGNdat.bak'}). Informational
message. No user action required.
2009-07-24 08:47:49.92 spid51 Setting database option RECOVERY to
FULL
for database AIM_LGN.
2009-07-24 08:48:29.68 Backup Database backed up. Database: AIM_LGN,
creation date(time): 2009/06/29(13:00:34), pages dumped: 15235, first LSN:
8090:16:85, last LSN: 8090:51:1, number of dump devices: 1, device
information: (FILE=1, TYPE=DISK: {'AIM_LGN_DAT'}). This is an
informational
message only. No user action is required.
2009-07-24 08:48:29.89 Backup Log was backed up. Database: AIM_LGN,
creation date(time): 2009/06/29(13:00:34), first LSN: 8089:449:1, last
LSN:
8090:56:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'AIM_LGN_LOG'}). This is an informational message only. No user action is
required.
2009-07-24 08:48:29.93 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH
NO_LOG is deprecated. The simple recovery model should be used to
automatically truncate the transaction log.
2009-07-24 08:52:43.95 spid51 Error: 3633, Severity: 16, State: 1.
2009-07-24 08:52:43.95 spid51 The operating system returned the error
'3(The system cannot find the path specified.)' while attempting
'DeleteFile'
on
'C:\PROGRA~1\AIM\SQLSERVER\Data\MSSQL$AIMSQL2000MSDE\Backup\AIM_LGN.CKP'
at 'storage.cpp'(875).
2009-07-24 08:52:43.97 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part
of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-24 08:52:43.97 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-24 08:52:43.97 spid51 SQL Server has encountered 4
occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part
of
plan cache) due to some database maintenance or reconfigure operations.
2009-07-24 08:52:44.04 Backup Error: 18272, Severity: 16, State: 1.
2009-07-24 08:52:44.04 Backup During restore restart, an I/O error
occurred on checkpoint file
'C:\PROGRA~1\AIM\SQLSERVER\Data\MSSQL$AIMSQL2000MSDE\Backup\AIM_LGN.CKP'
(operating system error 3(The system cannot find the path specified.)).
The
statement is proceeding but cannot be restarted. Ensure that a valid
storage
location exists for the checkpoint file.
2009-07-24 08:52:55.84 spid51 Starting up database 'AIM_LGN'.
2009-07-24 08:52:55.98 spid51 The database 'AIM_LGN' is marked
RESTORING and is in a state that does not allow recovery to be run.
2009-07-24 08:52:56.09 spid51 Starting up database 'AIM_LGN'.
2009-07-24 08:52:56.82 Backup Database was restored: Database:
AIM_LGN,
creation date(time): 2009/06/29(13:00:34), first LSN: 8090:127:136, last
LSN:
8090:183:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK: {'C:\PROGRA~1\AIM\Restore\AIM_LGNdat.bak'}). Informational
message. No user action required.


Thanks in advance for your help!
Glenn

"Uri Dimant" wrote:

Hi
Can you post the whole error message from ERROR.LOG? Do you perform LOG
backups?

Can you post as well the script you run to restore the database?

"gwhite" <gwhite@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F77075E0-AF3E-4B54-9EAC-7EE0F0FAD419@xxxxxxxxxxxxxxxx
Hello,

I have an application that runs on a MSDE database. The MSDE database
has
been converted to SQL Server 2005 Express Edition. I can create a
backup
of
the 2005 database and restore the created backup.

However, I receive the tail-log error when I perform the following
sequence:

1. Backup the database on yesterday.
2. Backup the database today.
3. Restore yesterday's backup.
4. Restore today's backup. (This is when I get the error)

I have tried every option in both the backup and restore script and
nothing
seems to work.

Any advice or direction would be greatly appreciated!




.

Relevant Pages

  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • Re: Restoring SQL Server Backups
    ... Why would you run a "backup log ... ... Why are you changing the Recovery Model of the database twice? ... ORIGINAL BACKUP & RESTORE ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part of ...
    (microsoft.public.sqlserver.server)
  • Re: Restoring SQL Server Backups
    ... MODIFIED BACKUP & RESTORE ... The tail of the log for the database 'dbname' has not been backed up. ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part of ...
    (microsoft.public.sqlserver.server)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Restoring SQL Server Backups
    ... ORIGINAL BACKUP & RESTORE (MSDE Database) ... occurrenceof cachestore flush for the 'Object Plans' cachestore (part ...
    (microsoft.public.sqlserver.server)
Loading