| Author |
Topic |
|
irietek
Starting Member
4 Posts |
 Posted - 2007-06-14 : 13:15:53
|
| Hello,I have a very odd problem.I have 2 windows 2003 servers with SQL 2000 installed on both.We will call them servers A and B.Server A is our main SQL server that hosts our live MRP/ERP system.Server B is our test SQL server that hosts a copy of our live MRP/ERP system for our users to testing / training etc.Periodically I would take a backup from Server A and restore it on Server B. But, lately I have been getting an inconsistency error and I can not restore Server A backups onto Server B anymore, nor can I restore it on Server A.I don't think the DB is corrupted because I took a backup from Server B, restored it fine on Server A, did a backup of that and tried to restore it back on Server B and got the same inconsistency error. So right now, I am backing up the MDF files and if I need to update Server B with new data I copy the MDF files over but this is an improper way of backup.I have the latest Service PacksAny suggestions? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 14:16:42
|
| RunDBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGSon both servers' databases to check for corruptionsKristen |
 |
|
|
irietek
Starting Member
4 Posts |
Posted - 2007-06-14 : 14:31:18
|
quote:
Originally posted by Kristen
RunDBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGSon both servers' databases to check for corruptionsKristen
Hi Kristen,Thank you for the reply. I forgot to mention I ran DBCC before and found no errors. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 14:39:14
|
| Use 'restore verifyonly' to double check if backup is in good shape. How do you backup db by the way? Backup to local disk? How big is backup file? |
|
|
|
irietek
Starting Member
4 Posts |
Posted - 2007-06-14 : 14:42:45
|
quote:
Originally posted by rmiao
Use 'restore verifyonly' to double check if backup is in good shape. How do you backup db by the way? Backup to local disk? How big is backup file?
When I create a backup on Server A put on the Verify box and it came with no errors.The way I'm doing a backup is either through a maintenance schedule or I go into Server Enterprise and manually do it.The size of our DB is pretty small, 2GB. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 15:08:44
|
| Did you get any error when restore the db? |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2007-06-14 : 18:24:28
|
| How are you restoring the database? Are you moving the backup file around the network (ftp?) or are you restoring 'over the wire' using a UNC path?Sounds like whatever method you use to do the restore, there is an I/O issue somewhere or a corruption of the data during a copy/move/ftp operation (ftp default being native 7-bit transfer will muck up a backup very quickly). |
|
|
|
irietek
Starting Member
4 Posts |
Posted - 2007-06-15 : 09:02:17
|
quote:
Originally posted by Haywood
How are you restoring the database? Are you moving the backup file around the network (ftp?) or are you restoring 'over the wire' using a UNC path?Sounds like whatever method you use to do the restore, there is an I/O issue somewhere or a corruption of the data during a copy/move/ftp operation (ftp default being native 7-bit transfer will muck up a backup very quickly).
Hello.Even if I try to restore the DB on the same server the backup was created it fails.Very strange problem.Server A:Create backup - try to restore on Server A does not work, try to restore on Server B does not work.Server B:Create backup - can restore on Server B. Copy to Server A and it can restore on Server A. If I do a backup on Server A from the restored DB from server B it mucks up. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-15 : 09:35:01
|
| Try a backup command rather than a maintenance plan or gui.The verify on the backup command doesn't mean that it can be restored.What is the error you get when you try a restore.Try a header only restore to see if it's a valid backup.What happens if you backup a very small database?Where do you backup to? A local disk?Have you checked the disk for errors?Have you looked at the windows event log for io errors?Did you try a backup followed by a restore without copying the file?Oh - and what is the error you are getting?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
server creating bad backups