July 10, 2009 at 1:47 pm
Hi All,
I have a 26MB database that used to contain tables and SProcs and more, which after a reformat of the server attaches successfully, but contains no tables, SProcs, or other data.
Some backstory: I have an SQL server 2008 database that I upgraded from sql 2005 not too long ago. One of the Windows Updates borked my registry permissions on CLSID and I had to reformat the machine. I booted from a Ubuntu 9.04 CD and copied all of the databases and files from that machine onto a USB flash drive, and reformatted the server, since I wasn't sure of the problem at that time. It could have been a virus for all I knew... once I had the same issue on my laptop, I knew how to fix it and then wished I had never reformatted.
I reinstalled Windows 2003 Server on the box, reinstalled all my programs (VS2008, SQL2008, etc). I attached all 5 of my databases on this server. All of the databases save the one in question work fine - all data and procedures are intact. The "truncated" DB's file size is 26MB, and its log is 3MB. Running a DBCC CHECKDB returns no problems. Selecting all tables using SELECT * FROM sys.tables returns no records. There are no SPs, built-in or not.
I don't care about the data in this table as much as the functions, SPs, table structures and indexes. Am I screwed, or can some of this information be retrieved?
No, I didn't make backups *facepalm*
- Derreck
July 10, 2009 at 1:51 pm
You sound screwed to me. Do you still have the database on the USB drive? What happens if you use that and attach it on another SQL Server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 2:02 pm
I have to agree with Jack - you are pretty much screwed. My guess is that you copied the mdf/ldf files for that database from a particular directory, and the actual databases mdf/ldf files were in a different directory.
I have seen this in a lot of cases where the database is moved to another directory and the old files are never deleted after the move. Now, if those database files were put on a SAN or a different drive, you would be okay - but it does not sound like it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2009 at 2:12 pm
You're all probably right - I am most likely screwed - but the file size is 26MB, so I would think that to get to 26MB it would have to have been used. This was the correct DB copied over, it was in a specific folder.
July 10, 2009 at 2:21 pm
Could it possibly be a permissions issue? Are you logging into SQL Server with a privileged account or a user account?
Try running sp_change_users_login with the report option and see if you have any orphaned users. If you do, maybe those objects were created in that users schema?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2009 at 2:35 pm
Jeffrey Williams (7/10/2009)
Could it possibly be a permissions issue? Are you logging into SQL Server with a privileged account or a user account?Try running sp_change_users_login with the report option and see if you have any orphaned users. If you do, maybe those objects were created in that users schema?
It's a brand-new SQL Server 2008 install on a freshly-wiped box. I'm logged in as Administrator using NTLM auth.
I tried to reattach the DB as SA, no dice. When I ran EXEC sp_change_users_login 'report', it returned the user ID that was associated with the database before the reformat. This user does not exist in my current SQL server install.
edit: engrish and addition
July 10, 2009 at 2:43 pm
derreckdean (7/10/2009)It's a brand-new SQL Server 2008 install on a freshly-wiped box. I'm logged in as Administrator using NTLM auth.
I tried to reattach the DB as SA, no dice. When I ran EXEC sp_change_users_login 'report', it returned the user ID that was associated with the database before the reformat. This user does not exist in my current SQL server install.
edit: engrish and addition
So, you have an orphaned user in the database. You should now run sp_change_users_login with the Auto_Fix option (look up the exact syntax in BOL) to recreate the login for that user.
I don't know if this will fix the issue - but, it could very well be that the objects owned by that users are not accessible or visible to you because that user does not exist. Just a guess...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2009 at 3:00 pm
I did what you said but still nothing. I think I am screwed. I'm just happy that this isn't something I am making money on right now. Thanks for all your time and help!
- Derreck Dean
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply