I am in a big trouble ,
accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.
Please help me , how can i recover the database ....
thanks in advance
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedAny ideas on how long it should take SQL Server 6.5 running on a 8 Gig RAID 0 array to "recover" a 4 GIG database. I rebooted the machine this morning (seldom ever have any problems with it) and it seems to be taking an awfully long time (15 min so far). I'll let you know what happens in any case.
View 2 Replies View RelatedI need to recover a database, but unfortunatelly i don´t have any backup... Well, it was not my fault. Anyway, i only have the physical .DAT files of the data and log devices of the database.
Will it work if I create two devices with the same name as the old devices, create a database with the same name as the old database within these devices and then run a full script of my old database, then change the 'phy_name' on the sysdevices to point to where my old dats were, or replacing the current ones with the old ones? If not, will anything else work?
Thanks in advance.
My server crashed i had full back up my .BAK file for every database and i reinstalled a fresh verion of sql server 2000 enterprise and when i comme in enterprise manger to restor the master its telling me i need to be in single user mode what do i do.
View 2 Replies View Related1) How do I reinstall a fresh copy of the MSDB database (since my current
one is corrupt). Is there any script available?
2) What is the purpose of the MSDB database? Backups and jobs/ Wizards?
Thanks for your help.
Ziggy
I have a 3GB DB that I was running an update script on. The sript failed and ran out of transaction log space. I expanded it and stopped and restarted SQL. The user DB is now marked as recovering. How long should I wait for it to recover? I don't want to have to restore again and start all over with my script. Please help.
(By the way this is a restored DB)
Thanks.
LC
I accidentally deleted at SQL 2005 database. Is there any way I can get it back? No, there is no backup.
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master]
go
create database [test]
go
USE [test]
GO
CREATE ROLE [ReadOnlyRole]
GO
USE [test]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO
drop role [ReadOnlyRole]
go
Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following.
"the selected user cannot be dropped because the user owns objects"
why? I never encountered this b4 ? I restored DB again but problem still the same. Any ideas?
What should I have done? Is there anything that can be done other than restoring from backup?
How does one know if the database is really recovering or is EM just joken? I can wait 2 hours
before starting the restore
I was BCPing 12 million rows into a staging table. II used the '-b' option every 20K which I thought
would do a commit and clear the log in batches. After the process EM appeared to show the transaction log
as empty. Upon inspecting the Bcp output file I discovered the message that the BCP did not complete
because syslogs was full. I could not do a truncate transaction log or a dump database. I tried to
do a truncate transaction with no_log and it appeared to just hang. I stopped the SQL Server thinking
I could dump the transaction log, but could not start the Sql Server again. I then stopped the NT Server
because 'if all else fails'. The SQL Server started but the user database if marked as recovering.
We currently have a company program in foxpro which inputs all of our information into its database. We are trying to shift from this to new updated software and a SQL database. There is a program we created which converts all the data from our old database into our new SQL database in preparation for our final move to the newer software. However, some of the tables in SQL were not taken directly from our old database but rather added in directly after our initial use of SQL, so when we ran our conversion software, a couple of specific tables were wiped because there was no equivilant within our old database. We already altered our conversion program to not touch those tables again, but is there any way to do a roll back or to find a backup of specific tables?
View 4 Replies View Related
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
My sql server is setup to use windows authentication. From time to time one of the applications on that server would have issues connecting to that server. I changed it to use sql authentication and all of a sudden all the problems went away.
View 8 Replies View Relatedwhile recovering it prompts a message 'the system administrator must have exclusive use of database to run the restore operation'.
now,how to do it?
how to make database to use exclusively when it is connected to an web based aplication.
thanks.
A friend of mine called and said that his database was suspect and he detached it and was unable to attach it. The SQL Server Version is 2000 with the latest service packs installed. When I checked it the database (MDF) size was 1MB and the LDF size was 3.8 GB. I was not able to attach the two together and I was not able to attach the database using sp_attach_single_file_db. I did find an old backup of his database and attached that so he can work off of it but it is 1 yr old and he needs recent data. Since the MDF seemed to be blank we cant do much with that, but there seems to be data in the LDF. Is it possible to extract any data from the LDF file?
Sunny
Hello,
I was coping database from my sql server 2005 to the same server but with different name. 4 steps completed successfully but the last step failed because the computer on which sql server is running turned off unexpectedly. When the server was running again, the source database was detached. I tried to attached it but sql server is reporting error 5 (system cannot access the specified file) I cannot access the file from windows explorer too. I discovered that the copy of the mdf file exists with the name of the target database but when I try to attach this new mdf file sql server reports that the file is not primary database file. Both files are the same size. What can I do? Thanks
I am by no means a db administrator, so I apologize for my ignorance. However I do know that I screwed up and am unable to find the solution...
The small company I work for has a development SQL Server where we do testing and development for web based applications. While using SQL Server Enteprise Manager I accidentally dropped the user's default default database.
I have always been able to access the enteprise manager via windows authentication, but now I am unable to. Is there any way to set the default database back to 'master'? Currently I cannot connect with SQL Query Analyzer for the same reasons, and do not have any other access except via windows authentication. Is there any hope? Any help is much appreciated.
URL>..n how to deploy (AKA publish) without dropping the users with the DropObjectsNotInSource enabled. With the SSDT September 2012 release I have been testing I seem to be able to ignore permissions and role memberships but not users.In my situation and that of several other folks I have talked to it is a problem trying to manage this. I am thinking of scenarios where there could be (1..n) databases needing the current build (where n is big and not a static number) and need to be updated but the database Users are different for several reasons.
don't have to manually use schema compare to identify objects to drop when we deploy and not drop database users at the target? Because of the dynamic nature of these databases (i.e. 1-n might be on a different release than the current build) we would like to manage security at the source and not the project or post deployment script level.From the MSFT folks is there any hope that we will get a parameter to ignore users when DropObjects otInSource=true in an soon to be released version of SSDT?
database : MS SQL server ver 6.50.201
problem: server startup / server time out
details
SQL server shows usage of 100 % CPU utilization, if started automatically / manually. and does not proceed / hangs up(server time out), but win nt operates extremely slow.
The background of the situation in brief is as stated below.
The errorlog was found to have the messages
warning : OPEN OBJECTS parameter may be too low.
Attempt was made to free up descriptors in localdes()
Run sp_configure to increase parameter value.
Error : 644, severity : 21, state; state 1
the non clustered leaf row entry for page 2 row 1 was not found in index page 40 indexid 2 database 'tempdb'
Error : 2620 severity 21 state 3
the offset of the row number at offset 32 does not match the entry in the offset table of the follwoing page : page pointer = 0x1395800, page no= 40, status = 0x2, objectid = 1, indexid = 2
Action taken
I used the sp_option to increase the open objects from default 500 to 1000, and the LE threshold maximum value to 400 from the default 200.
after which I used reconfigure go and restarted the computer to take the effect. The following did work fine and server was working ok. But then from yesterday I am having the problem that the MS SQL sever is utilizing the cpu to 98 - 99 % and I am not able to connect to the server.
I tried to start the server with the minimal configuration by specifying the -f option in the service manager start up options, it showed the following message
00/12/07 10:40:49.73 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
00/12/07 10:40:50.02 ods Unable to connect. The maximum number of '5' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
afterwhich I tried with the option of starting the sql server with the option -c -f
which in the event detail of the win nt shows
"mesg 18109: Recovery dbid 6 ckpt(55813,8) oldest tras= (55813,0)"
The open procedure for service "MSSQLServer" in DLL "SQLCTR60.DLL" has taken longer than the established wait time to complete. The wait time in milliseconds is shown in the data.
DB-LIBRARY error - SQL Server connection timed out.
and in the errorlog present in MSSQLLOG directory
00/12/07 15:06:43.24 spid1 Recovering Database 'master'
00/12/07 15:06:43.31 spid1 Recovery dbid 1 ckpt (7944,28) oldest tran=(7944,0)
00/12/07 15:06:43.41 spid1 1 transactions rolled forward
00/12/07 15:06:43.49 spid1 Activating disk 'AM'
00/12/07 15:06:43.49 kernel initializing virtual device 1, D:MSSQLDATAAM.DAT
00/12/07 15:06:43.50 spid1 Activating disk 'AMLOG'
00/12/07 15:06:43.50 kernel initializing virtual device 2, D:MSSQLDATAAMLOG.DAT
The dbid is / was 6 for both the instances, the database which is having this dbid is database name "AM" and is having above 1300 tables (approx)
what could be the problem and solution for it?
kindly help me out of this situation.
If any more information is needed please contact me on devendrakulkarni@yahoo.com / devendra@me.iitb.ernet.in
Regards
Devendra
Hi
I am in a trouble without doing any mistake. I restarted while in parallely my sql server was restarting i.e I opened a management studio.
When it booted up next time. I see that my database is gone. Though it is showed up in the server but has no node below it.
I then checked the mdf files and ldf files and found mdf file to be of 0 KB and ldf file is of 1.73 GB. My database was in full recovery mode but i did not take any database backup.
Here ais the entry from my SQL server log:
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAHello.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I wonder if now there is a case to recover my database..Can I restore/recover the database from .ldf file as it was in full recovery model?Any help i shighly appreciated.
Can anyone give me assistance on trying to restore my master file. I purposely corrupted my dabase on my test box so that I could work on my recovery process. However, during one of the steps I used the:
restore database Master From
disk= 'c:mssql7ackupmaster.bak'
Command. However, I continue to recieve:
Server:Msg3201, Level 16, State 2, Line 1
'Cannont open backup device 'c:mssql7ackupmaster.bak. Device error or device off-line. See the SQL Server error log for more details.
Note I am in single user mode and I have not been able to find anything specific in the error log file.
Can some tell me what possible could be the problem. Thanks.
is it possible to recover an existing SQL server? Here is what happened. WNT server 4.0 was corrupted, had no backup, repair did not work. consequently, i had to reformat the C drive and do a clean install of the OS.
my SQL files and user databases were on other drives. can i do a install of SQL server and tell is were the SQL server files (ie., the MSSQLDatamaster.db) are and have it use those files? given that this problem occurred as i tried to backup my SQL server database.
hope you can help or my butt is burned!
We are having major hardware replaced on our W2000 Adv Server and we
need to be prepared to restore the entire system.
If the hardware fix goes badly, worst case, the plan is to
rebuild the server and then REINSTALL SS7 and
then restore all the databases from tape backups.
My question is, which database(s) should we restore first?
Should we restore master and then proceed to restore the user
databases or restore the user databases first and restore master last?
Thank you in advance.
Help !!!!
How would you fix these two problems.
1. On the window of Enterprise Manger and under the databases, the database shows one of the databases like this manag_dat(recovering)rather than manag_dat. (manag_dat is the name of the database). What could cause the probem and how to fix it?
2. Same position as above, but instead it shows manag_dat (loading). I tried to delete its device and the database. it hang. How would it happen and how to fix it?
Thank you ahead of time
Any ideas how I can recover a job that has been deleted (?) from SQL7.0 ? We had one which was used on a regular basis which has nowceased to exists. I've spoken to the people responsible for the serverand was met by blank faces (as usual). They have no idea when it wasdeleted, by whom or when. To make matters worse, they don't think theycan get it off the backup set without a major bit of work to theserver. Suprisingly, none of the users noticed that things had stoppedworking.Unfortunately, I needed to do some work on this in preparation for mymonthly meeting with my boss on Monday. Whilst I'm not to blame, I'dlike to understand a little more about any options available to us forrecovery as he will undoubtedly ask when he finds out.I have an old back up on another server, so it's not the end of theworld re-creating it, but I was under the impression it was backed upproperly. Is this normal or have they missed something ?Are there any ways I can recover this ? What can I get done to thebackup to make sure this is backed up properly ?Thanks in advance.Ryan
View 1 Replies View RelatedHi,
I am working on SQL Server Business Intelligence Development Studio - 2005. While working on the same, the studio got closed automatically and I was not able to save my existing file.
When I try to open the last saved file, it is thowing the following error:
Deserialization failed: The element 'QueryParameter' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Value ##other:*'. Line 277, position 12.
EDIT CODE
Now I am not able to open the file. Please let me know how can I recover the lost file.
Regards
Syed
I saw the other thread that is related, but let me give you our circumstances. A drive failed and that drive had the SQL installation and the *.ldf files on it. The MDF files were unscathed.
The drive has been rebuilt and I have reinstalled SQL 2000 SP3A on there (that is what was there before). I have tried attaching the MDF through the GUI and letting it create the log for me. This failed.
I tried sp_attach_single_file_db, but this failed since the db was never dettached.
I tried creating the db again, dettaching it, copying the old (good) MDF file over the newly created one and then attaching (using multiple attach approaches). It knows that the log file and mdf are not matched and won't let me do the attach.
I also found DBCC REBUILD_LOG, but the article in SQL Mag supposes that your instance is still in working order and that there is an entry for the db in sysdatabases. Such is not true in my case.
Can you help? We are trying to get old backup tapes shipped to the site, looking up our Gold Partner contact info, etc., but in the meantime, I am trying to get the data restored even if we lose some transactions.
Thanks for any help!
Is it possible at all to remove the SA login or deny "System Administrator" from it by loggin as another user who has sa previledges
thanks
Our database -SQL Server 7.0 sp1 (NT 4.0 sp5)- is growing at a very fast rate despite the fact that we are deleting old record. It doesn't seem to be recovering disk space for the deleted records. Please let me know if there is a specific setting that can help us recover disk space. )
View 1 Replies View RelatedHi,
Bit of a panic here.. Someone accidentally deleted a database device (and
consequentially the associated databases) from MS Sequel 6.5. I don't have a
backup dump of the databases.
Does anyone know how I restore the device? I have the DAT file and I can
see the data in it if I use a text editor.
Thanks
Jonathan
Is there any way to recreate SQL2k cluster recources after someone removed them, which is is quite easy to do in Clustermanager?
We are running testscenarios at the moment with SQL2000Adv on Win2kadv servers. We assume a situation where someone at a client site accidentaly removes one or more of the SQL cluster receources in clustermanager. We can easily re-create the recources manually, but they will not come online. Of course it is always possible to re-install SQL2k on the nodes, but after "accidentaly" removing recources even that seems to be a pain, as the sql2k installer will not uninstall properly or reinstall/overwrite. Usually we get both nodes back up and running after running the uninstaller, cleaning up the registry and reinstalling SQL2k with the previous virtual (sql)server name and all the previous settings.
There must be a more efficient and reliable way to recover from this type of "usererror", but neither technet nor any other site seems to mention anything on a situation like this.
Any thoughts?
Alexander
Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.