backup failing affter move

[SQLBill]

I moved a database from one virtual server to another. I detached it from VS1, moved the .mdf and .ldf to their new drives and attached them to VS2.

I used Enterprise Manager to do the detach and attach. I can access the database, I can query the database, my users can get what they need from the database. I just can't back it up. I can successfully backup all my other databases on VS2, so I apparently missed something when I moved the database over.

What did I miss doing? Any suggestions?

-SQLBill

 

[SQLBill]

BTW- I should add that I had orphaned users. I cleared them out and added the proper logins/passwords back in. Then I double-checked accesses.

-SQLBill

 

[Corran007]

does it give any sort of an error? or does it just not let you. does em not let you back up, or will it not let you back up running qa as well? do backup jobs work?

 

[SQLBill]

I have a backup job with 6 steps, each one does a full backup of different databases. All work fine but the last one.

No error message except in Job History and that only says that it failed.

I'll try QA, didn't think of that.

-SQLBill

 

[SQLBill]

Here's more information:

I have a job set up that backs up this database. It's set to run as DBO. It fails. I run it under QA as me and it succeeds. I change the job to set it to run as me and it works fine.

How do I get DBO to work on this database again?

-SQLBill

 

[SQLBill]

I have another thread going that appears to be different issues, but may be related.

Thread962-587507

-SQLBill

 

[Corran007]

Who is the database owner? If its set to you, try changing it.

 

[MaffewW]

it does sound like a SID thing from moving the databases

What logon is the DBO mapped to on your working dbs, then your not working one? (use <db> sp_helpuser). On the db which isnt backing up try changing the DBO to map to SA, then changing it back to the proper user

use db
sp_changedbowner 'sa'
sp_changedbowner 'realdbo'







Matt

Brighton, UK

 

[osjohnm]

Hi Bill

Run the following sql to determine if the DBO user in the database is &quot;unmatched&quot;

SELECT u.name AS &quot;Name&quot;, ISNULL(l.name, 'dbo is unmatched') AS &quot;Matched Login&quot;
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'

If it the case then run sp_changedbowner as MaffewW suggested.

John

 

[SQLBill]

Well, turns out the database owner was NULL. Changed it to the proper owner and everything works fine.

Thanks for all the support and advice.

-SQLBill

(how often do I overlook the obvious? Obviously too often!)

 

[MaffewW]

nice. did it fix the cluster problem as well?

Matt

Brighton, UK

 

[SQLBill]

1. Got the issue with backing up the one database solved.

2. Found we have a hardware problem that causes the cluster to failover all the time. It's not failing over to the other node, part of the 'fabric' fails over to the other part. We are trying to get a replacement part.

3. Had a problem with users not being able to access the database when it was on the first node. Turned out to be a permissions issue. Somewhere along the line, the users were removed from a Windows group on node1. Because of all the issues we had with our system, we were using node2 all the time so we never noticed the permissions issue.

Except for the hardware issue(s), everything seems to be fine.

-SQLBill