February 1, 2006 at 2:57 pm
I have searched what I could think of within the forums and have not found an answer to my problem so here is a new one.
I have a production database on Server A replicating (transactional) to a database on Server B. I need to apply some schema changes to this database but I need to first make a copy of this production database so I can test the schema changes.
My test database already exists so I have tried the restore from backup (of production database) as my test database but because the database is fairly large, the restore is taking forever and right now it seems to be hung. In addition, users are complaining about the applicaiton (connecting to the production database) is acting really slow.
First, can I kill the restore job to keep my users happy and if so, how?
Second, Is there an easier way to create my test database from production? Could I just make a copy of the MDF and LDF files, rename them as the name of a new database and attach them? Am I a moron? (don't answer that last question).
If it does finally finish, in order for the restored database to be useable and able to insert, delete, add records, I am forced to drop the replication column on all the tables.
What other options do I have?
February 2, 2006 at 10:45 am
Ok, now I really have things messed up. I have stopped the restore job - took about 3 hours to stop. Now I have a development database that thinks it is a publisher but it really isn't. syspublicaitons and syssubscriptions objects do not exist yet if I try to drop the database, I get an error message that it cannot be dropped because it is used for publication.
I have tried sp_dboption 'database', 'published', false but get the 208 error message "invalid object name 'syssubcriptions'"
How can I tell SQL that this database is not a publisher so that I can drop the database and find a different way to create it?:
February 3, 2006 at 6:22 am
Hey,
I happenened to see your post this morning. I feel your pain. Transactional Replication can be troublesome.
A couple of things... and I am by no means an expert.
First, I have found that, in order for a backup of a replication 'publisher' to be 'use-able', the publication has to be dropped prior to creation of the backup.
Second, it seems like you may have been runnning the backup while your users were on the system. I personally never do that. These large scale 'admin' type functions are always done on 'off-hours'. SQL Server performance can suffer based on any number of configuration and operational issues without me adding to the load.
I don't know if that helps at all. Like I said, I'm no expert.
T
February 3, 2006 at 6:48 am
Tom,
Thanks for your reply. Unfortunately in our environment, there is no such thing as "off-hours".
I have fixed the mess that I created by making a backup of Northwinds and doing a force restore over my "broken" development database. After that, I was able to delete it.
Dropping the publication prior to doing a backup would be a fine idea but it would need to be recreated immediately after the backup which will take hours for all the articles and snapshot (over 650 tables and the database is 21 GB). It is also a load on the server and results in slowness for the user community.
One thing that I do on a monthly basis is use DTS to drop and recreate the tables in an accounting month-end database from the publisher database. I think that this is the option that I will have to rely on to create my development database again unless an easier way can be found.
February 3, 2006 at 1:25 pm
You shouldn't need to drop any publications. I restore my Production (publisher) database on top of my test database fairly regularly. The difference is that I use my regular Prod backup, and my test db is on another server so the users are not impacted by the restore.
February 3, 2006 at 8:35 pm
Same here, backups of published databases and restore in another server can't give you any problems. Replication is not that complicated!
February 6, 2006 at 10:09 am
Rodrigo,
Doesn't the restore try to recreate the database as a new publisher?
February 7, 2006 at 9:22 am
No. even you backup and restore tables like syspublications and sysarticles included in the published database, when you restore it, the database is not marked for replication and is not published.
For example, this check is stored in the master database, so when you restore the db, in the new server the db is not marked as published because in the master is not configured like that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply