SQL backups

[joepc]

I have a 2003 server with sql 2000 and sql 2005. Not sure why this is but I inherited via a new cient. In a nut shell the problem I am having is that when we try to run a SQL backup via 2000 enterprise manager, the backup freezes. I have to restart the SQL services and run the backup manually serveral times before it will work. This is a problem because I can't automate it. Anyone have any ideas what might be causing this issue?

Trying to backup two databases. One is about 100 MB the other is just under 4GB. It is running a newer HP server with lots of RAM, CPU, and disk space.

 

[mrdenny]

How long do you wait for?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLWilts]

Have you tried using code, and see if that makes any difference?
In query analyser:

BACKUP DATABASE MyDB
TO DISK = N'pathtobackup.bak'
WITH STATS = 5

The stats should give you a counter to tell you if it is actually doing anything - it may just be EM hanging.

If you decide this works and put it in a job - remove the "WITH STATS" bit.

 

[LawnBoy]

I don't think EM will back up a 2005 instance...

"We must fall back upon the old axiom that when all other contingencies fail, whatever remains, however improbable, must be the truth." - Sherlock Holmes

 

[SQLWilts]

I have kind of assumed that the 2000 tools are being used to back up databases on the 2000 instance - but we all know what assumptions make...

 

[joepc]

I can wait all day and nothing happens. I will try the code and see if that will give me anymore info.

Thanks

 

[joepc]

Could it by any chance be a problem with the database? Maybe it needs a reindexing or something???

 

[SQLWilts]

you could run dbcc checkdb and see if it finds any errors, but I suspect something else going on here.
Let me know how it turns out with the code. Also, while it is running have a look in the folder that you are backing up to and see if there is a file growing in there.
Is there anything in the windows application log, or the SQL server log that may give us a clue?

 

[mrdenny]

Reindexing shouldn't effect your database backups.

See what happens when you run the code. With as small as the databases are it shouldn't take that long to back them up.

The GUI may simply be screwed up.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[joepc]

Thanks a lot guys!!! I'll let you know how I make out.