Best way to Backup / Restore SQL 2005 Data Bases ?

It could be that the Tapeware agent is for SQL 2000 and not 2005. The error message was:

This is normal if the server has rejected the DUMP or LOAD command."

The DUMP and LOAD commands are from previous versions and not generally used although they are supported.

SSIS is a very good tool in SQL Server 2005 to use to back up databases. It works really well and you can save data onto tape drive also.

Please let me know if this helps

Aash.

Brugh:
Thanks! I will read the link and try to get it configred.

Crag:
Yes, that is what I was starting to suspect, but I am unable to get confirmation of that yet.

Matrix_aash:
What is SSIS?

Thanks,
-Chris

SQL Server Integration Services new tool in SQL Server 2005. I really nice tool to do lot of database tasks and you can also automate them as it was possible throught DTS in SQL Server 2000.

Aash.

the latest version of Tapeware is now called Yosemite Backup:

·        TapeWare 6.3 SP3
·        TapeWare 7.0 SP5C
·        TapeWare 7.0 SP7D
·        Yosemite Backup 8.0 (all service packs)
·        Yosemite Backup 8.1 (all service packs)

A new feature in YB 8.1 SP3 is support for SQL 2005

http://www.yosemitetech.com/support/packs/release_notes_25283.htm

If you want to avoid upgrading your best bet is to use a maintenance plan (or two) to create the flat backup files and then use Tapeware to back them up to tape.

Thanks for the document on how to configure Maintenance plans.

I setup a maintenance plan to do a full database backup to file every week night. I found the "bak" files in the sepecified folders this morning!

I also setup a maintenance plan to back up the transaction logs every week day, every hour beween 6:00am and 10:00pm, yet I did not find any "trn" files.

The documentation states that:
"Before you create a plan for a transaction log backup, you need to verify that the target database recovery model is either Full or Bulk logged".
But it does not state how to do that. Can you please advise me on how this is done?
What is the difference between the "recovery modes"?

Also, am I correct in assuming that the transaction logs only need to be backed up during working hours?

Thanks in advance,

-Chris

Right click on a database in SSMS and select properties
Select options and the second item down is the recovery model.

you should back up the TR logs whenever you have transactions going into the system that you would like to recover. generally this will be more during your peak usage times but it depends ont he business requirements - you might have a batch processing out of hours that you would like to recover and not have to reprocess.

Thanks for the prompt response Crag.

Can you explain the difference between the recovery models?
I just want to understand what would work best for our situation?

Thanks!

What works best will depend on the business requirements.
There are three types of backups:

Full - complete database and stands on its own.
Differential - only changes since last full backup - restore comprises full followed by latest diff.
Log - only the transactions that have been processed against the database. Restores requires full and if used differential. Comprises full, latest diff and all TR Log.

From these there are three recovery models:

Simple - Only Full & differential database backups can be used. simplest but you could lose data

Bull-logged - half-way house. between simple and full. Large data loads not logged and either full or diff backup required to save the data

Full - all three backup types can be used. if TR log backups not used then log will fill up and database will stop processing activity. Recommended for minimal data loss.

The business requirements will dictate whether you need to take log backups every hour or every fifteen minutes (or other interval).

OK, I think for now I am just going to go with a comptlete full backup for everything.

It seems the simplest way of doing it all. Considering I am using a 320GB DLT to backup just the SBS server, I don't foresee too many issues with that.

The only thing is when I try to set the "tempdb" to anything but simple, I receive the following error:
"Alter failed for Database 'tempdb'. (Microsoft.SqlServer.Smo)
Additional information:
    An exception occured while executing a Transact-SQL statement or batch.
    (Microsoft.SQLServer.ConnectionInfo)
          Option 'RECOVERY' cannot be set in database 'tempdb'. (Mircosoft SQL Server, Error: 5058)"

OK, my Transaction Log Backups are failing. I am not sure why.
Here is the Log file output:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054
Report was generated on "KHSERVER31".
Maintenance Plan: SQL Transaction Logs to Disk
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database Task (KHSERVER31)
Backup Database on Local server connection
Databases: TEST1,TWO
Type: Transaction Log
Append existing
Task start: 2007-10-17T10:00:02.
Task end: 2007-10-17T10:00:02.
Failed:(-1073548784) Executing the query "BACKUP LOG [TEST1] TO  DISK = N'D:\\SQL Backup Files\\Transaction Log\\TEST1\\TEST1_backup_200710171000.trn' WITH NOFORMAT, NOINIT,  NAME = N'TEST1_backup_20071017100002', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


Back Up Database Task (KHSERVER31)
Backup Database on Local server connection
Databases: TEST1,TWO
Type: Transaction Log
Append existing
Task start: 2007-10-17T10:00:02.
Task end: 2007-10-17T10:00:02.
Failed:(-1073548784) Executing the query "BACKUP LOG [TWO] TO  DISK = N'D:\\SQL Backup Files\\Transaction Log\\TWO\\TWO_backup_200710171000.trn' WITH NOFORMAT, NOINIT,  NAME = N'TWO_backup_20071017100002', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks Crag,

Yeah I noticed this morning that the tran-log backups did start working after the scheduled full backup ran last night.

Ok I will just leave the tempdb alone.
Thanks for your help!

Has this question been resolved?