Solved

sql server 2008 R2 remote backup timeout expired

Posted on 2014-10-15
11
16 Views
Last Modified: 2015-11-12
Hi,

I have a remote routine to backup a SQL Server 2008 R2 database. The routine run in a different server of the database. So, I use the following command:

BACKUP DATABASE [<database name>] TO DISK = '\\<local server name>\BACKUP.BAK'

I do not connect to the database with the "sa" user, but with the database owner user, and the routine work well until now.

The problem is that now I get the error below when I run the routine:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. When execute the SQL command (BACKUP DATABASE [<database name>] TO DISK = '\\<local server name>\BACKUP.BAK')

How I fix this?

It is important to remember that, as I haven't the "sa" user credentials, I can not  change parameters of the SQL Server installation. Also, due to the strict security policy,  initially my client does not permit this type of change.

Thanks in advance,

Marcelo Camarate
0
Comment
Question by:Marcelo Camarate
  • 6
  • 5
11 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40381936
I don't think it's a permission issue.
You should check if it's some heavy process running at same time and also check if the network bottleneck.
0
 

Author Comment

by:Marcelo Camarate
ID: 40382047
Hi Vitor,

Thanks for your reply.

I know that is not permission issue because, as I said in my last post, the routine work well until now. But, in the last two days I try to run it at several moments of the day without success. Therefore, the user that I connects to the database have permission to execute this command.

I said about permissions only in relation to change some parameter of the SQL Server installation, because I don't have the "sa" credentials.

Any other ideas?

Regards,

Marcelo Camarate
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40382050
You should check if it's some heavy process running at same time and also check if the network bottleneck.
0
 

Accepted Solution

by:
Marcelo Camarate earned 0 total points
ID: 40384424
Hi Vitor,

Today, at 2:30 AM, I tried running the routine and the same error occurred.

The image below shows the existing infrastructure

INFRASTRUCTURE
I access the APPL_SERVER through a remote connection using an administrator login. Then, I establish a connection to DATABASE using a user that is owner of the database that I want to backup, and run the following command.

BACKUP DATABASE [<database name>] TO DISK = '\\APPL_SERVER\BACKUP.BAK'

Note that the path is in the network ("\\APPL_SERVER"). Ie, to the server that I'm logon. And it had been working fine until last week.

The problem is that don't have tools for network monitoring installed in APPL_SERVER, and I can not install any tool of this kind because the infrastructure is in my customer. Also, I haven't any user credential to login DATABASE_SERVER.

But I need to resolve the issue as soon as possible. Then, do you know (or other expert) a similar tool of the Microsoft Database Publishing? It does not work SQL Server 2008, because it was embed in Microsoft SQL Server Management Studio 2008, tool that also I can not install in APPL_SERVER in accordance with the policies of my customer. Would be ideal, because the Database Publishing generates a script to create/charge the database.

I'm waiting for your next suggestions.

Regards,

Marcelo Camarate
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41212807
I'm sorry Marcelo. Somehow I missed your comment.
The database owner is a Domain Login or a SQL Login only?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Marcelo Camarate
ID: 41213047
Hi Vitor,

The database owner is a SQL Login only.

Regards,

Marcelo Camarate
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41213087
That's why you couldn't have this working. A SQL Login doesn't have access to a network share. You'll need to run the BACKUP command with a Domain Login and that Login must be at least member of db_backupoperator role.
0
 

Author Comment

by:Marcelo Camarate
ID: 41213592
Hi Vitor,

This is exactly why I closed this topic and open another titled "SQL Server - Web tool to backup / restore". You could read it and suggest something.

Thanks and Regards,

Marcelo Camarate
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41213605
Ok. Can you provide the link for the new question?
0
 

Author Comment

by:Marcelo Camarate
ID: 41215964
Hi Vitor,

The new questions is in http://whttp://www.experts-exchange.com/questions/28702123/SQL-Server-Web-tool-to-backup-restore.htmlww.experts-exchange.com/questions/28702123/SQL-Server-Web-tool-to-backup-restore.html.

Regards,

Marcelo Camarate
0
 

Author Closing Comment

by:Marcelo Camarate
ID: 41232529
I did not received any reply since Oct 16, 2014 and at August 1, 2015 I opened another question titled "SQL Server - Web tool to backup / restore" to try solve the same problem.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
STDEVP in SQL 2 38
TSQL - Rollup data set 7 29
how to check the index used by a MS SQL queries or SP 7 15
Stored Procedure 2 0
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now