Veritas and backing up SQL database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Veritas and backing up SQL database

Author

Topic

rango316
Starting Member

20 Posts

Posted - 2007-11-08 : 12:15:10

Hello,

I really hope someone can help...

I am having great difficulty backing up my SQL database through Veritas/symantec backup exec here is the error being reported in log:

Backup- servername V-79-57344-33938 -
An error occurred on a query to database "databasename".

my sql database is setup for both windows and sql authentication...

What settings does a database need for Backup exec to be able to query it?

any help much appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-08 : 12:56:09

You should not use Veritas to backup your databases. Backups should be performed in SQL Server and then Veritas can sweep the backup files to tape.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 13:41:06

"What settings does a database need for Backup exec to be able to query it?"

Please PLEASE PLEASE do NOT do it that way.

(Do it as Tara says)

Kristen

rango316
Starting Member

20 Posts

Posted - 2007-11-09 : 04:12:46

oh dear, im a little worried now!

is there a tutorial or something as im not an SQL DBA or anything of the sort im just trying to back up the database! please can you help?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-09 : 04:19:14

Database files (MDF and LDF) are opened in the exlusive mode by SQL server.
No other application, including backup apps, can access these files unless SQL server is stopped or database is detached.
it is known as 'cold backup' but it is not practical as it reuqires a downtime for each backup.

Usually SQL server creates files (.BAK, .TRN) which should be backed up to tape.
They are in the different directory normally
There are also different backup strategies in SQL server (Simple mode? Full mode? schedule for TR logs?) -there are many questions.
Schedules for TAPE and SQL SERVER backups must be in sync.
You should discuss it with your SQL server DBA.

If you are just alone, having a request to 'backup SQL servers' without having a DBA, let us know, we'll give you a simple plan in that case.

rango316
Starting Member

20 Posts

Posted - 2007-11-09 : 04:33:51

ok im getting further...

I have backued up my CRM SQL databse but only by using Files and filegroups and selecting the crm databse, If i select the option to backup the whole database i get the following error:

System.Data.SqlClient.SqlClient.SqlError: The backup of the file or filegroup "sysft+ftcat_documentindex" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

Is this ok and i just select the database file?

Thanks, Ian

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-09 : 04:48:33

begin from a FULL backup. Dont back up files or filegroups individually.

rango316
Starting Member

20 Posts

Posted - 2007-11-09 : 04:52:49

I cant because of the error above :(

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:16:53

"If i select the option to backup the whole database"

It would probably help if you list the steps you are using to do the above. Don't need an essay! just "Select database [CRM SQL] in SSMS. Right Click. Select ..."

Kristen

rango316
Starting Member

20 Posts

Posted - 2007-11-09 : 05:23:03

ok i start the management studio
right click on my database
select tasks
Backup...
select the database
backup type = full
backup component = database
back up to disk
and run it

after googleing for about an hour it seems quite a few people have experienced this loss of their full text catalog and i need to recreate it somehow!

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:27:49

Are you on Service Pack 2 for SQL 2005?

Quite a lot of bugs, some relating to backup etc., fixed in SP2

Might be worth trying to make a "manual" backup using SQL and seeing if that gives same/different/no error


BACKUP DATABASE MyDatabaseName 
	TO DISK = 'x:\MyPath\MyDatabaseName_yymmdd_hhmm.BAK'
WITH NOINIT,
	STATS=10

Kristen

Kristen
Test

22859 Posts

Posted - 2007-11-09 : 05:31:57

...
backup type = full
backup component = database

got that

back up to disk

at this point there will be a path/file listed (the name of the previous backup)

You are probably best off REMOVE-ing this, and ADD-ing a new path/filename

Otherwise there is the risk that it will try to overwrite/append to that file, and that file may not allow that.

This GUI-route is full of pitfalls like this I'm afraid. Setting up a backup schedule would be better:

Server : Management : Maintenance plans : Right-Click : New ...

Kristen

rango316
Starting Member

20 Posts

Posted - 2007-11-09 : 05:39:43

I GOT IT!

I had to go into the database --> storage --> Full Text Catalogs and ftcat_documetindex and rebuild... full backup now works

Thanks for all the helps guys!!!

happy SQL'ing!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources