Backup Sql Server Express From Remote SQL Server Enterprise

Question

Post reply

Backup Sql Server Express From Remote SQL Server Enterprise

hkflight

SSCommitted

Points: 1949
More actions
December 7, 2011 at 1:50 pm

#247341

I have a SQL Server 2008 R2 Express server that I want to run a set of maintenance plans against. I would like to create these plans on SQL Server 2005 Enterprise Edition. I had this working when I was using SQL Express 2005, but we upgraded the server to win 2008 and SQL Express to 2008 R2 and now it doesn't work.
I tried creating a linked server on the SQL Enterprise server to the SQL Express server but that didn't seem to help. TCPIP and Named Pipes are also enabled.
This is a snippet from the error logs.
"...A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified))."
However, I can query the SQL Express database from the SQL Enterprise instance. When I set up the maintenance plans, I simply browse for the SQL Express instance and select it. It is in the form "SERVER_NAME" and does not have the instance as it is a default instance.
Any thoughts? THank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there are a lot of variables in there...

for example, by default, SQL Express does not allow remote connections...it has to be explicitly enabled.

can you connect via SSMS from the Other server? that's the first thing to check.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hkflight SSCommitted Points: 1949 More actions · Answer 2

Hi Lowell, That box is checked and I am able to connect via SMSS from the 2005 enterprise box.

I know there are probably quite a few variables here, but I am pretty stuck.

THank you for your help!

Lowell SSC Guru Points: 323518 More actions · Answer 3

Ok if you can connect via SSMS from the 2005 machine to the 2008 Express, you can create a linked server;

change the server name belwo, and tell me if this works;

this simply creates a linked server , tells it to passyour current credentials , then obviously asks what tables are visible.

--Linked server Syntax for SQL Server With Alias

EXEC master.dbo.sp_addlinkedserver

@server = N'MyLinkedServer',

@srvproduct = N'',

@datasrc = N'DBSQL2K5\SQLEXPRESS',

@provider = N'SQLOLEDB';

-- Add Default Login (if applicable)

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'True'

EXEC sp_tables_ex MyLinkedServer

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hkflight SSCommitted Points: 1949 More actions · Answer 4

Okay, I have the linked server created, now I get this error when trying to do a DB integrity check remotely using the maintenance plan.

"Executed as user: USER. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:49:42 PM Progress: 2011-12-07 13:49:43.01 Source: {5EFFE11BB-3254-4993-A229-CE01A42FDB0D1} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:49:42 PM Finished: 1:49:43 PM Elapsed: 0.812 seconds. The package execution failed. The step failed."

I feel like I am getting closer.

hkflight SSCommitted Points: 1949 More actions · Answer 5

Thank you for your help Lowell. This was a combination of me needing a linked server and the user that SQL Agent was using did not have permissions on the server. I changed the user for SQL Agent and everything worked perfectly.

Thank you again for your help.