SQL Backups, how?

[Ladyborg]

Hi,

I'm new to SQL, I took over a site with an Access database which was upgraded to SQL. How do I back the SQL db up so that the information isn't lost? Apparently, once before (last webmaster) lost it entirely and it had to be re-entered - about 400 records. So now the owner wants to move to a less efficient way of keeping information, HTML.

Any input would be appreciated. But I REALLY need to know how to get to the SQL db and back it up regularly.

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

If your SQL Server is a full copy that includes the Enterprise Manager, then you can use it to set up a "Database Maintenance Plan" to back up your data on a regular schedule. Enterprise Manager has a wizard in the Management section of a server to help you set up the maintenance plan.

If your SQL Server is the Desktop Engine (MSDE), then you can use the command line utility OSQL.EXE along with some SQL scripts and a Batch file to back up your data. The OSQL program usually is installed in "C:\Program Files\Microsoft SQL Server\Tools\Binn." To get a display of the command line parameters for OSQL.EXE, go to a command prompt, change to the SQL Server Tools, Binn directory, and type OSQL.EXE /? for the listing. One of the parameters to pass to OSQL.EXE is the name of an input file that contains the SQL script to run. This SQL script would include the "BACKUP DATABASE" statement that applies in your situation.

 

[Ladyborg]

Ok, you're way over my head !!!!

I'm just trying to log in to a client's sql server on another computer with my Access by going to File > Import.

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

The easiest way to copy SQL tables to a Jet MDB is to use the SQL Server Data Transformation Service (DTS). But that utility may not be available on your client computer. If you want to just use Access, then the simplest route is to first set up an ODBC Data Source (System DSN tab; SQL Server ODBC Driver) on the client computer for the SQL Server database that you want to use. You can do this in the Windows Control Panel using the ODBC Data Source Administrator (32-bit). Then, in Access, when you "Get External Data" to import tables, change the "Files of type" dropdown list to select "ODBC databases()" and pick the ODBC Data Source that you made from the "Machine Data Source" tab.

 

[Ladyborg]

Thanks for the help. I did all that, and got the following message:

"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database".

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

It sounds like you selected the "Microsoft Access Driver (*.mdb)" for your ODBC Data Source instead of the "SQL Server" driver. The message you described occurs when you try to use ODBC to link to tables in another Microsoft Access database rather than a SQL Server database.

 

[Ladyborg]

You were right, I did. However, am getting error screen even with connection set up correctly:

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

It sounds like the TCP/IP port number is missing or wrongly entered on your configuration.

On the screen that you show in your message here, click the "Client Configuration" button and check the following items:

1) Your SQL Server name should be entered in the "Server Alias" text box.
2) In the Network Libraries section, "TCP/IP" should be selected.
3) In the Connection Parameters section, your SQL Server name should be entered in the "Server name" text box, and the check box for "Dynamically configure port" should be checked, unless your network has specific port reassignments that you need to use. (The default port number is 1433. You could try entering that number if needed.) If you must use a specific port number, then uncheck the check box and enter the port number as needed.

If this still doesn't work, then perhaps TCP/IP has been disabled by the software installer or the network administrator as a SQL Server connection method. In that case, you could try using the "Named Pipes" network library in the Client Configuration. "Named Pipes" are used by SQL Server for NT Trusted Connections. You would enter your SQL Server name in the "Server Name" and "Pipe Name" text boxes in that case.

 

[Ladyborg]

OK - first of all, let me say how much I appreciate your help!

Stupid question: How do know the server name?

= feeling stupid

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

On the screen just before the one you show above (this would be the first screen of the ODBC DSN Configuration wizard), there should have been a dropdown combo box that prompted you for "Which SQL Server do you want to connect to?". Any available SQL Server on your network is listed there for you to select. I can't help you with specific names because those are unique to your network.

 

[Ladyborg]

Ok, I think we're getting somewhere \ I have nothing there except my feeble attempts at connecting to either the domain or the dsn. Should it be something like that or a connection string like Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\dbname.mdb;User Id=admin;Password=pass; ???

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]

 

[jfischer]

No, the items that appear in the list of SQL Servers will look something like the following:

MyNTServerName
MyNTServerName\Acctg

These are named "instances" of SQL Server installations. The first instance is the default instance and it carries the machine name of the server on which it's installed. The second instance is another copy of SQL Server installed on the same server machine with a specific name of "Acctg" assigned to it.

If you do not see any SQL Server instances listed in the combo box, then you have other network/security related problems going on that will need to be addressed before you can set up an ODBC Data Source. First of all, make sure that SQL Server is actually running on the server machine. What you'll need to do is go to (or talk to someone at) the server machine and open the SQL Server Service Manager (system tray icon or Start, Programs, Microsoft SQL Server folder) to determine the SQL Server instance name(s) and whether or not the services are started. Write down the instance name you need. Start the services if they're not already started. Then try your ODBC DSN SQL Server Name combo box again. The named instances should be there now. If not, try hand typing the instance name that you wrote down earlier into the combo box. If you still cannot connect to the SQL Server, you'll need to get your network administrator involved.

 

[Ladyborg]

I got an email from the server telling me he would send the ip address of the sql server. Hopefully that'll do it, because I don't have it.

I'll let you know how it goes.

THANKS AGAIN FOR ALL YOUR HELP!

Ladyborg
"Many of life's failures are people who did not realize how close they were to success when they gave up." [Thomas A. Edison]