Restored A SQL DB But It Did Not Restore Users
Mar 1, 2001
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 2 Replies
ADVERTISEMENT
Apr 8, 2002
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 3 Replies
View Related
Jul 29, 2015
when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.
Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.
What does SQL Server or Windows do after the restoring?
pic a:
pic b:
I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .
pic 1:
pic 2:
see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?
Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...
View 1 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Jan 24, 2006
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 3 Replies
View Related
Jun 25, 2015
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies
View Related
Dec 25, 2007
hello there... i'm new here... i just want to ask for help.. i have created new sql server 7 in a new domain server named sqlserver and i have already restored my backup from the old sql server. but after restoration, i checked the users and found only bdo. i want to restore also all the users from the old sql server to the new sql server because basically they are the same. please help!
View 1 Replies
View Related
Oct 4, 2004
Hi All,
Greetings,
Sql server 7
I am trying to restore a database of sql server 7 to another. Restoring is happening fine with respect to tables, stored procedures etc, but i am not able to restore the users that are present in the source database.
Please help in this.
TIA
View 1 Replies
View Related
May 18, 2007
i restored a database from a .bak file.
from production to development.
But i dropped the database from development before restoring. Now all the users are gone from this database. Anyone know how to recover them?
the users from development are different from production's
View 14 Replies
View Related
May 28, 2002
Hi,
After doing a database restore on my development server, It only shows DBO As the db user..When I query SYSUSER all other users are still there..But doesn't show up in enterprise manager...
Anybody came acrosss the same situation ?
Thanks for you help in advance.
View 1 Replies
View Related
Jul 22, 2002
I made a backup of a production database and copied that backup over to a development server and restored the database. Now I have users saying that the application is failing on development. I have users that have NT authentication and some with SQL authentication.
What is the best way to get everything to sync up again?????
View 1 Replies
View Related
Aug 30, 2004
HI guys,
I just restored a database on a new server with a backup(complete backup stored in backup device) from another database on another server using the "with move" option. In fact here was my process:
Alter database ngauge SET SINGLE_USER WITH ROLLBACK IMMEDIATE
restore database ngauge
from disk = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPgauge1.BAK'
with move 'NGAUGE' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Data.MDF',
move 'NGAUGE_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Log.LDF'
Alter database ngauge SET READ_WRITE
it worked.
But it did not move the 98 or so users/logins...from the source database
what is going on??? what am I doing wrong??!!
Please help!
'W
View 2 Replies
View Related
Nov 25, 2005
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
View 1 Replies
View Related
Apr 21, 2008
How would I save a users password to a table, change it to a default password and restore it if necessary?
View 3 Replies
View Related
Apr 29, 2015
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
View 1 Replies
View Related
May 7, 1999
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
View 1 Replies
View Related
Jul 28, 2015
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
View 5 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Dec 6, 2007
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 10 Replies
View Related
Sep 28, 2015
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies
View Related
Oct 19, 2000
Hi
If I dump a replicated Db and restore it onto another DB, how do I switch off replication from the restored DB. I believe there were some scripts developed for this in 6.5.
Any suggestions will be appreciated.
Rich
View 1 Replies
View Related
Jun 3, 1999
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 1 Replies
View Related
Oct 19, 2001
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 1 Replies
View Related
Sep 11, 2007
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 13 Replies
View Related
Oct 17, 2007
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 6 Replies
View Related
Apr 20, 2006
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 4 Replies
View Related
Apr 30, 2008
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
View 9 Replies
View Related
Jan 20, 2006
Is there a way to restore a 2000 database to 2005?
will the restore process convert automatically to 2005?
View 1 Replies
View Related
Jan 6, 2014
I submitted the following T-SQL:
--BACKUP CERTIFICATE EncryptTestCert
-- TO FILE = N'c:backupEncryptTestCert.cer'
-- WITH PRIVATE KEY
-- ( FILE = N'c:backupEncryptTestCert.pvk',
-- ENCRYPTION BY PASSWORD = N'T0yp0calypse'
-- )
[Code] .....
However, the return data just contains nulls, instead of the original decrypted data. You can see above that I deleted the certificate, but then restored the certificate from backup. However, it doesn't work.
View 2 Replies
View Related
Aug 15, 2007
Hey Guys,
I have a .dmp backup file from an oracle database. I want to restore it into an sql server database. Can this be done?
View 2 Replies
View Related
Feb 5, 2008
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 3 Replies
View Related
Dec 7, 2007
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 5 Replies
View Related
Mar 31, 2008
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 10 Replies
View Related
