Resore .bak to SQL Express

Our vendor has provided us a copy of our data in a .bak files from SQL server. I want to run reports against this data. I have installed Express as well as Server management studio.

I presume I need to restore the .BAK file before I can run any reports? If so how does one do this?

Thanks in advance

In the UI right click on a database and select restore, or review the restore command in Books On Line;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

Also here is a short example;

RESTORE DATABASE MyNwind
FROM MyNwind_1

Re-set identity field

Hi:

I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?

Can you assist?

Thanks

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

Requirement to use Management Studio

What are the requirements to use Management Studio/Management Studio Express for local instances? Are the requirements same for both studios and same for all OS?

Based on my own testings:
Remote connection is required for Management Studio in Windows XP SP2.
But it is not required in Management Studio Express in Windows XP SP2.

For Windows 2003 and 2000, remote connection is not required for Management Studio. Have not tried Management Studio Express yet.

Peter

This is additional info to clarify the issues:

I'm testing the following in a Windows XP SP2 with SQL Server 2005 Developer Edition SP1:

Scenario #1
1. Checked Local connections only in SSSAC for a SQL Server 2005 local instance
2. Restarted that instance in SSCM (Note: only Shared Memory enabled for Network Configuration)
3. Tried to connect to that instance in SSMS and get this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

Scenario #2
1. Checked Local and remote connections and Using named pipes only in SSSAC for a SQL Server 2005 local instance
2. Restarted that instance in SSCM (Note: Shared Memory and Named Pipes enabled for Network Configuration)
3. Tried to connect to that instance in SSMS and get this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (.Net SqlClient Data Provider)

Scenario #3
1. Checked Local and remote connections and Using TCP/IP only in SSSAC for a SQL Server 2005 local instance
2. Restarted that instance in SSCM (Note: Shared Memory and TCP/IP enabled for Network Configuration)
3. Tried to connect to that instance in SSMS and was able to connect
4. The following is fromt the SQL Server Logs:

Server is listening on [ 127.0.0.1 <ipv4> 4906].
Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2005\sql\query ].
Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2005].
Server is listening on [ 'any' <ipv4> 2464].

What do the log mean?

Why I cannot connect to the local instance in Scenario #1 and #2?

Thanks for any help,

Peter

|||

SQL Server supports connections using shared memory (which can only work with applications on the same machine), named pipes (an inter-machine communication technology that is mostly used by older applications running on Windows machines - it lets the client communicate with SQL Server as if it were reading or writing to a file), or TCP/IP connections, which can work for any network client on any machine (including, say, Java clients on Unix machines).

In scenario #1 you've disabled remote connections, so only applications running on your server machine can access the server. In #1, you are only allowing connections via shared memory, and in #2 you are allowing shared memory or named pipes connections. It seems likely that in #1 and #2 SSMS is trying to create a TCP/IP connection to your server, but your server isn't accepting those connection types. You can configure how Management Studio connects to your server in the Connection Dialog by clicking the "Options >>" button. In the "Network protocol" dropdown, just pick one of the connection types your server is allowing. In #1, that would be "Shared Memory."

In scenario #3, the log is saying that SQL Server is accepting

TCP/IP connections from the TCP local loopback adapter (the reserved IP address 127.0.0.1, used by network applications to talk to the local machine) on port 4906. If you type "(local)" or "." as the name of the server, SSMS uses 127.0.0.1 as the address of the server.|||

Hi Steve,

Thanks for your post.

I'm not sure what do you mean by "in #2 you are allowing shared memory or named pipes from local clients". If I understand correctly, the #2 scenario is allowing shared memory from local clients and named pipes from remote clients.

If I don't click the Options button in the Connection Dialog, which protocol will be used by default or how can I find out? If I click the Options button in the Connection Dialog and then select <default>, is it same as not clicking the Options button?

Is there any webcast from technet/msdn/microsoft regarding Connection in SQL Server 2005 and/or SQL Server Browser Service?

Peter

|||

You're right, #2 allows remote connections via named pipes. I wasn't reading your description correctly.

<default> means that the connection dialog doesn't specify a network protocol in the connection string used to connect to the server. Under ADO.net 2.0, I believe this results in a TCP/IP connection. If ADO.net changes this default in a future release, <default> would use it. The Options button just exposes additional connection options and doesn't change anything itself, so clicking <default> shouldn't have an effect if that was the setting initially.

I don't know the answer to your question about upcoming webcasts.

Hope this helps,
Steve

Required Microsoft Sql Server 2005 Express Server Roles for JDBC Connection

Hi!

I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.

The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.

Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:

Code Snippet

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;

Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("\nSQL Server Connection established ...\n");

I have heard that Java JDBC connections to Microsoft require high-level access.

Any informed answer is more than welcome. Thanks for reading my post!

Your connection string is a bit different than what's documented in the following article - you'd want to start there:

http://blogs.msdn.com/angelsb/archive/2005/08/01/446452.aspx

Connecting through JDBC does not require high level access or sysadmin rights.

-Sue