Results 1 to 7 of 7
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067

    Read a sql server database outside of SQL Server


    Is there a way to be able to read a sql server database outside of sql server? We had a server crash last week with Our SQL Server installation. we discovered that the scheduled jobs about 20 were not backed up. We have the msdb files from the old server and tried changing the name of the files to attach them as a user database so we could try and build a script to recreate those jobs in our new installation of sql server. Unfortunately changing the file names did not change the actual database name and we were unable to attach the old msdb database. So I was wondering if there's a way we can access that database to at least print the jobs and their steps so they can be recreated in our new installation.?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access can connect to sql server via odbc and using either dao or ado methods. Not sure what you mean by msdb? do you mean the access mdb? also what is the purpose of changing names?

  3. #3
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    msdb is one of the SQL Server System Databases. It contains all of the information to run Scheduled SQL Server jobs. When our SQL Server crashed we found out that the msdb was not backed up properly. we did find that only the boot sector of that hard drive was damaged and we were able to recover the SQL Server Database files system and user. But we've been unable to restore the system databases to the rebuilt server. I need to be able to read the tables in the Sql Server msdb database 7 specific tables in order to recreate those jobs. My first thought was to attach that database as a user database by changing the name of the files. Doesn't work SQL Server still reads it as the MSDB system file no matter what I name it. Kind of thought that would happen but I was hopeful. So now I need to be able to read that database outside of SQL Server but no idea if it's even possible. The file name of all SQL Server Databases have the extension mdb and the transaction log has the extension ldb.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The file name of all SQL Server Databases have the extension mdb and the transaction log has the extension ldb.
    that sounds more like an access 2003 db or earlier. If it does relate to sql server it is outside my knowledge so I'm afraid I can't help. I can tell you that you can rename mdb files with any extension you like and it will still open as an mdb

    Your tag says you are using 2010 which can open mdb files. If you are actually using 2013 or later, some mdb files cannot be opened since support for this was removed.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by RayMilhon View Post
    msdb is one of the SQL Server System Databases. It contains all of the information to run Scheduled SQL Server jobs. When our SQL Server crashed we found out that the msdb was not backed up properly. we did find that only the boot sector of that hard drive was damaged and we were able to recover the SQL Server Database files system and user. But we've been unable to restore the system databases to the rebuilt server. I need to be able to read the tables in the Sql Server msdb database 7 specific tables in order to recreate those jobs. My first thought was to attach that database as a user database by changing the name of the files. Doesn't work SQL Server still reads it as the MSDB system file no matter what I name it. Kind of thought that would happen but I was hopeful. So now I need to be able to read that database outside of SQL Server but no idea if it's even possible. The file name of all SQL Server Databases have the extension mdb and the transaction log has the extension ldb.
    Not completely correct.
    Not sure which version of SQL Server you are using but in all versions I've used, the datafile suffix is .mdf and the transaction log is .ldf

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	22.6 KB 
ID:	40756
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    You're right it is mdf and ldf.

  7. #7
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    I got it solved. I installed a developer version of sql server on my laptop. Stopped all of the services moved the msdb to another folder. Copied the old msdb to where that was and restarted sql server. Created a crystal report that accessed the msdb sysjob tables and sent the report to pdf. I am now in the process of using that pdf to recreate the jobs in the new sql server. (by the way) I stopped sql server on the laptop and restored the correct msdb to it's all good. Only thing I have to do now is copy and paste from the pdf and recreate each job. It'll take a day or so but It'll get done. Next step is to ensure it doesn't happen again. Thanks for everyone's help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Upgrade from SQL Server 2008 to SQL Server 2014
    By RayMilhon in forum SQL Server
    Replies: 4
    Last Post: 07-12-2017, 04:54 PM
  2. Replies: 9
    Last Post: 04-20-2015, 10:12 AM
  3. Database stored on server read-only
    By GGCR in forum Access
    Replies: 2
    Last Post: 05-23-2014, 09:42 AM
  4. Replies: 0
    Last Post: 12-20-2012, 01:44 PM
  5. ODBC link from Access to SQL Server changes to read-only
    By Petr Danes in forum Import/Export Data
    Replies: 1
    Last Post: 06-08-2011, 09:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums