6 Replies - 4605 Views - Last Post: 25 August 2012 - 01:32 AM

#1 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

SQL R2 md file to mdf file type

Posted 30 July 2012 - 01:00 AM

Good Morning People.

I have a issue and I don't know what to do.

I have backed up a database on my sql 2008 R2 server, but the file type goes to .md and not .mdf

The problem with this however is that the database have no SQL Server Primary Data File.

Do anyone know how to resolve this please. I don't have alout of experience in this so I don't know if this can be resolve.

Thanks
Thinus

Is This A Good Question/Topic? 0
  • +

Replies To: SQL R2 md file to mdf file type

#2 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 591
  • View blog
  • Posts: 1,318
  • Joined: 24-April 12

Re: SQL R2 md file to mdf file type

Posted 08 August 2012 - 01:27 PM

The file extensions in SQL Server are not strictly enforced. When you do a backup, you can get away with giving it pretty much any name and extention that you want. Normally, people like to name backup files something like MyBackup.BAK as a standard for full backups and maybe MyBackup.TRN for a transaction log backup. Perhaps your .md file is one of these. I assume you are doing proper SQL Server backups rather than just copying the .MDF file (this can be done, but it's really only done for specific purposes and not the normal method of doing a backup). The backups should only be done with either T-SQL or the backup wizards in SQL Server.

So, when you restore a proper SQL Server backup file it shouldn't matter what it's extension is as long as you point to that file to restore from and it's a full backup file. It should recreate the entire database including the .MDF file and others.

Moving a .MDF file is a much more complicated process and not really a backup.

The .MDF file is the primary data file. Additional database files will have a .NDF extension.
Was This Post Helpful? 1
  • +
  • -

#3 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: SQL R2 md file to mdf file type

Posted 16 August 2012 - 12:01 AM

Good Morning guys I think maybe I am taking a change here but hope that one of you maybe can help me with the following problem if this is possible in any way.

I wrote a vb.net application that connects to my sql 2008 R2 database like this:

Imports System.Data.SqlClient
Imports System.IO
Public Class Prep_Status
    [/Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")]
    Private Sub Prep_Status_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim currentFolder As String = Directory.GetCurrentDirectory()
        ' Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA")
        Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA")
        For Each FI As FileInfo In INF.GetFiles()
            Dim filenght As String = FI.Name.Length
            Dim sfilename As String = FI.Name
            sfilename = sfilename.Remove(filenght - 4)
            If FI.Name = sfilename + ".mdf" Then
                If sfilename = "master" Then
                ElseIf sfilename = "model" Then
                ElseIf sfilename = "msdb" Then
                ElseIf sfilename = "tempdb" Then
                ElseIf sfilename = "ReportServer" Then
                ElseIf sfilename = "ReportServerTempDB" Then
                ElseIf sfilename = "MSDBData" Then
                Else
                    cboxcompany.Items.Add(sfilename)

                End If
            End If
        Next



Connection string is this :
Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")



Now it works perfectly well and I can call all off my databases in my application now I was wondering can I connect my application to a server via VPN access through my desktop pc? IS this possible?

How it works currently I connect to VPN access and from there remotely connect to my server and do all my database admin through remote desktop.

If someone can help me or let me down easy and say it is not possible I can get some sleep for a change. Would be appreciated.

This post has been edited by Atli: 16 August 2012 - 09:29 AM
Reason for edit:: Fixed the [code] ... [/code] tags.

Was This Post Helpful? 0
  • +
  • -

#4 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 591
  • View blog
  • Posts: 1,318
  • Joined: 24-April 12

Re: SQL R2 md file to mdf file type

Posted 16 August 2012 - 10:59 AM

View PostThinus du Pisanie, on 16 August 2012 - 01:01 AM, said:

Good Morning guys I think maybe I am taking a change here but hope that one of you maybe can help me with the following problem if this is possible in any way.

I wrote a vb.net application that connects to my sql 2008 R2 database like this:

Imports System.Data.SqlClient
Imports System.IO
Public Class Prep_Status
    [/Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")]
    Private Sub Prep_Status_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim currentFolder As String = Directory.GetCurrentDirectory()
        ' Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA")
        Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA")
        For Each FI As FileInfo In INF.GetFiles()
            Dim filenght As String = FI.Name.Length
            Dim sfilename As String = FI.Name
            sfilename = sfilename.Remove(filenght - 4)
            If FI.Name = sfilename + ".mdf" Then
                If sfilename = "master" Then
                ElseIf sfilename = "model" Then
                ElseIf sfilename = "msdb" Then
                ElseIf sfilename = "tempdb" Then
                ElseIf sfilename = "ReportServer" Then
                ElseIf sfilename = "ReportServerTempDB" Then
                ElseIf sfilename = "MSDBData" Then
                Else
                    cboxcompany.Items.Add(sfilename)

                End If
            End If
        Next



Connection string is this :
Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")



Now it works perfectly well and I can call all off my databases in my application now I was wondering can I connect my application to a server via VPN access through my desktop pc? IS this possible?

How it works currently I connect to VPN access and from there remotely connect to my server and do all my database admin through remote desktop.

If someone can help me or let me down easy and say it is not possible I can get some sleep for a change. Would be appreciated.


Sure it's possible. All VPN does is put you on the network. It's the same as if you plugged the ethernet cable into the router there on the network, except you can be anywhere on the planet and do it across the Internet rather than through the router.

So, your app will work the same as if you are there at that location plugged into the network. It won't work any differently.

All VPN is is a network connection over the Internet rather than through the network router. It's big selling point is that it's an encrypted connection going across the Internet, so that people can't spy on what you're doing (important since you're just going across the public Internet). But otherwise, it's just a network connection. So, nothing works differently.


I see in your connection string that the client and the server are on the same box. In a network situation, you probably want the server on a remote box, which means your connection string has to change to point to it.

And SQL Server is just going to use a normal TCP/IP connection on Port 1433 (by default). So as long as a firewall doesn't prevent it, anything can connect to the SQL Server including your app.

This post has been edited by BBeck: 16 August 2012 - 11:02 AM

Was This Post Helpful? 0
  • +
  • -

#5 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: SQL R2 md file to mdf file type

Posted 23 August 2012 - 05:41 AM

Nice and thank you now can you please help me understand how this is possible with my connection to vpn I am being connected to the network witch is fine.

Then I connect with remote connection to my server.

Now how do I compile this string to connect to the server....

Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")


My Server login details is also localhost with no password.

Do I put in username and password in the string to tell him to connect to the remote server somewhere?

And this string isn't goiing to change because the path is exactly the same on my server site?

Dim INF As New DirectoryInfo("C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA")


Sorry for the punctuation I am really exited to try this but have no Idea How..
Was This Post Helpful? 0
  • +
  • -

#6 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 591
  • View blog
  • Posts: 1,318
  • Joined: 24-April 12

Re: SQL R2 md file to mdf file type

Posted 23 August 2012 - 11:34 AM

So, my understanding is that you are running the VB app on your client/workstation which is VPN'ed into the network. You have a SQL Server running on a server on that network. You can Remote Desktop into that server.

So, the client (VB app) isn't running on the server; it's running on your local machine. So, you don't need Remote Desktop for using the client.

But you're wanting to connect the client/VB app to the SQL Server on the network. Assuming that there are no firewall issues and that you've got a good VPN connection, it should be fairly straight forward.

Probably the first thing is to make sure you have connectivity to the server.

I would start by setting up an ODBC Data Source whether you intend to actually use it or not, because it will allow you to verify connectivity.

Depending on what version of Windows you are running, you should be able to find ODBC in Control Panel, System and Security, Administrative Tools, Data Sources (ODBC).


On the User DSN tab click the Add button. Scroll all the way to the bottom of the list and select "SQL Server". Next click the Finish button.

This should give you the "Create a New Data Source to SQL Server" dialog box. In the Name edit box you can enter a name that you would like to refer to this Data Source by (from your app or Excel or anywhere). Description is just for you, so you can leave it blank.

For the server edit box you have to fill in the server name, or you may be able to fill in the IP address here. Then you should be able to hit the Next button.

The way you answer "How should SQL Server verify the authenticity of the login ID?" is kind of a big decision. Assuming that you have a Domain Server, a Domain Account, and have verified that you can login directly to the SQL Server using that account, then you can leave the default selection of "NT Authentication" there. After that you can hit the Next button. At that point it will probably fail if there's a problem.

If you select the "Change the default database to" check box, you should get a drop down list of all databases your account has access to. If you're not getting this list, something's wrong and you need to get that something corrected.


If you get the list then you have a valid connection to the server from that box. Select the database you want this to default to and hit the Next button. Then you can just hit the "Finish" button.

Click on the "Test Data Source" button here just for good measure. It should say "TESTS COMPLETED SUCCESSFULLY!" But, honestly you would not likely have gotten half this far if it was going to fail.

Anyway, you can use that ODBC DSN to connect in your connection string if you like, but you can use several other methods to connect as well.

But if you can't make that ODBC DSN work correctly, you're probably not going to be able to get any connection to work.

Since you are saying that you are able to Remote Desktop into the server, you must know the correct server name. I assume we are on a Domain here with a Domain Name.

LocalHost means the "same box". So, if the app and the server are on two different boxes, LocalHost will never be correct.
Dim sqlcon As New SqlConnection("Data Source=localhost;Integrated Security=True")



You can get the connection string there from ConnectionStrings.com.

http://connectionstr...server-2008#p21

I think the primary change is going to be to change localhost to the host name or IP address of your server. (If you've changed the listening port, or if this is not a default instance of SQL Server it may be a little more complicated. I think SQL Express installs differently, as a named instance.) You "may" also have to add the "Initial Catalog=" to point to your database, but it will probably default to your default database set on the server without it.

You should not need Remote Desktop for any of this. If the app is actually running on the server, that would be different. But in that case you would get it running on the server without VPN or Remote Desktop and then it would just "work" through VPN and Remote Desktop.

This post has been edited by BBeck: 23 August 2012 - 11:36 AM

Was This Post Helpful? 0
  • +
  • -

#7 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: SQL R2 md file to mdf file type

Posted 25 August 2012 - 01:32 AM

Ey everything went well I just can't configure the connection string to the server I am a little dumb on the topic..

http://connectionstr...server-2008#p21

The username and password in these connection strings what username and password are they asking me to use.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1