Always specify the version of SQL Server are you are using.
The TRUNCATE_ONLY option should not normally be used after your backup.
Once the log is truncated without a log backup, subsequent log backups
are not allowed. TRUNCATE_ONLY is used to expeditiously recovery from a
full log situation. I suggest something like:
--nightly db backup
BACKUP DATABASE ContactManager2
TO DISK = 'e:\backup\ContactManager2.bak'
WITH INIT
--this will backup, truncate the log and init backup file
BACKUP LOG ContactManager2
TO DISK = 'e:\backup\ContactManager2Log.bak'
WITH INIT
GO
--periodic log backup
this will backup and truncate the log
BACKUP LOG ContactManager2
TO DISK = 'e:\backup\ContactManager2Log.bak'
GO
Your recovery scenario (backup log with NO_TRUNCATE) will work with SQL
2000 but not SQL 7. In SQL 7, you can only recover to the point of your
last successful log backup when the primary data file is lost. See
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q218739 for
details.
Be sure to periodically test your recovery plan under a variety of
scenarios.
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
-----------------------
Quote:> By my reading of the documentation, you should be able to recover a
database
> to any point using a backup file and the log. The scenario would be
that we
> backup the database nightly. During the day there is a hard drive
crash and
> the database is destroyed. The log is on a different drive and it
remains
> intact. We should be able to recover to the point of the crash.
> My backup procedure is as follows:
> Backup database ContactManager2 to disk =
'e:\backup\ContactManager2.bak';
Quote:> Backup log ContactManager2 with truncate_only;
> The recovery procedure I used is
> RESTORE DATABASE ContactManager
> FROM disk = 'e:\backup\ContactManager.bak'
> WITH NORECOVERY;
> RESTORE LOG ContactManager
> From ContactManager_log
> WITH RECOVERY;
> The restore database part works ok but I get an error message on the
restore
> log:
> No entry in sysdevices for backup device 'ContactManager_log'. Update
> sysdevices and rerun statement.
> What am I doing wrong?
by