• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 861
  • Last Modified:

Best way to Backup / Restore SQL 2005 Data Bases ?

Hello,

I am trying to get our Disaster Recovery plan in place here, but I am running into problems trying to backup our SQL 2005 databases.

We are running SBS 2003 R2 Premium, I have installed a 320GB DLT tape drive.
I have installed Yosemite Tapeware v 7.00 SP5C.
I have also installed the tapeware agents for Exchange and SQL.

Running a full backup (with Tapeware) backs-up everything without problems except for the SQL 2005 databases.

The Tapeware log shows the error "932 - Unable to create SQL pipe" for each SQL database.

I followed the Tapeware documentaion to add the ODBC connection for the SQL instance and I believe I have it configured correctly, but I can't find any real documentaion explaining the error except for the following:
"Error Number 932  Unable to create SQL pipe
Description: When attempting to backup or restore, TapeWare was unable to open the transfer pipe. This is normal if the server has rejected the DUMP or LOAD command."

After reading some of these posts, it looks like I could also just scrap the Tapeware SQL agent and have SQL "dump" the data to a file and just have Tapeware backup the file. But I am not sure how to do that.

Either way, I need to get these databases backed up so I can sleep at night!

I am quite inexperienced with SQL.

Any of your expertise would be greatly appreciated.
0
PortableLighting
Asked:
PortableLighting
  • 6
  • 6
  • 2
  • +1
2 Solutions
 
BrughCommented:
Create a Backup plan using SQL Studio and then use tapeware to backup the Database Backup files that are created by SQL.

Depending on the use and size of your DB, i wuold recomend hourly Transaction log backups with a nightly Full DB backup.  THen use tapeware to backup all of the backup files in teh directory.  

This will eliminate the need for Tapeware to connect via ODBC.

Some information on SQL Backup JObs
http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

 - Brugh
0
 
CragCommented:
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.
0
 
matrix_aashCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortableLightingAuthor Commented:
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
0
 
matrix_aashCommented:
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.

0
 
CragCommented:
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.
0
 
PortableLightingAuthor Commented:
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
0
 
CragCommented:
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.
0
 
PortableLightingAuthor Commented:
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!
0
 
CragCommented:
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).

0
 
PortableLightingAuthor Commented:
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)"

0
 
PortableLightingAuthor Commented:
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.


0
 
CragCommented:
To answer these posts in order:

1. The TempDB should be left at simple - as the data it contains is only temporary there should be no need to backup the tempDB. In feact I'm not even sure you could restore it if you did.

2. The log backups have failed because you did not take a full database backup after setting the recovery model. As explained before the Log backups are useless without the initial full backups and SQL is enforcing this. Full backups taken before setting the recovery model do not count as transactional data could have been lost prior to setting the recovery model.



0
 
PortableLightingAuthor Commented:
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!
0
 
CragCommented:
Has this question been resolved?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now