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
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 RelatedI am new to this, so, I will be greatful to anyone who can clarify the following:
qn 1:
In login properties under Logins of Security, what does creating mapping to the different databases mean ?
For a given login, I notice that I can login / execute DDL statements alike for the databases which are checked and also for the ones that are not checked.
Then, what does
Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login -> checking the database - accomplish?
qn 2:
For a perticular login, say 'Bob' which has server admin rights, I wish to include two more databases. For this I 'checked' the databases, which I wish to include, in the
Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login
When I clicked 'OK', I get the following error and the two databases are left unchecked.
User, group, or role 'SQL1AGENT' already exists in the current database. (.Net SqlClient Data Provider)
How can I include the databases ?
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 Relatedhello 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 RelatedHello,
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
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
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.
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?????
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
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
We are running SQL Server 2005 in a Windows 2003 domain and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group. Any attempts by these users to login result in a login failed,
Error: 18456, Severity: 14, State: 11
Which indicates that it is a valid user who does not have access to this SQL Server.
I have been able to narrow the failures down to the following situation:
Create a user, TestUser1, as a member of 1 domain local group TestGroup1
Give TestGroup1 access to SQL (standard public access to master)
All good. Login succeeds.
Add TestUser1 to another domain local group TestGroup2
Attempt to login to SQL Server -> login failed.
Add the user explicitly -> login failed
Add one of the groups to sysadmin -> login succeeds
It seems that as long as the user is a member of more than one AD group, and none of those groups is a member of the sysadmin server role then the user is unable to login. Obviously having all of the users as sysadmin isn't a workable solution, has anyone seen this issue before?
I have been able to replicate a similar situation in our test domain, but in that case the issue is resolved by adding the users explicitly to SQL Server (still not an ideal solution).
Interestingly, if I run the same test in our test domain but use global groups, it works. But unfortunately the network admin tells me the groups must stay as local.
Any help would be greatly appreciated.
Regards,
Daniel Watkins
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 RelatedDevelopers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
Hi,
I have every nights a backup script that is running using the following command on my sql server 2000 :
backup database MYDB to disk="d:mssql2000MSSQLBACKUPdb_MYSERVER_MYDB.bak" with init
Now, i need to restore one of these files to a new Database with another name...in order to look at certain table and its content
I'm unable to find the correct RESTORE command and didn't find how to do it using enterprise Manager..
Any help would be VERY welcome ;-))
Florent -- Near the whole
Hi,After just installing sql 2005 on our test machine, I am trying torestore a database using a backup file on the network. However, thespecify backup diagol box allows only for browsing to a file on thelocal machine - I find no way to type a UNC path to choose the backupfile from. I can do this in 2000, is it no longer an option to doitthrough the GUI or am I missing something?ThanksKR
View 1 Replies View RelatedHi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same
name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
Hey
Quick question for the experts here! Wink
We have the issue of wanting to restore an MSSQL database (.BAK) but are unable to "Connect" to the existing database; but we are able to "Start" the database currently.
When we are unable to perform a "Connect", then under "All Tasks" you don't get the option to "Restore Database"
I am hoping there is a way around this and still a possibility of doing a restore without having to do an entire install of MSSQL again and then a restore?
Thanks!
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
I am trying to restore a SQL Server 2005 database backup file.The size of the database backup is 5.51 GB. I specify the source as a device and then provide the path of the .bak file and press OK i get the error as follows
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'G:ITWeb DB BkupDB BkupNew BkupWSS_Content_ITWEB.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Please let me know how we can solve this issue.
Satish
Hello,
My name is Elizabeth and I'm a SQL newbie.
So we do SQL 2005 Database backup daily. At one point, we have to restore the backup. However, we cannot restore it whatsoever; it keeps saying that the database is in use.
It is actually a SQL Database for a CRM Server. I stop the internet service, so CRM or any other instances cannot pull up information from the SQL Database. I try running sp_who and killing the processes that are still using the database, but those processes keep coming back again.
Here is the error that I keep getting.
Could anyone please help me on this one? Thanks in advanced!
-Elizabeth K.
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?
Hi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is urgent. WIll be VERY grateful for your urgent reply.
Hi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is really urgent. Will be VERY grateful for your urgent reply.
I have restored the master db in single user mode.
---
Processed 440 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
---
I removed single user mode parameter(-m;)
When I start SQL Server in configuration manager getting below error
---
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details
--
I'm trying to test my backups by restoring from production onto a DEV machine.
However, when I insert the backup tape into the drive, then go into SSMS and right click on
Databases -> Restore Database -> From Device .... -> Tape -> Add -> (my tape drive \.Tape0) -> Contents
it displays the contents as it should.
So I click "Close" to close the contents and bring me back to the Specify Backup where I have \.Tape0 selected and I then click "OK"
SSMS sits for a few seconds, and then I get a time out error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
That's all the further I can get... any suggestions?
I'm VERY perplexed by this...
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
I am using sql server 2005 and unable to restore backup file(.bak) file when i ry to restore it to other database it shows following error
restore failed for server (microsoft.sqlserver.smo)
additional information:
system.data.sqlclient.sqlerror : the media set has 2 media families but only 1 are provided.All members nust be provided.(microsoft.sqlserver.smo)
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?
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies View RelatedI'm trying to test my backups by restoring from production onto a DEV machine.
However, when I insert the backup tape into the drive, then go into SSMS and right click on
Databases -> Restore Database -> From Device .... -> Tape -> Add -> (my tape drive \.Tape0) -> Contents
it displays the contents as it should.
So I click "Close" to close the contents and bring me back to the Specify Backup where I have \.Tape0 selected and I then click "OK"
SSMS sits for a few seconds, and then I get a time out error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
That's all the further I can get... any suggestions?
I'm VERY perplexed by this...
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?
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