Restoring Database And Losing Users

Apr 12, 2000

I backed up and restored a database successfully except for the users. The only user that shows is dbo, but I can't add a user with the proper user name from my old server; I get the error that the user or role already exists in the current database.

When I go to role, name of public, there is the user I need to add. I can't remove it as it's a table owner, but I need to add it to the list of users, so I need to remove it then re-add it.

I tried using EXEC sp_changeobjectowner to no avail.

Thanks for any help/guidance.

View 2 Replies


ADVERTISEMENT

Losing Logins When Restoring Database

Aug 7, 2000

When I a set of databases from backup all login information is lost. The front end of the application provides a way to create new users, but it is a very long process. I am looking for a script or stored procedure that will help attach the users to the database.

View 3 Replies View Related

How To Preserve Users When Restoring A Database

Jul 20, 2005

Hi,I restored a database 2 months ago. After the restore, I lost all my"users" for that database. It has been a while, so I don't rememberthe detail of what I did.How can I reserve all the users when restoring a database from a file?Did I do something wrong?Thank you in advance,Eddy

View 1 Replies View Related

Restoring DB (users/login Problems)

Apr 11, 2007

Hi,

I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.

I am restoring in the following order

1.) Creating empty db "123"
2.) Restoring database from file to database "123"
3.) Creating Login to this database (error happens when linking it to the database, "user exists")

When connecting thru QA I get "Cannot open user default database login failed"

I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :S



Any suggestions much appreciated!! :)

Thanks!
mike123

View 4 Replies View Related

Restoring Master Did Not Bring Back Users

Jun 28, 2000

I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?

Thanks.

View 2 Replies View Related

Reseting Users After Restoring SQL Server 2005

Jan 9, 2008

I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.

any assistance would be greatly appreciated.
thanks

View 5 Replies View Related

Restoring Database Leaves Me With (Restoring...) Message

Jan 9, 2007

I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)

Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.

Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.



Any advice on how to get this thing finished out?

View 3 Replies View Related

Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?

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

Difference Between Database -- &&> Users And Security --&&> Users

Nov 28, 2006

Hi Team,

In SQL Enterprise Manager, when we expand "Database -->Users", we see the

users there. When we expand "Security --> logins" we see the same users there.

Can you differentiate these two.

Thanks

Santhosh

View 1 Replies View Related

SQL Server Admin 2014 :: Restoring A Database Even If No Database Or Backup Encryption

Sep 3, 2014

I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.

I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

View 6 Replies View Related

SQL Server 2014 :: Restoring Database Programmatically But Not Over Existing Live Database

Aug 6, 2015

I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:

SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)

I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.

View 5 Replies View Related

Restoring A Backup Of A Subscriber Database To A New Database

Dec 1, 2006

I have a Subscriber database updated using transactional
replication and want to create a copy for development & testing. The Distribution database is running on the
same 2005 instance as the Subscriber and the Publication database on another
server running SQL Server 2005.



If I use a TSQL script to run the restore, the database is
restored to a usable state but I get the following messages:









Msg 15247, Level 16, State 1, Procedure
sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job [job name] was not removed because
it has a non-standard name; manually remove the job when it is no longer in use.
(I€™ve removed the job name from the message)
RESTORE DATABASE successfully processed 46219 pages in
935.413 seconds (0.404 MB/sec).



It looks like an attempt is being made to restore
replication information too. I note that
the RESTORE statement has a KEEP_REPLICATION options which implies (I would have
thought) that by default the replication information is not be restored.



Incidentally, before implementing replication, backing up
the live database and restoring the backup to a new database took < 7
minutes but running the same process with a Subscriber database backup takes
about 15 minutes.



I€™d be interested to know if anyone has any idea what I€™m
overlooking or doing wrong.



ThanksTim

View 7 Replies View Related

Restore Of Database Backup To Another Database Makes Users DBO

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

Restoring Database

Oct 4, 2001

1) We have got the database dump (.bak) file in one machine and we need to restore this to another machine where the SQL server (version 7) is running. But in the restore option there is no way to choose any mapped or shared drives in remote machine. Any pointer to resolve this problem?

2) Can you set up a database if you have the .mdf and .ldf files?

View 1 Replies View Related

Restoring A Database

Jan 12, 2001

I am running SQL 7 on two seperate NT4 servers, a production server and a standby server. Both servers are configured for Mixed Mode authentication. If I back up a database on the production server, do I also need to back up the master database and restore it. My reasoning behind this is that users' login details will be held in the sysxlogins tbl in the master, while their access rights will be held in the sysusers tbl in the database I am backing up and restoring. If I back up and restore a database without backing up and restoring the master db as well, will the connection between the system login and the database user be broken.
Any comments/suggestions would be appreciated.

View 1 Replies View Related

Restoring Database

Sep 14, 1999

Hi there...

can any one help in following situation.

Suppose in a network I have two sql server ABC and XYZ and XXX (NT server) on same sub net.

1) I take a backup from server ABC on XXX (NT).(ie.. backup on network)

2) Can I restore database in XYZ which was backed up by ABC? How???

3) Suppose My ABC machines fails and I have to again install SQL Server 7 on it, will I be able to restore database from XXX(NT)? how???

regards,
zak

View 1 Replies View Related

Restoring SQL 6.5 Database

Jan 23, 2001

Currently have large database in three devices, named Cabinet cab2 and cab3. Cab3 device has been expanded twice. We are upgrading the hardware and would like to get the database into one device named cabinet and clean the mess up our vendor initially created for us. Any Ideas/steps on how this could be accomplished??? TKS Jerry

View 2 Replies View Related

Restoring Database On SQL 7.0

Mar 31, 1999

Using SQL 7.0, has anyone backed up a database and restored on to a different database without the device files being moved around.

For example, With SQL 6.5 we used to backup PROD (source) database and restore to TRAIN (target) database. It worked just great regardless whether the databases are located on the same drive or not. We loved it and used it to refresh TRAIN from PROD on a weekly basis.(using sql Enterprise Mgr)

However, in SQL 7.0 when we attempt this procedure the database and log files of the TRAIN (target) are getting relocated to the drive location of PROD (source) database. Very strange.

Though thru object transfer i.e. import ,we can import data from PROD(source)
to TRAIN (target) database, but it is very slow and takes lot of time and performance degrades for source database (PROD)

Any input on above will be appreciated if backup / restore thru
enterprise mgr or TSQL procedure works in SQL 7.0 for restoring another database which is also on different drive i.e PROD (source) database on
drive C and TRIAN (target) database on drive E.

Thanks
Rajiv

View 1 Replies View Related

Restoring A SQL Database.....

Jul 19, 2000

While this may be in the wrong forum, I thought that I should try here first.

One of the developers that works for this company would like to restore a sample database to the SQL server after installing our software.

Is there a command line command that can be issued to restore/create a database with a file (.bak) of an SQL database (sample)? If this can't be done, can it be done with VBScript or something similar?

SQL is not my strong point, and neither is development. Any help would be appreciated.

TIA.


Joe

View 1 Replies View Related

Restoring The Database

Mar 7, 2005

Hi,

Two of my hard drives went down yesterday. and with it went the Sql server database.The data recovery guys recovered the back up database (.bak) file.I have since reinstalled SQL Server 2000 and now when i try and restore the .bak file it says the Microsoft tape back up file set is not valid when i try and open the .bak file.

Whats the right way to add my databse to the SQl Server2000 using my .bak file and if anyone has come across the error they could help.


Thanks

L

View 7 Replies View Related

Need Help With Restoring Database

Mar 9, 2005

hey...
when i try to restore my database... they gave an error. the database is still in use.. is there a way to kill all the connections to that database ?

View 3 Replies View Related

Restoring A Database

Apr 24, 2008

Hi,

A while back, I used SQL Server 2000 to back up a database. Now, I am wondering if there is a way to restore this backup into a SQL Server 2005 environment as I no longer use SQL Server 2000?

Thank you

View 1 Replies View Related

Restoring Database

May 30, 2008

Hi all,
Our Application is made to work with different database. I have a few query
1. There is option of restoring for only table, the tables can be mention during runtime, how to acheive same in sql
2. During restoring if already same data exists, it throws an exception , based on the exception we display the message data already exist. In sql any option is available to check the data exist or not
3. Can sql create the column during runtime.

Thanks

View 4 Replies View Related

Restoring Database Help

Feb 26, 2006

theres two .mdf files
game_log.mdf
game_data.mdf
is there a way to restore a database with these files but not with .bak (backup) files? i couldn't connect to database so i reinstalled sql server 2000 and this time i could connect to the sql server fine but when i tried to restore the database, it wouldn't work when i set it to transcation log and it shows an error

The preceding restore operation did not specify WITH RECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE LOG is terminating abnormally.

please help, thank you

View 7 Replies View Related

Restoring Database

Jul 20, 2005

Hello,due to some testing my SQL Server crashed. I have to reinstall it and ...For now I only have .mdf file and nothing else. My question is: is there anypossibility to restore this .mdf file for SQL 2000? I have some useful datain this file and there will be nice to use it.Best RegardsMarciN

View 2 Replies View Related

Restoring A Database With T-SQL

Oct 24, 2006

Is there a way to restore a database backup file to the default data directory in a specific SQL 2005 instance using T-SQL?

I know that you can use the command RESTORE DATABASE, but that will restore the database files back to it original location. I also know that I can use the WITH MOVE option also, but is there a keyword or some special notation that I can use to make sure that the data/log files are moved to the default data directory for a specific SQL 2005 instance?

I am trying to avoid cases where the original file location does not exists in the current file system.

Thanks in advance,
Rodney

View 1 Replies View Related

Restoring Database

Mar 28, 2007

i was using mssql 2000



how can i restore my database on another server without affecting the data stored on that server,



or



would there be a wizard where i can compose a query of the design of the whole database and run those on another server to update its structure

View 3 Replies View Related

Restoring Database

Jul 13, 2006

Hi everyone,
I made a backup for my Bilgiler database and then by relying on this back up, I erased my Bilgiler data base. so now I tried to restore this Bilgiler database by using Enterprise Manager however I could not succeed.
How can I overcome this ?

Thanks

View 4 Replies View Related

After Replicating A Database, SP's In The Same Database Are Not Able To Modify By Other Users.

May 21, 2008

hi there,

After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...

Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

any suggessions are most welcome.

by
CHINNASAMY

View 1 Replies View Related

Error While Restoring A Database.HELP!!!

Aug 24, 2006

Hello,
I am taking the error message which is in the below side,while restoring a database.(I am using sql server 2005)
The backup set holds a backup of a database other than the existing 'db1' database. (db1 is the database name.)
Can you help me?
 

View 1 Replies View Related

Restoring A Sql Database From Another Server

Jan 18, 2007

Hi
I have a .db file from another server and i am trying to install it at home and get this lengthy error message ( 9 lines long )
Microsoft SQL-DMO ODBC SQL STATE 42000
The physical name ( path blah blah may be wrong)
how do i change the physical path of the .db file i'm trying to restore??
thx

View 1 Replies View Related

Restoring Database On My Localhost

Sep 6, 2007

Hi there,
I am using VWD and have my production website hosted elsewhere. The hosting company provides a backup service that allows me to download a something.bak file as the backup of my website. My query is: how to export or copy the data in the something.bak file into the database on my own computer? Can anyone helps please?
Thanks in advance.

View 3 Replies View Related

Problem Restoring Database

May 12, 2008

m able to take backup and restore the database through gui. Backup is comming with backup query, but using restore query m unable to restore database.It is giving exception is REstore failed for server.what it means ? what is the solution for this?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved