Database Restore Rights - Sql Server 2005
Oct 30, 2007Is there a way to grant rights to a user to be able to restore a specific database on a server, without being able to muck around with other databases on the server?
View 1 Replies
ADVERTISEMENT
I Need To Give DBA Full Admin Rights To SQL 2005 Without OS Windows Rights, Can Anyone Help Please!!
Jul 12, 2007The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
SQL Restore Rights
Nov 9, 2007Hi All,
I've got an SQL 2005 server setup with some databases. I'm trying to set it up so that a user can upload his database backup and then restore the DB using Studio Express but am having some issues with it. The user can upload his database fine, but when we try and go in to restore it, he can't view the directory or file of the backup. I've added the service account that SQL runs as, as well as the SQL2005%Machine%etc... user without much luck. The only thing I can think of from here is SQL permissions and I'm a little vague on how to accomplish what I want. One other thing is that if I add sysadmin rights to the user, they can see the directories fine. I tried adding dbcreator as recommended by other posts and that didn't work either. Any help would be greatly appreciated. Thanks!
Brad
SQL Server 2005 Database Restore Error
Nov 18, 2007Hi experts,
I had a database in sql server 2005 under instance name 'rohitcvman'. I took its backup. The computer is formatted and sql server 2005 is installed again with instance name 'rtamrakarcvmanager'. When I try to restore it, it ends up with the following message (copied from message box).
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I also have its 'MDF' and 'LDF' file. When I try to attach it, I get the following message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'rtamrakar'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 15, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'CVManager'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I have not tried to restore/attach in the same instance name from which it was backed up. Is it compulsory to have same instance name to attach/restore?
Can any one help me?
Thank you.
Sql Server 2005 Restore Database Error
Jan 31, 2008
I am using SQL server 2005 9.00.3042 . When I go to databases restore, restore to Northwind, from device
I select backup device and select SDLT. The actual backup device is LTO3.
The error is an exception occurred while executing a Transact-SQL statement of batch
Timeout expired. The timeout period elapsed prior to completion or the server is not responding.
I checked and we have no firewall turned on this server
I tried on another SQL 2005 9.00.3042 server - this one has an SDLT drive and the error is similar
Restore headeronly is terminating abnormally
Need To Back Up && Restore A SQL Server Database When Only SQL 2000 And 2005 Are Available.
Oct 22, 2007I am maintaining a .net 1.0 asp.net web application that accesses a SQL server 2000 database. I need to backup the database and then restore it on a laptop. Currently it resides on a server. The problem is that on the server they are using sql server 2005. If I backup using 2005 and then try and restore this database using SQL server 2000 or 2005 on the laptop, I get an error message. "Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.
What can I do to restore the database? Must it be backed up with SQL server 2000 and then restored with SQL Server 2005?
Please help!
Unable To Restore Backup Database In Sql Server 2005
Oct 5, 2007I 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)
Cannot Restore Database In The Computer Is Setuped SQL Server 2000 And SQL Server 2005
Dec 27, 2007Hi All
I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows:
Please help me to solve this problem. Thanks
How To Restore A Sql Server 2005 Database Backup To Sql Server 2000
Oct 17, 2006Hi All
This is a very urgent requirement of my client having 200 stores and one Head office. the HO has just installed SQL-Server 2005, The HO sends small backups of the data related to stock transfer which needs to be restored at stores. It was working find when both the HO and stores were SQL-Server 2000. but the process has come to a dead halt as the HO has SQL-Server 2005. It is not possible to convert the stores immediately. please help me out.
Thanks
Raoshan
SQL Server 2005 Master Database Restore To A Different Server
Apr 27, 2008I have never done this in a DR scenario before. What I used to do in SQL Server 2000 was apply the logins to a new installation of SQL 2000 then recover the application databases. I'm using 2005 now and I'm in a different mode where I would like to be able to recover the system (master, model, msdb) to a different server with the same SQL Server 2005 build ---- 9.0.3054. I'm following the procedures from Microsoft where they state to place the instance in single user mode first then invoke SQLCMD then perform the restore. Sounds simple enough.
C:> SQLCMD
> RESTORE DATABASE MASTER from DISK = 'Z:MINIDRmaster_backup_200804200315.bak'
The console is coming back with a message that the backup set holds a backup other than the existing master database. So I suppose that this cannot be done and is only intended for recovery on the same instance.
Any input/experience is appreciated.
Thanks.
Restore Database From SQL Server 2000 To SQl Server 2005
Jul 15, 2007I have a file backup database SQL Server 2000
I want to restore it to SQL Server 2005.
But it failed.
I try :
Database ( right-click) - Restore Database - From Device - Specify Backup Add
Locate Backup File. I must choose File of Type is All File so that i can find file backup from SQL 2000, if i choose File of Type is *.bak,*.trn so i cannot find that file.
And then i choose OK all. But it failed. The error is below :
Restore failed for Server 'DHBK-734D0C3663'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataqlsach_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()
===================================
System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataqlsach_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
Program Location:
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
SQL SERVER 2005. RESTORE DATABASE FROM ONE SERVER TO OTHER
May 29, 2008
Hello, I have two servers (both of them with sqlserver2005) :
SERVER3 with the
Database: ReportServer$SQLSERVER2005TFSTempDB
and SERVER5 with the Dabase: ReportServerTempDB.
I''ve got a backup of the database in SERVER3, and I want to restore it in the database of SERVER5. As you see, they have different names(I don't know if this is a problem....)
So I try doing the following:
RESTORE DATABASE [ReportServerTempDB]
FROM DISK = N'C:TFSReportServer$SQLSERVER2005TFSTempDB_backup_200805282200.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
MOVE 'ReportServer$SQLSERVER2005TFSTempDB' TO
'C:Archivos de programaMicrosoft SQL ServerMSSQL.1MSSQLDataReportServerTempDB.mdf',
MOVE 'ReportServer$SQLSERVER2005TFSTempDB_log' TO
'C:Archivos de programaMicrosoft SQL ServerMSSQL.1MSSQLDataReportServerTempDB_log.ldf',
GO
And I am getting the following error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'MOVE'.
Does anyone know why is throwing this error?
Thanks a lot!
Setting Admin Rights To Report Manager, SQL Server Express 2005 On Vista Business
Mar 7, 2007Hi all,
I'm a newbie to Reporting Services so please forgive my ignorance...
I've recently installed SQL Server Express 2005 with Reporting Services on my Vista Business Notebook (with no network connections) and have installed VS C# Express as well as the Business Intelligence Studio to create reports.
My C# application can connect to SQL Server fine, however, the reports I created with Business Studio refuses to deploy to the server due to the following reason:
"The permissions granted to user 'localhostJenny' are insufficient for
performing this operation".
I find this odd as I've been able to deploy reports on my other Windows XP machine (with SQL Server Express) no probs at all. Additionally, I cannot see the 'Site settings' link within Report Manager (Web) that I'd normally get with an admin account (i.e. on my Win. XP machine). Various sources indicate that my account has insufficient access rights to view this.
Having browsed through seemingly endless blogs and forums, I suspect that my local account has not been configured to access the Report Manager web client to publish, manage and administer the site.
I have tried the surface area tool for SQL Server, but I suspect that this is relevant only to SQL Server DB access and management. Adding an administrator here does nothing.
The question is, how can I grant access to myself (local account) to allow me to publish reports via business studio, and at the same time - administer Report Manager Site Settings - on my vista notebook??
I would be extremely grateful for any help on this matter - I've tried ceaseless reinstallations, but to no avail!
Many thanks in advance,
Jenny
SQL 2005 Database Restore
Jul 27, 2006In SQL 2000, When I restore a database backup called "A" as "B" the datafile name and log file will change automatically with the new restored database name , whereas in SQL 2005 the datafile name and log file name remains with backup database name.
Will this issue be addressed in next release of service pack.
How Can I Restore A SQL 2005 Database On My SQL 2000?
Aug 4, 2007My customer has SQL Server 2005. I have SQL Server 2000 on my laptop (on XP Pro). I believe I have an empty database the same as theirs, but I need to restore their data. They sent me a normal SQL Backup, but my laptop threw a fit and cannot work with it.
They are now trying to export the database to Microsoft Access and send me that, but I won't get that until next week now. However is there a better and easier solution for those of us with insufficient experience of this, please?
Many thanks,
Caspar
Restore A SQL 2005 Database On SQL 2000
Aug 13, 2006Hello,
If I have a SQL 2005 database set to SQL 2000 Compatiblity can I create a backup on SQL 2005 that can be restore on SQL 2000.
Michael
Restore (migrate) Database From MS SQLserver 2005
Nov 1, 2007can we restore (migrate) database from MS SQLserver 2005 to MS SQLserver 2000
Pls reply.
Restore Database April CTP 2005 Failed?
Jul 23, 2005The following failed in EM (or whatever it is called now managementstudio I think) and I scripted it out and ran it in a query window toget more info. This works fine in SQL 2000 but not in 2005. I also triedto 'create' the database first then ran this command below but with the'force restore over existing database' checked on but this failed alsowhich is odd as well. Any ideas. This is my very first play with 2005 soit is not off to a great start although to be fair, i seem to have gorlucky on the install as it all went OK. BTW, is there a more approriatenewsgroup for this query?ThanksRESTORE DATABASE [bob] FROM DISK = N'C:BigFujiCopiesBob.bak' WITHFILE = 1,MOVE N'bob_Data' TO N'c:Program FilesMicrosoft SQLServerMSSQLDataob_Data.MDF',MOVE N'bob_Log' TO N'c:Program FilesMicrosoft SQLServerMSSQLDataob_Log.LDF',NOUNLOAD, STATS = 10GOMsg 5122, Level 16, State 1, Line 1Directory lookup for the file "c:Program FilesMicrosoft SQLServerMSSQLDataob_Data.MDF"failed with the operating system error 3(error not found).Msg 3156, Level 16, State 3, Line 1File 'bob_Data' cannot be restored to 'c:Program FilesMicrosoft SQLServerMSSQLDataob_Data.MDF'.Use WITH MOVE to identify a valid location for the file.Msg 5122, Level 16, State 1, Line 1Directory lookup for the file "c:Program FilesMicrosoft SQLServerMSSQLDataob_Log.LDF" failed with the operating system error3(error not found).Msg 3156, Level 16, State 3, Line 1File 'bob_Log' cannot be restored to 'c:Program FilesMicrosoft SQLServerMSSQLDataob_Log.LDF'. Use WITH MOVE to identify a validlocation for the file.Msg 3119, Level 16, State 1, Line 1Problems were identified while planning for the RESTORE statement.Previous messages provide details.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedRESTORE DATABASE Without Full-text In SQL 2005
Nov 29, 2007Is it possible to do a full restore of a database without restoring the full-text catalogs? At the end of each month, we restore a copy of a database for quick access to month-end data with a different name. e.g. we restore the database DBN as DBN1107. Since upgrading to SQL 2005, the restore is failing because it it trying to restore the full-text catalog.
View 2 Replies View RelatedRestore Master Database With SQL 2005 Made An Error With Me
Mar 6, 2007- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBinn
then sqlservr.exe -m
- I entered that single user mode
---> Problem here:
- I tried to connect to sql by sqlcmd -E --->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
---> I tried to connect to management studio:
TITLE: Connect to Server
------------------------------
Cannot connect to BISHOYSERVER.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECTAdministrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
Restore Database From Evaluation Version Sqlserver 2005
Apr 3, 2008Hi
I have an sqlserver 2005 Enterprise edition (Evaluation version) installed on one of my servers.It got expired after 180days and now i want to transfer the database that was residing on that Evaluation version sqlserver to a new Development server.
Do we have any way to restore the database from a already expired Sqlserver trail version to a new Development server.
I dont want make an upgrade.
Please suggest me.
Regards
Arvind
Backup/Restore Vs Attach/Detach 2000 Database To 2005
Jul 15, 2006I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
Peter
Xp_cmdshell Execute Rights (2005)
Sep 11, 2006I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?
I solved it by checking "Control server" under "Properties" > "Securables"
for the login, but I don't actually want this login to have full control.
(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)
Install Visual Studio 2005 Rights Issue
Apr 28, 2006Hello-
I am trying to install VS2005 Standard Edition on W2k Sp4. This install fails on the first part when MDAC 2.8 Sp1 tries to install. I am logged on as ADMINISTRATOR with admin rights. I tried to install MDAC 2.8 sp1 alone with the same result. Can you help?
Vincent DeLuca
vdeluca7@centurytel.net
Retrieving Rights Of An User On All The Tables In A Database
Aug 8, 2001Hi,
How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.
SQL Security :: Login Rights In A Database With Frontend?
May 26, 2015When creating a frontend with a database behind that, how should i get the login rights for user working?
Can i create a user with a password in the database and assign it a role or how should i get that done?
SQL USER's ADMIN RIGHTS ON RESTORED DATABASE
Aug 22, 2007Thanks 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
Legacy DTS Package On SQL 2005 - Give User Rights To Save DTS Packages...
Oct 4, 2007We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
Thanks
SQL Server 2005 Restore Error!
Aug 18, 2006Hi Team,
I try to restore my local database using the backup from the live server. However I encountered this type of error:
====================================================================
System.Data.SqlClient.SqlError: The path 'C:Microsoft SQL ServerFullTextApplication_FullText' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive. (Microsoft.SqlServer.Smo)
====================================================================
It worked before I added a full-text search feature. Any idea?
Thanks in advance.
Sql Server 2005 Restore Problem
Jun 10, 2008Hi,I am using SQL Server 2005 Enterprise edition. I face a problem when I want to restore a database. DataDB.mdf is the database file, When i want restore it using Sql server management studio it show the following error.
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 'C:DatabaseDataDB.mdf' 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.3042&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------Any suggestions are welcome.Best Regards
Sql Server 2005 Backup && Restore
Dec 27, 2007Morning guys,
What is the equivalent to the taskpad on sql server 2005?
I want to be able to see when the last backup and restore was performed via transact sql ......
how would you go about that..... ?
what would I search... ?
Restore Failed In Sql Server 2005.
May 19, 2008Hi,
I am trying to restore a production copy. I have only the .bak file with me. While restoring, i m facing the following error. can u please help me... thanks in advance.
"MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file."
I have placed the data files(52GB) in D: drive(140GB free space) and transaction log file(100GB) in F: drive(465GB free space). Still i am facing this error...
-- Sudheer
Restore Fail In Sql Server 2005
Jan 8, 2007Posted - 09/07/2005 : 15:32:52
--------------------------------------------------------------------------------
Hi, i need help about restore a DB
I did a backup of a database using SQL Server Management Studio, but when i try to restore my database now, i get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'Athenas'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
What do i doing so bad??
Thanks for ur opinions and help.
Regards
------------------------------------------
seyha moth