Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
I 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
View Complete Forum Thread with Replies
Related Forum Messages:
Do System Databases Need To Be Restored?
The consultant hired as our Remote DBA for SQL Server 2005 recently changed the recovery model on our user database from Full to Simple. He explained this was necessary because the transaction log was growing very large and backing up the database when the model was set to Full was not shrinking it. Now, the master database and the msdb database are backed up daily along with our user database. When we need to restore our user database, do we need to restore the master database and the msdb database too? What order should the databases be restored in?
View Replies !
SQL 2000 DB Restored On A SQL 2005
Greetings! I'm wondering if a db from an sql server 2000 restored on a sql 2005 server will use algoritms from sql 2000 or sql 2005. It seams to work quite well with a few exceptions, it's only for test anyway. I'm sorry if this has been asked before but I could not see it. Thanks IceDread
View Replies !
Sql 2000 Database Restored Sql Server 2005
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
View Replies !
Restored SQL 2000 Backup, Stored Procedures Don't Work
Hello I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures. I get the following error: Could not find stored procedure 'xxx'. If I use enterprise manager and go to the procedures tab the procedure appears there. Any ideas what is up? PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem? Thanks in advance.
View Replies !
Working With Multiple Databases In SQL Server 2005
Hello, Am working on a prototype web application which would cater to 5 countries, as such I would have to store data related to the 5 different countries Now in my prototype, I have a webserver and 2 SQL servers. I have thought of 2 approaches for storing data in the DB, 1) One would be to store all the data pertaining to all the countries in separate tables in the same DB 2) Separate DBs for each of the countries So the questions are1) Any real advantages to doing this, for security, maintenance, etc?2) Any issues with one app / multiple DB;s (it's going to be a single server app for the first release.)3) Any other things that i should take care of? Any feedback on this would be appreciated.Thanks!
View Replies !
Restored Failed
Hi, Can someone help me, I have this DB that needs to be restored. I have the full backup and the differential backup. When I'm trying to restore my differential backup it prompts me this error "System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)". Then I try to create a test DB and restore my full backup there, it was succesfully restored and now try to restore my differential backup since I need the newest backup I received that error. I have already used the option leave the database non-operational but still the same. Thanks. Russell
View Replies !
Size Of Restored Database
Hi all, I executed sp_spaceused against my test database to get the following - database_name database_size unallocated space --------------- ------------------ ------------------ TestDatabase 12675.00 MB 2541.64 MB reserved data index_size unused ------------------ ------------------ ------------------ ------------------ 5734000 KB 2862056 KB 5288 KB 2866656 KB Question - 1. If I backup this database, the size of the backup file would be size in the data column (2862056 KB). Am I correct? 2. If I restore the database from this backup, the restore will contain the same size values as indicated in the outpout of sp_spaceused above. Example - database_size (of restored database) = 12675.00 MB. Am I correct? Let me know if I understand the concept correctly. Thanks for your help. - Vivek
View Replies !
Dump Of SQL Db Is Restored To A Different SQL Server
Hello, Is it possible to use a dump from a database to do a restore of a db on a different server? I have a SQL 2000 server and having been given a file called test.dmp (I am not sure what SQL version this is). Can I use this to do a restore to a new db I have created on my server? If so what method do I use? Thanks reorg
View Replies !
SQL Server 7 Security After DB Have Been Restored
I have 2 SQL Servers 7. DBs on the first were upgraded from 6.5. A DB has 2 users: dbo + user1. User1 is also uses user1 login I created a DB backup on the first server copied it on the second & restored on the second into existing DB. The problem is that after that user1 is not in the list of the restored DB on the second server & I can not include it in the DB & grant access to user1 login to the same DB and error message is "15023 User or role "user1" already in exists in the current DB.
View Replies !
Restored A SQL DB But It Did Not Restore Users
Hello, Im running SQL 7.0 in production with no SQL service pack applied. I am trying to copy the database over to a test box running SQL 7.0 SP3. Both servers are NT 4.0 with SP6.1a. When I restore the database on the test box, I do not get the Users restored but I do get the Tables and other items. The SQL backup Im restoring from is a complete backup. Could the fact that the server Im restoring on is SQL 7.0 SP3 and it came from a pure SQL 7.0 server? Thanks
View Replies !
Database Restored, Publications Gone
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
View Replies !
Attach A Read-only Restored Database
hi allcan i attach a read only restored database. I restored adatabase Test in Stand by Mode. After that i detached that database andwhile restoring i m getting msg "Cannot attach a database that wasbeing restored.". Is it o that i can not attach a readonly restoreddetached database ?Parveen Beniwal
View Replies !
Asymmetic Key Recovery On A Restored Database
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
View Replies !
DBOWNER Can Not Access The Restored 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,
View Replies !
Object Permissions On Restored Database
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
View Replies !
Backup File Database Was Restored From
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
View Replies !
Service Broker Erro But Everything Working
Hi I have been running service broker between 3 initaitors and a single target witha forwarder for some time. Everything works 100%, i use persistent conversation and all queues and transmission_queues are clear. Everything works 100%. However while running profiler on the forwarder i see the following errors every now and again. Actually every time a message succesfully gets to central i see this error: On the forwarder: Broker Connection: EventSubClass 4 - Closing : An error occurred while receiving data: '64(The specified network name is no longer available.)'. Database ID 1 : master: ObjectName : tcp:11.45.101.100 - this is the target i sometimes see the exact same error with a initiator IP. I am very confused becuase everything is working 100%, queues and transmission queues are all clear. Why do i see this error ? Thanx
View Replies !
Access 2005 Databases From 2000
I have upgraded 2000 databases to sql server 2005.If the users use just query analyzer & enterprise manager in 2000 to access 2005 databases. will there be any issues??? let me know
View Replies !
Managing SQL 2000 Databases From SQL 2005 EM
Hi, I have just installed SQL 2005 client tools and management studio to register SQL 2000 databases. Although it registers the servers successfully , I can't see any activity monitor on the SQL 2000 registered server . Is it something more that I have to install to see what's happening ? Thanks in advance.
View Replies !
Merging 2000 And 2005 Databases, Save As 2000
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge? TIA
View Replies !
How Do I Determine The Backup Date Of A Restored Database..?
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 Replies !
Logins Are Not Restored While Restore The Database To Another Server
Hi 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
View Replies !
Old Excel Query Don't Work After DB Restored To Another Server
Hi.. I have just rebuilded our SQL Server and reinstall the OS. But after we restored back the DB into the DB name and All DB Logins. We found that those old excel query that we quert to old server doesn't work any more. Below is the error message Connection Failed: SQLState:'08004' SQL Server Error 4060 Server rejected the connection; Access to selected database has been denied Even I chang the datasource to the new DB name, Old query still doesn't work. When I go to the Edit Query, it is grey off. Is there any way to edit the old query
View Replies !
SQL USER's ADMIN RIGHTS ON RESTORED DATABASE
Thanks 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
View Replies !
Restore From Different Server - User (sid) Does Not Match Restored Db - Fix With Sp_changeobjectowner
This is more of a fyi than a question.After restoring a live db backup to our development server there wasan identically named login Melb02 on both the db server and in therestored db backup.However if you log in as Melb02 on the server you cannot access anyobjects owned by Melb02 on the restored db, without puttingMelb02.<object_name> in front. This would break our app so we neededa workarround.The mssql docs say that first check if the login is the owner, thendbo then deny access - something to that effect. So this wasconfusing.workarroundI think what happened was that the db restored from the live remoteserver had a different security id (sid) to the development serveralthough both users had the same name: Melb02. Thus if you log in asMelb02 you can't get access to Melb02 objects on the restored dbbecause of the different sid.we wrote a cursor that went though everything in sysobjects belongingto Melb02 and did a : sp_changeobjectowner to a new user login thatour app now happily uses.Jol.
View Replies !
Restored My SQL Server DB First Time, Now Its Read Only? How To Set Correct Permissions
Hi all, I am new to SQL Server 2005 and databasing in general, I have had a webhost restore a copy of my online database but now it is read only. Could someone tell me how I go about letting the database to be writeable etc for online use. If I right click the db and go to permissions there is nothing in there, should there be? Do you have to manually set permissions for each table after restore??? Any help would be great
View Replies !
Restored Full With NORECOVERY And Db Reads DbName (Restoring...)
Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error: System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo) Does anyone know what I need to do? Thank you in advance. Julio
View Replies !
Service Broker Message On Remote Instance Not Working
I was trying to send a message on a different instance in the network using service broker. I have created the endpoint and route both the side. I could see that the messages are in the transmission_queue in the sender side with no error in the status. What could be the reason. I am doing the following: CREATE ROUTE inst02 WITH SERVICE_NAME = 'SERVICE2', ADDRESS = 'tcp://10.14.43.149:2341' in the send script i am using this: BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE [SERVICE1] TO SERVICE 'SERVICE2' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ; in the sending side if i change the endpoint's authentication as windows kerberos , then i get thefollowing error in the transmission queue: "Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 66." I also have given access to the remote user on this endpoint(on the recever side) using this : GRANT CONNECT ON ENDPOINT::Endpoint_test to paras I am executing the sending side send script using the same user wich has access to the remote endpoint. Can some one resolve this issue. Thanks
View Replies !
How To Reverse Migrate Databases From SQL 2005 Express To SQL 2000
I've got about 2 months worth of daily databases created in SQL Express 2005 for a pilot project, which the client is converting to a purchase. Unfortunately they're using SQL Server 2000 and I can't figure out how to migrate the databases to the older version. All my searching turns up how to do it in the expected direction. Any pointers to how or where to search appreciated. Thanks, Wendell
View Replies !
Error Connecting Database Problem After Moving Databases 2000 To 2005
I have moved my databases to 2000 to instance of 2005 on the same server. Now i set databases offline in 2000 as i did upgrade use backup & recovery method. & my connection string Password=test;Persist Security Info=True;User ID=test;Initial Catalog=databasename;Data Source=ipaddress i was using above connection string to connect when using 2000 databases form applications in the production machine I get the following error when i am running my applications now after moving to 2005 An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) IS it because i have two server on one server or What do i need to do in order to fix this....tried several thing by searching nothing worked out.. Let me know thanks
View Replies !
|
TRACKER
