I restored a backup of a replication database named "distribution" to a non-replicated development instance (ss2000). Well, when I try to drop the database I am told I can not because it is used for replication. It isn't. But you now how Microsoft is . . . . So the question is: how do I drop the database without rebuilding the server, or jumping through firery hoops????
Thanks!
Scorched
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedI just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
hi all,
our datbase was restore but the publications were all gone.
however replication monitor shows publication and subscription.
worst. can create the publication with the same name because
it raises an error saying that it can't drop publication becuase
there is a subscription in it.
how can i remove the publication when the are already gone in the publication
folder but exist in the replication monitor
thanks for the help
Is there a way to restore a 2000 database to 2005?
will the restore process convert automatically to 2005?
Hello!
I am trying to find out name of the backup file database was restored from (I am restoring from DISK), something similar to physical_device_name in backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
Hi,
I have recently restored a backup of a SQL Server 2000 Database (from my production server €“ which is a shared hosting service) on my dev machine so I can do some testing. The issue I am having is with the permission of objects. Some of the objects were created with the dbo user and some with another login (myLogin). The ones that were created with dbo work fine on my dev machine. The others do not work unless I prefix them with €œmyLogin€?€¦ I get an €œInvalid object name€? if I don€™t prefix them. This is problematic because there are hundreds of stored procs and tables that I would need to modify in order to use them on my dev machine.
Can anyone tell me how to create a new user with the necessary permissions so I can execute my procs and access tables without having to prefix them?
Hope this makes sense €“ please let me know if it doesn€™t.
Thanks,
Mike
Hi
I have an sql server 2005+sp2 instance and a db on it.
I have create a database master key (specifying a password so it WILL NOT get encrypted by the server master key) on my database and backed it up
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test'
go
BACKUP MASTER KEY TO FILE = 'c:Genesis_DBMasterKey.key' ENCRYPTION BY PASSWORD = 'test'
Then I have created a asymmetic key without specifying a password therefore the key will get encrypted bt the database master key.
CREATE ASYMMETRIC KEY card WITH ALGORITHM = RSA_512
And then for test I run
DECLARE @Encryptvalasym varbinary(MAX)
SET @Encryptvalasym = EncryptByAsymKey(AsymKey_ID('card'), 'EncryptedData')
SELECT @Encryptvalasym
SELECT CONVERT(varchar(max),DecryptByAsymKey(AsymKey_ID('card'),@Encryptvalasym) )
And the result is OK.
Problem: as a recovey plan if I restore the database on the same or other instance (althogh the asymmetic key is part of backup) the decrypt doesn€™t work.
I have tried to create a bank database restore tha databse master key and recreate the asymmetric key, but I€™m afraid I€™m still not having any luck with it, as it does not decrypt.
My main issue is that is any case if I have to recover the database , how do I have to make sure that encryption/Dycription will work?
Thanks
I only have the ldf file and the bak file.
My bak file was current to 3/2/2007
My ldf file was current to 3/25/2007
I do not have the mdf file.
How to I apply the transaction from the ldf file to my resotred database?
Dear All,
I have a user with DBOWNER,DBCREATOR role and access is set to deny on viewing any other databases. Now when I connect using this user and try to restore the database. after restore, I can not open it or access it using the same user.
what is causing this.
Thanks,
Is there any workaround on this? I need to migrate my data in sql server 2005 into sql server 2000. Thanks in advance.
View 2 Replies View RelatedHi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
Bye,
Madhavi
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies View RelatedThanks in advance for help.
Could someone please help me for SWL backup restoration and db by user
I restored SQL 7.0 database to SQL 2000. ( by creating empty db on SQL 2000 and restored from SQL 7 backup) -- restore ok..
I need to use same SQL user which is admin for DB on SQL 7, for SQL 2000 also. ( DB user is sql user not domain user)
I put mixed authentication mode ( windows and SQL) in SQL 2000 enterprises manager security tab setting.
I can see DB user is available in DB user list on restored DB but can not access DB when I try to access from query analyser
I tried to create new login with same name as it was in SQL 7 and tried to give full admin access on SQL 2000 enterprise manager but I get error 21002:[ SQL DMO] User 'user' already exists.
Kind Regards
Sunod
I have a SQL 2k database backup and I restore it in sql server 2005.
Is this now a SQL Server 2005 database or a 2K database in a 2005 server.
Means do I need to convert databases somehow when I migrated from 2000 to 2005. Is the above mentioned way a way to migrate... or are ther some conversation tools.
Any help / links / ressources would be Great...
Felix
We have a log shipping database running on SQL 2000 ENT SP4, the secondary DB is in standby/read only mode . we have a job that runs every day to take it offline, make a copy of the files and attach them as a new database for reporting purposes.
We're planning to upgrade to SQL 2005 however, when we copy the Standby/read only database files and attach using "CREATE DAABASE FOR ATTACH" we get :
msg 1284
Cannot attach a database that was being restored.
this works in SQL 2000 but not SQL 2005, is there a way to force attach the DB ?
Thanks.
Hello,
View 10 Replies View RelatedI need a SQL query which lists out directory path from which the database has been restored recently ( i.e backup path ).....
View 2 Replies View RelatedOn SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
Hi,
I'm new to working with replication & I'm just starting to test some implementations. We're still using the beta, v7.00.517. and... basically nothing works, I can't even open any of the replication management features in Enterprise Manager. As soon as I select any item from the replication menu I get:
Error 208: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'msdb.dbo.MSdistributiondbs'
It's either:
A: a bug, B: something screwed up in my system databases, or C: I'm missing something really obvious.
Any insight would be much appreciated.
Gordo
i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
thanks
When setting up Replication in v. 6.5, a 'distribution' database is created, containing these tables:
MSjobs
MSjob_commands
MSjob_subscriptions
MSsubscriber_info
MSsubscriber_jobs
MSsubscriber_status
However, they cannot be accessed via the SQL Server Enterprise Manager interface like tables in other databases, at least I can't get to them. Is this a problem or a feature? I can query them to get their contents, but I'm wondering if there might be something wrong with my installation of SQL Server.
Any knowledge shared would be greatly appreciated.
Regards,
Karl Simanonok
I can't use the distribution database because it doesn't exist, and I can't create the distribution database because it already exists.
A failed replication set up had left a fat distribution database that caused the server CPU to peg 100% when I tried to set up a new Transactional replication (halfway through the wizard).
I removed all replication objects from SMSS, but the only thing that fixed it was deleting the distribution database with an ALTER to OFFLINE and a DROP, and restarting SQL.
Now, I'm unable to set up replication.
When I try, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."
In Distributor Properties, the Publisher is shown with a distribution database of 'distribution'. If I untick that, SQL asks if I want to disable the Publisher. I say yes & get the error "Database 'distribution' does not exist.'
Ok, so let's create it. In the General tab of that window I create a new Distribution database and call it 'distribution'. Then I get the error 'Could not add the distribution database 'distribution'. This distribution database already exists.
exec sp_helpdistributor shows distribution database as 'distribution'
SMSS doesn't show the distribution database at all.
How I can proceed?
We have a production SQL2K DB with publication.
I found replication stopped, distribution database was marked suspect
and log file is gowning.
I have tried using sp_resetstatus and restart server but recovery
said some IO errors reading distribution's datafile, so I guessed distribution database was totally dead.
I have handful documents telling me how to remove replication and
I am going to take that ride.
Before I do anything real,
is there any warning from you Prof people?
is there any trap I may encounter into?
is that OK to rebuild replication after I remove the dead replication?
highly appreciate for any help and comments.
thanks in advance!
-DW-
What job cleans out the distribution database?
Cna you perform maintenance to keep the size down?
Thanks,
Thomas
ThomBeaux
Hello,
I have my distribution database at C drive, I have to move this database to another drive D.
May anybody tell me how do this secure, please.
This production environment so I can't make any mistakes.
--
Regards
Hi all,Is it possible to move the distribution database to a new folder/drivewithout removing replication? I am attempting to do it the same way youwould move tempdb:ALTER DATABASE distribution MODIFY FILE(name = distmodel, filename = 'C:DISTMOVEDdistribution.MDF')ALTER DATABASE distribution MODIFY FILE(name = distmodel_log, filename = 'C:DISTMOVEDdistribution.LDF')But I get -Server: Msg 5037, Level 16, State 1, Line 1MODIFY FILE failed. Do not specify physical name.Thanks,Josh
View 2 Replies View RelatedI would like to use SQL Server Express with Advanced Services for both a centralized publication/distribution database and a local subscribing database for offline.
I need to configure the distribution database but do not see the "configure publishing and distribution" option while right-clicking on the Replication folder in SQL Server Management Studio Express.
How do I get that wizard to show? I do see the "New" -> "Subscription" wizard.
I am fairly new, any help would be greatly appreciated with configuring my centralized database. Thanks in advance!
Hi all,
I have set up sql 2005 transactional replication .The environment here cannot accept latency so I guess I will have to go with sync db option at the distribution db.My question is what should be my distribution db back up strategy.Should it be scheduled after the backup of the publication db.I am confused.Somebody please help me regarding scheduling it.
Thanks,
SATYA
Hi:
I'm an asp.net programmer and my database is in access format.My server is Windows Server 2003 Enterprise Edition Service Pack 1.I do not need microsoft access installed on my server to access my database through asp.net.
I want migrate my database to sql server 2005
Could I access my database in sql server 2005 format (mdf) through asp.net without sql server 2005 installed on the server?
could I buy to microsoft only "Database Engine"? orIs there a free distribution of "Database Engine"?
Thanks!!
Hi there,
I'm a newbie. I've installed a replication between 2 NT4 servers, with SQL 6.5. My logreader tasks has crashed "Can't allocate space for objects 'syslogs' in database 'distribution' etc...". With a DBCC SQLPERF(LOGSPACE) I can see that the log is 100% full, but I can't dump it : when I use DUMP TRAN DISTRIBUTION WITH NO_LOG, my distribution log is still 100% full. So how can I do to dump it and what can I do to prevent it from filling up ? Do I need to dump it like I do for my database but how could it work if a DUMP TRAN doesn't work ?
Thanks in advance.
Sorry for the English, it's not my native language.
I have a distribution database that has grown over 50g. Distribution cleanup takes over 15 hours to run and there are always very few rows in msrepl_Commands. Usually only a few hundred. Everything is being replicated fine, but I fear a storm coming. why distribution would be so large with so few rows? Also, Ghost Cleanup has been running all week, so I'm thinking maybe the records marked for deletion aren't being deleted?
View 1 Replies View Related