March 14, 2005 at 12:17 pm
Greetings!
This is the first of what are sure to be many posts. I'm fairly newly embarking on a career as an actual DBA (as opposed to a SQL developer who needs to administer the databases). So I'm sure I'll be by here a lot.
Anyway, I'm testing the restores of my backups and I keep receiving an error: "Incorrect syntax near 'table'." (the full text is below). I am totally stumped as to why it's happening. I've looked and looked and can't find anything.
So here's what I'm doing. I have nightly full DB backups on MiddleTier2 (full recovery model). I thend do a transaction log backup every 5 minutes. These have been set up through a maintenance plan. I have received no problems from the backups. The db is the subscriber and publisher of all 3 types of replications (it's a middle tier, after all). I'm testing the restore by copying the .bak and .trn files to a development server and doing the restore from there. All of the restores work until the last one, which is done WITH RECOVERY. Regardless if I do it through EM or through QA, I still get the same message: "Incorrect syntax near 'table'." Oh, and I'm running SQL Server 2000 Standard SP3.
I tried checking my databases by scripting every user object and in each of the user databases (and every user object in the system databases) and by scripting all of my publications. I would then run the check syntax in QA, and everything return with no errors. I'm not sure what else I can check.
Does anyone know what is wrong here? I suspect one of my objects in my database is bad, but I don't know how to identify it. Thanks a ton!!
Here's the database restore command I used:
RESTORE DATABASE MT2 FROM DISK = 'e:\restoretest\mt2\middletier2_db_200503132230.bak'
WITH NORECOVERY, MOVE 'MiddleTier2_data' TO 'e:\restoretest\mt2.mdf', MOVE 'MiddleTier2_log' TO 'e:\restoretest\mt2.ldf'
Here's the final log restore command I used (after restoring 10 other log files with the NORECOVERY option):
RESTORE LOG MT2 FROM DISK = 'e:\restoretest\mt2\middletier2_tlog_200503140845.trn'
WITH RECOVERY, MOVE 'MiddleTier2_data' TO 'e:\restoretest\mt2.mdf', MORE 'middletier2_log' TO 'e:\restoretest\mt2.ldf', stopat = '3/14/2005 08:43:30'
And here are my results:
Processed 818 pages for database 'MT2', file 'MiddleTier2_Log' on file 1.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'table'.
Server: Msg 3165, Level 16, State 3, Line 10
Could not adjust the replication state of database 'MT2'. The database was successfully restored, however its replication state is indeterminate. See the Troubleshooting Replication section in SQL Server Books Online.
RESTORE LOG successfully processed 818 pages in 0.413 seconds (16.225 MB/sec).
I wouldn't mind hearing about why I'm getting the replication state error as well, but I suspect the answer lies with the incorrect syntax. I'm more worried about that one anyway. Please let me know what you think and/or if I can provide any other information. Thank you very much!
March 14, 2005 at 12:41 pm
this is a complete guess ... but may it be that MORE 'middletier2_log' should be MOVE 'middletier2_log'
(I never did this so I might be completly off here)
March 14, 2005 at 1:11 pm
Oops! Yeah, it should be MOVE. But that was just a typo in this forum. The actual code had MOVE. I just typed it wrong. :> Sorry about that.
March 14, 2005 at 1:22 pm
NP... hopefully someone else will be able to find you problem.
Good luck.
March 14, 2005 at 9:22 pm
try to put go between each statement to avoid confusion.
From your statement, there was no 'table'.
March 15, 2005 at 1:27 am
I am assuming that the database that was backed up (MT2) was being replicated (what type of replication?) and that the SQL Server is what running on was the publisher and distributor. Is the server that it is being restored to set up in a similair fashion?
March 15, 2005 at 7:36 am
OK, I'll try and remember to put the GO in. Sorry about that.
And yes, exactly: there was no 'table.' That's what has me so puzzled. And that's what leads me to suspect there's a bad script in the backup. I don't know the guts of restores, but I suspect it runs scripts to create the objects (correct?). If there is a bad object or a bad script, then you'd see such a message, I would think. I suppose one thing I could do would be to script all database objects and then run them on a different server. That would confirm the validity of my objects.
March 15, 2005 at 7:38 am
Yes, it was being replicated. It was the publisher of merge, transactional, and snapshot replication. It also was a subscriber to merge and transactional. Yes, it is both the publisher and distributor. The server being restored to does not have any replication setup on it, and there are no databases configured for publication. I suppose I can give that a shot. I have other development boxes which are configured correctly, but unfortunately there's no disk space (it's a large db). So, I'll try enabling replication and seeing if that helps.
March 16, 2005 at 9:39 am
OK, I figured out the problem. First, I enabled replication on the server being restored to. Second, I used the WITH KEEP_REPLICATION switch. That cleared up the error and I was able to restore without any problems. Thanks for your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply