Reattaching Lost Databases - No Backups
Feb 9, 2000
What do you do when the database actually, physically exists, intact, but is not recognized by SQL Server 7 and there is no backup?
On a newly installed system, I created a new database and imported tables from another system. I also disbled SQL Server 7 services, rebooted and file-copied into the directory another database, that was not on a current SQL Server system, then restarted SQL Server.
SQL Server 7 failed to recognize any of the databases - master, model, etc., as well as the user databases. Neither database was specifically detached - allegedly a requirement for using sp_attach_db. No backups had been done - I was still setting up(?).
I know that there must be a way to reload, reattach or recreate a database when the database actually exists. In this case, we also lost master, etc., which may change the situation. Any advice would be appreciated.
View 2 Replies
ADVERTISEMENT
Nov 8, 2015
I recently installed a trial version of Sql12k on my laptop. Since 1st of November I've been creating tables without problems (15) and different schemas.I would like to emphasize that both mdf and ldf files are allocated in my USB3 portable disk. is that a problems for Sql12K? SSMS had stuck just for 20 sec or so. Then, system itself kill the task. A huge surprise when I restarted again... all the databases including the last one.. without objects. I mean, user databases...
Where are my logins? Where are my tables and schemas? Jesus christ.Sql logs inform the following (in all the moment my USB hardisk was properly connected)
11/08/2015 16:00:30,spid19s,Unknown,The operating system returned error 21(El dispositivo no está listo.) to SQL Server during a read at offset 0x000000 00140000 in file 'f:enriccopia_de_bbbb.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
[code]....
View 4 Replies
View Related
Nov 6, 2007
Ok guys, I do a regular daily ntbackup of my entire server. I thought I had sql backups going daily but I think a admin password change stopped that and well those backups are a LONG time ago. The ntbackup is just 1 lost half day.
Windows crashed and had to be reinstalled, so I have all the files in the database folder and what I've done so far was reinstall windows, sql, and all that stuff so now windows is back up and going. I used sql admin and created the database ncdsm. Then I took the folder from the backup of the database for ncdsm and copied it to the new sql install (after stopping the service) and then turned it back on, and it shows that the tables do not exist, but it shows the list of tables in red.
How can I fix this and get all my databases back to normal? Thanks.
View 8 Replies
View Related
Jan 22, 2004
Yesterday, at 7 am, I was able to connect to remote databases from Enterprise Manager. Then at 11 am, I started getting "General Network Error" and connections failed. This happened on all my remote databases, so the problem is not the remote server. (I did check.) I have Comcast Broadband cable access to the internet. I can still access the internet, email, and ftp servers, just not the sql port. I rebooted my entire system several times. Today, I took my computer to a friend's house, and was able to successfully connect to the remote databases fom Enterprise Manager, so the problem is not on my computer. (I did check for the sql worm, and downloaded the McAfee Fix program, but it didn't find anything, and confirmed that the Service Pack 3 fixes were in place.)
I also tried connecting directly to my cable modem, but that did not work, so it doesn't seem to be my router or firewall. I contacted Comcast and they are not blocking port 1433. I don't know what else to try. I don't think the problem is on my computer or the software.
Any Help would be greatly appreciated!
View 9 Replies
View Related
Jun 13, 2002
I have a test server where I was experiencing security/rights anomalies. I tried disabling the BUILTINAdministrators account, closed SEM, and when I reopened it, I could not access my Server (all databases,etc.are on local machine). I thought I could just use the SQL accounts (sa, etc.) to connect, but they all fail?? I tried creating a new registration, and that didn't work either. Since I deleted the old registration, and I cannot reconnect, what are my options(short of restoring image)? If I re-install SQL will all my databases still be there? Is there another way to get access back?? Any help would be appreciated.
rob
View 3 Replies
View Related
Feb 23, 2015
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies
View Related
Apr 20, 2015
I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?
Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?
Do you just check that the SQL Agent job that runs the backup was successful?
Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?
Or is there a better method?
View 1 Replies
View Related
May 5, 2015
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
View 7 Replies
View Related
Jan 29, 2002
Hi
I have tried to restore on db to another on different servers. Unfortunatly the sort orders dont match. Does anyone out there have any articles or know how to attach the the source db data and log files to the destination db. I would appreciate any assistance.
Thanks
D
View 1 Replies
View Related
Apr 27, 2006
I have just installed SQLExpress version 9.0.1399.
I was using an earlier version of the server before and backed up a copy of my mdf files for safety's sake. However when I now try and attach the old databases I get an error that the database files from the old non-release version (590) is not compatible.
Probably my own fault - the information was probably clearly on display deep in a file somewhere in the documentation (cough). However I would kind of like to get at the data in these files.
Is there any way of getting hold of an earlier version of SQLExpress which will handle these files? Or can they be opened in any other way?
View 3 Replies
View Related
Nov 30, 2000
Anybody know if it's possible to re-attach a readonly database without making it operational? I'm using log shipping to keep my hot standby current, but would like to take the detached db and duplicate it elsewhere, I just cant re-attach it as read-only again when I'm done. Thanks!
View 2 Replies
View Related
Oct 8, 2007
Since the "Cannot Open User Default Database (error 4064)" thread is closed, and I had this same error, I'd thought I'd leave the solution that worked for me since I spent about 2 hours researching this problem. After detaching my database, then reattaching it, I couldn't log back in with my login ID. I could get in using the sa account. I could also log in with my login ID if I set the default database to master using SQL Server Management Studio by navigating to Security, Logins, right-clicking my login ID, then setting the default database to master. However, this isn't a solution, because I need my login ID to default to the database I reattached, not master.
One way of resolving this is to set the new Login ID as the owner. Right click the database under Databases in Management Studio, choose Properties, choose Files, then set the owner with the new login ID. I can then connect to the reattached database with my login ID. If I set the owner back to sa, I can't connect with my login ID, I have to use sa. If someone has a solution to relink my new login ID and the user with the same name so I can log in with sa set as the owner, I'd love to hear it. I can't make that work.
BTW, if I go under Logins, right click my new login ID, choose properties, then User Mapping, my reattached database is selected under Users mapped to this login. However, the Users and Default Schema columns are both set to dbo.
--Steve
View 1 Replies
View Related
Nov 15, 2015
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Aug 19, 1999
I need help ASAP!
For some reason, we lost a dozen of our tables with valuable data. could be a virus or something that cleared out our data.
The last backup we have is last night at 11PM. If we recover from that backup, all the users lose all of today's work!
Is there a way I can just roll back the transactions from today?????!
I'd really appreciate any help urgently
Joyce
View 1 Replies
View Related
Nov 15, 2005
Hi all......well ill try and explain what I dont understand :-(
I am completely new to SQL. I have downloaded SQL Server Express 2005. I guess I need to download another program now like SQL Query Analyaer so that I can query a database - can anyone recommend a free download that will do the job?? I had a play around with one that I downloaded but to be honest, I have no idea on how to get the two programs talking.
Is someone able to guide me through how to initially set up SQl Server and how to get evrything working??
Much appreciated :-)
Gavsta
View 1 Replies
View Related
Jan 17, 2008
I get multiples of one record. i dont want multiples. but i need to have all the records that are the latest and i dont know how. can you help me.
thanks
SELECT TOP 1 [sn].[WrkstaId]
,[sn].[Computer Model]
,[sn].[Serial Number]
,[sn].[Asset Tag]
,[sn].[Computer Type]
,[id].[Name]
,[id].[Domain]
,[id].[OS Name]
,[id].[OS Type]
,[id].[Last Logon User]
,[id].[Last Logon Domain]
,[id].[Client Date]
,[pu].[_id]
,[pu].[Month]
,[pu].[User]
FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id
ON [sn].[WrkstaId]=[id].[WrkstaId]
INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Primary_User] pu
ON [sn].[WrkstaId]=[pu].[WrkstaId]
WHERE [sn].[WrkstaId]=(SELECT MAX([WrkstaId]) FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number]
WHERE [Serial Number]='68659')
ORDER BY [pu].[_id] DESC
68659Latitude D62028LGGD1Data not accessiblePortableWL-28LGGD1AZCORPMicrosoft Windows 2000Professionaluswclh04AZCORP2008-01-16 17:20:37.000864649Januaryuswclh04
68659Latitude D62028LGGD1Data not accessiblePortableWL-28LGGD1AZCORPMicrosoft Windows 2000Professionaluswclh04AZCORP2008-01-16 17:20:37.000864648Decemberuswclh04
View 7 Replies
View Related
Apr 11, 2008
After much hard work and effort I finally got SQL Server downloaded and opened. I am at a lost since the "Dummies" books, SQL & SQL Servier 2005 Reporting Services, I have don't (I can't find or understand) explain what I am trying to do. I have been able to create a couple of table, but can't see them. I can see the queries but not the actual table unless I run a query. How can I open the table it self?
Would to import an Access table in the SQLEXPRESS but can't figure out how. Can I get some guidence?
Any help I can get would be helpful. Thank you in advance.
CarLost48
View 5 Replies
View Related
Jun 8, 2007
Hello,I am using visual studio express edition. I making a nice website that uses the ASPNETDB.MDF for all those cool asp 2.0 membership and roles stuff.. This uses a SQL express database. OK so far. I have tried and tried to get my website live so I can further test it, but I can't People have been helpful, escpecially Bruce from this forum. I need more than help I guess. I really need to get this website up. Can someone please tell me where I can go to hire someone who will talk me through this process. I am not a programmer, I am a hobbyist. I understand SQL to select and update my database, but I can't do the stuff I read in FAQ's. It seems to be written for professionals. I have tried 3 different hosting services none seem to work. Please someone send me a private msg. we can work out terms. I am sure with someone over the phone that knows sql express and visual web developer expres will have me up and running in a jiffy. I can send payment via paypal.Jason
View 1 Replies
View Related
Sep 18, 2007
This procedure is missing one importent piece is I need to have a customerID added to the invoice table. The CustomerID comes from another table called Customer_Invoice_cart. I don't know how to convert Customer_Invoice_Cart.CustomerID to a @CustomerID to add to the table. If there is anyone out there who is good at stored procedures (unlike me who is learning) could help me out. FYI I realy cannot get the CustomerID for the .aspx page, it is not called any where.
procedure [dbo].[InvoiceAdd]( @EmployeeID int, @CartID nvarchar(50), @OrderDate datetime, @OrderID int OUTPUT)AS
BEGIN TRAN AddInvoice
/* Create the Order header */
INSERT INTO invoice( employee_number, invoice_date)VALUES( @EmployeeID, @OrderDate)
SELECT
@OrderID = @@Identity /* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO invoice_books( invoiceID, ProductID, TaxID, Quanatity, UnitCost, UnitPrice )
SELECT @OrderID, Inventory_Invoice_Cart.InventoryID, Inventory_Invoice_Cart.TaxID, Inventory_Invoice_Cart.Quanity, products.UnitCost, products.UnitPrice FROM Inventory_Invoice_Cart INNER JOIN products ON Inventory_Invoice_Cart.InventoryID = products.ProductID
WHERE Invoice_CartID = @CartID
INSERT INTO invoice_books( invoiceID, ServiceID, Service_TaxID, Service_Quanatity, ServiceCost)
SELECT @OrderID, Service_Invoice_Cart.ServiceID, Service_Invoice_Cart.TaxID, Service_Invoice_Cart.Quanity, services.price_leval1FROM Service_Invoice_Cart INNER JOIN services ON Service_Invoice_Cart.ServiceID = services.serviceID
WHERE Invoice_CartID = @CartID
/* Removal of items from user's shopping cart will happen on the business layer*/EXEC Customer_Invoice_Cart_Empty @CartIDEXEC Inventory_Invoice_Cart_Empty @CartIDEXEC Service_Invoice_Cart_Empty @CartID
COMMIT TRAN AddInvoice
View 15 Replies
View Related
Aug 18, 2004
I used sql 2000 create a data base at home call 'Mysite' several months ago, there are about eight tables and 30 stored procedures. When I run my asp.net program at home yesterday, and found can't connect to database. The error as follow: "Cannot open database requested in login 'Mysite'. Login fails. Login failed for user 'MINGYANG-MSONXHASPNET'. "
When I open the database, very surpriced to found that my database was suspect and not any item inside it. The suspect database include CommunityStarterKit. But Northwind and pubs database still there?
What happen? Last week I just download new Norton antivirus software, does have any relationship with it? Does data really gone? How to solve this problem?
Please help me if you have these experience.
View 2 Replies
View Related
Dec 31, 2005
What is the quivalent of EM for SQLExpress2005? With the old SQL 2000 I used EM to manage tables, SPs and such. Now, I cannot even find the databases - I installed the Club Starter Kit and I want to view and modify some tables. How do I do that?
View 3 Replies
View Related
Jun 10, 2006
Hello all let me first start out by saying I suck at SQL. I can do quite a bit with ASP.NET but SQL Server is on area that has haunted me and I have finally decided to bite the bullet and figure this behamouth out.Well here is my first problem, which is the biggest reason I can't seem to understand SQL Server or dataases in general.To have a realtional database you set up tables with foriegn keys and primary keys referenceing the id's like product table has a product name and one of the columns is a user_id displaying the id number of the user in the user table. From what I can tell you are supposed to set up the user_id in the products table a foriegn key to the Primary Key in the user table. I may be totally wrong on this though.Now my biggest question is how do you retrive this information the proper way to get it ready to be displayed in a gridveiw or a datalist? I have been reading SQL Server 2005 for developers and reading online tutorials and it seems like they say you need to set up a relationship and because of the realtionship you don't need to do a join, but I never hear how to use the relationship at all.Please someone help I am so totally lost that i feel like i will never understand. I have spent the last week trying to figure this out and I guess I am just googleing this wrong completely or i am incompentent one of the two.Thank you.
View 1 Replies
View Related
May 8, 2001
Hello all,
My company took over a project that another company was building. The other company switched all the passwords for the system and the database right before they left. No one has the passwords to the system or the database. Is there a way I can dump the data, or re-mount the drives into a different machine and get the data? Or am I just screwed? I have physical access to the machine and I can do anything needed I just need this data asap!!
TIA
Sean Silvius
View 3 Replies
View Related
Nov 27, 2000
Hi, maybe someone has some clues to the following situation. Suppose from a SQL 7 database, only the log .ldf files are lost. All datafiles are present. Suppose there is no backup of this database. SQL Server will put the database in the SUSPECT status. The following question is very interesting to me: Can one recover from this situation??? I ask this, because when datafiles are lost, it's obvious that you have lost data, but since the transaction log files are a "bit special", I just wonder how one can survive such a situation.
I know that IF a database has only ONE log file associated with it, and this one log file gets lost, SQL Server will create on startup a new log file. But with multiple log files, it does not behave in this sympathic manner. Is there perhaps any way to recover (for example, an undocumented dbcc command or so) ?
I surely will appreciate any tip or suggestion !! Thanks!!!
View 1 Replies
View Related
Oct 22, 2000
We lost all the data we had in a table. We restored the db and the transaction log up to a point in time, but could still not recover the table. We eventually only do db restore without the transaction log. We recovered the data in the table in question, but lost all the data entered that particular day. Does any body have a solution or suggestion that can help? I will be greatly appreciated.
View 2 Replies
View Related
Mar 19, 1999
Hi,
I face a different behaviour between SQL Server 6.5 and 7.0 regarding
the global variable @@identity.
I insert a row into a table with an identity column. This table has
an insert trigger attached which performs another insert into a second table.
Now I seem to lose the identity value (@@identity is NULL).
Example:
Take the following 2 tables:
create table a
(aaintnot nullidentity,
bbintnot null
)
create table b
(bintnot null
)
go
Table a has a trigger attached:
create trigger a_trigger
on a
for insert
as
insert b
select aa
from inserted
go
After this I execute the following SQL statement:
insert a (bb) values (1)
select @@identity
On 6.5 I get the result:
------------------------------------------
1
On 7.0 I get:
------------------------------------------
(null)
Which means that outside the trigger I have lost my identity value.
This feature of Version 6.5 I usually use to maintain
event logs. Using a trigger hides this maintenance nicely from the
application.
My question is now, is this a new feature of Version 7.0 or is it a bug?
BTW: The books online describe this behaviour in the case when the trigger
inserts value into a table with an identity property, which is not the case
in my example.
Regards
Marco
Marco Ruggli
ruggli_m@simultan.ch
Simultan P&I
Kantonsstrasse 1
6246 Altishofen, Switzerland
View 1 Replies
View Related
Aug 30, 1999
templog2.DAT and templog3.DAT were deleteed and now sql wont start. how do I recreate them with out a backup do I have any options? I will continue to monitor this site while I work on it. Please help.
View 1 Replies
View Related
Nov 7, 2007
I lost my datafile , that's corrupted and I don't have database backup. Any idea ?:(
View 1 Replies
View Related
Jul 14, 1998
Hi All,
I have lost the system administrator password for my test server which contains
inf. which I cannot afford to loose. The SQL server documents state that if
the password is lost , then a SQL re-installation will have to be done.
Is anyone aware of a way to reset the password for SA without reinstalling the
SQL server.
Matter is most URGENT.
Regards
Vedanth
View 5 Replies
View Related
Jun 18, 2006
Hey every one...I'm new here, and I feel rather bad just jumping in with out at least formally saying hi. However, I have a problem that I'm struggling with that I'm not finding an answer to via searches or through my library of books.
I'm sure it's basic, but I'm still new to the world of MS SQL, so please allow my naivety to MS SQL be forgiven.
I've been using MS SQL 2000 and 2005 on a Windows 2003 machine. However, I've been needing to be more mobile, I've been transferring some projects over to an XP pro lap top. There was a mild learning curve with .net with II5, because I've gotten spoiled with II6. However, that's fine.
It's just that when going through the procedure of attaching my databases from my Windows 2003 machine to my XP machine, I'm no longer able to log in, even though I've changed all the Connection string information.
On one database, it's not allowing me to use a stored procedure:
EXECUTE permission denied on object 'ViewSubscriptions', database 'la_market', schema 'dbo'
I know of general information concerning Owners of Databases and what not. However, when I tried to fiddle with granting dbo ownership (which i though DBO was just a general all purpose owner), it gave me other errors.
And on the other Database I'm getting absolutely no access to it what so ever (Once again, I've changed my connection string and everything)
Cannot open database "catalog" requested by the login. The login failed.
Login failed for user 'BUCKYCOREASPNET'.
Now, the problem isn't what I'm showing you here. Normally I have what it takes to find answers to these sort of problems. But with this one, I can't seem to phrase the problem correctly. So, advice would be wonderful. However, just phrasing the problem is good enough. So far I was told that my databases aren't mapped to my current Windows authentication. So, once again, just having some advice as to how to understand the problem will help. I may be new, but I'm enthusiastic and confident.
Thanks to any and all who may help...
View 1 Replies
View Related
Feb 6, 2006
I have lost LOG file, and I can not open database, (Error 5105) enterprise manager can not create database is it possible to not rewrite database ??
br
View 1 Replies
View Related
Feb 5, 2007
Hey everyone,
I've run into a fairly big problem. Apparently, the automatic backup has not been working since September, and that backup is of no use to me.
What I'm trying to do is restore the database without using the transaction log file. I don't care about a dirty database or lost data. The data is all I need, and the database structure of course.
I've tried several things.
Right click on the <database> go to tasks->attach-> pick MDF file etc.
Following this guide The only problem is I get a different error message. It doesn't ask a yes no question it just says the file doesn't exist and it can't create the database.
I've tried running it from other SQL management software and removing the LOG file when attaching, and that doesn't work. I tried:
Code:
MS SQL
EXEC sp_detach_db @dbname = <my db name>
EXEC sp_attach_single_file_db @dbname = <my db name>, @physname = <my db mdf file>
and I get the same message pretty much.
I tried this but I can't seem to get the database into emergency mode.
Am at the end of my rope and I really need this up and running ASAP.
Although I'm not sure of the proper syntax for setting a database into emergency mode, I think the last link I provided might do the trick.
I also tried the following:
http://www.spaceprogram.com/knowled...file-on_12.html
But when I get to step 8, the database is not in a suspect mode. When I click on it it says the database is in accessible.
When I right click and click on properties I get:
Quote:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Database 'BridgeTrak_Central2' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I checked the server and there is enough memory and disk space, about 2GB on each drive. Should be plenty for my 32MB database.
It's not my neck on the line but I would like to see my coworker stay here.
If anyone here knows anything about this, that would be great.
MS SQL 2000
Windows Server 2000 SP 4
_Any_ help or suggestions, other than the obvious, make sure backups are working, will be greatly appreciated....
Did I mention greatly appreciated?
I think the issue is that the original database was lost when someone else was trying what I'm trying now. They're very delete happy.
I think they may be SOL... glad it's not me, then again I wouldn't use the delete key without making 100% sure it wouldn't screw things up...I especially wouldn't empty the recycling bin. I probably would have deleted a database already burnt to CD, or burned some to CD....or made sure my backups were working. Messy messy network state, last guy left it in a mess. New network/DBA/ if it's got a network cable attached you're responsible. :S I don't like this new company that much. Too stingy... Dual P2 server?!?!?! Isn't that like, antiquated? No wonder there have been 3 dba/network admins/"if it's got a network cable..." that have left in the past year.
View 1 Replies
View Related
