Database User Problem When Restoring
Mar 14, 2001
Hi everybody
i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..
some body please tell me fix for this broblem
ThanKs prasad
View 2 Replies
ADVERTISEMENT
Mar 17, 2007
Hi, Folks. I hate to have to come back and ask about restores right after I had to ask about Back ups, but I'm in a bind and can't seem to find other resources that address this issue.
I'm using VB Express 2005 and SQL Express 2005 to develop a windows form application that uses a User Instance of a database. With help from this forum, I was able to get the backup working. Now, I can't get the restore to work. Here's the code I'm using:
Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.NCSConnectionString)
SqlConnection.Open()
Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)
Dim srv As Server = New Server(ServerConnection)
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(My.Settings.DefaultBackUpPath & "NCS_Full_Backup1.bak", DeviceType.File)
''Define a Restore object variable.
Dim rs = New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
rs.ReplaceDatabase = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
rs.NoRecovery = False
'Specify the database name.
rs.Database = SqlConnection.Database.ToString()
'Restore the full database backup with no recovery.
rs.SqlRestore(srv)
'Inform the user that the Full Database Restore is complete.
MsgBox("Full Database Restore complete.")
The error is:
- InnerException {"RESTORE cannot process database 'C:DOCUMENTS AND SETTINGSMARCOSMY DOCUMENTSVISUAL STUDIO 2005PROJECTSWORKING COPY OF NCSNCSBINDEBUGNCS.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally."} System.Exception
I think that I need to either free up something that's locking the database or I need to switch to the master table in the User Instance. I tried a number of variations in attempt to try both of these avenues without sucess.
I'd really appreciate any guidance that I might be able to get.
Thank you,
MF
View 11 Replies
View Related
Apr 11, 2007
Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\.pipe4A1F91FF-F6FE-45 sqlquery'. "
"Exclusive access could not be obtained because the database is in use."
I have implemented the changedatabase method as described in that thread.
SqlConnection.ChangeDatabase("master")
right before the line
rs.SqlRestore(srv)
I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?
I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.
Robert
Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)
sqlconnection.Open()
MsgBox(sqlconnection.Database.ToString())
Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)
Dim srv As Server = New Server(ServerConnection)
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)
''Define a Restore object variable.
Dim rs As New Restore
'Set the NoRecovery property to true, so the transactions are not recovered.
rs.NoRecovery = True
rs.ReplaceDatabase = True
'Add the device that contains the full database backup to the Restore object.
rs.Devices.Add(bdi)
'don't know why the below norecovery is changed to false
rs.NoRecovery = False
'Specify the database name.
rs.Database = sqlconnection.Database.ToString()
sqlconnection.ChangeDatabase("master")
'Restore the full database backup with no recovery.
rs.SqlRestore(srv)
View 4 Replies
View Related
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
Sep 10, 2000
I have a nightly job that restores a production database to a reporting server. I have some report- related stored procedures
that need to run on the reporting server once the database is restored. However, during the restore, the permissions for the
reporting stored procs get deleted and replaced with the production permissions, which are useless in this case.
Is there a script or system stored procedure I can schedule to run after the nightly restore that will also restore user
permissions? Thanks, Frank.
View 1 Replies
View Related
Sep 8, 2005
I'm new to working in SQL, so forgive my lack of terminology.
View 1 Replies
View Related
Dec 29, 1999
I am copying a database to an alternate server by restoring a full backup onto the new server. However, whether I create the logins prior to the restoration or not the user accounts in the database no longer map to logins in the master.
Unfortunately it is not simply a case of dropping the acounts as they own objects in the database.
My best solution to date has been to use the sp_addalias to alias logins of the same name to the original user. However this is a far from ideal situation as one cannot readily tell that the logins are aliased and there must be an additional overhead in doing this.
View 3 Replies
View Related
Feb 29, 2008
Hi all,
I'm kinda stuck with the following issue and would apreciate some help with it.
Basically i need to have a user within SQL that has rights to restore databases and is the owner of that database so it can alter the data. The problem i face is that at this moment i see no other way then making that user either member of the server role "sysadmin" or "serveradmin".
Anyway, these roles have to much rights. I tried using the dbcreator role which will make a the needed user db_owner but only when a new database is created, not when a database is restored.
Tried to resolve this with a DDL trigger and then temporarily run as a user with serveradmin rights, however there is no system event for restore database, so that doesn't work either.
Anybody got any suggestions?
View 3 Replies
View Related
Jul 18, 2001
hi everybody.
Scenario
Company X has Sql server and domain x.com
so
on sql server
they have logins
XA
XB
XC
Back of database Production was done
database Production was restored in Company Y
use Production
select name from sysusers
name
----
XA
XB
XC
sp_dropuser 'XA'
Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'XA' does not exist in the current database.
How to drop 'XA' ?
View 2 Replies
View Related
Sep 18, 2006
Hi all.
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
Thanks in advance for your help.
View 1 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
