Backup problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Backup problem

Author

Topic

AG_corcho
Starting Member

5 Posts

Posted - 2012-11-13 : 05:13:42

Hello

My MS SQL version is Standard Edition/SP4/9.00.5000.00
I have maintenace Plan to make full backup to all databases. It is working with no problem.
The problem occur when I try to backup one of db manually by start enterprise management and right click on database then ->Task->Back up (Recovery model: SIMPLE; Backup type: Full; Destinantion file).
Backup process starts and then go up to 10% and raise error like that :

quote:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Backup failed for Server 'Server1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Uplynal limit czasu. Limit uplynal przed ukonczeniem operacji lub serwer nie odpowiada. (Microsoft SQL Server, Error: -2)
This is polish - bellow translation
quote:

Timed out. Limit expired before completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476 height="1" noshade id="quote">

Location of backup file is the same as in maintenance plan case and also other database CAN be backup in this location so is no problem with files permissions.
No additional info or errors are in SQL logs or system logs.

Waiting for any suggestions.
Best regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-13 : 05:45:43

Maybe you are running management studio on your client machine and not on the server and then the destination path is a drive E: (for example)... This drive E: is on the server and not on your local machine.
Try to open mstsc (remote desktop) and do it on the server...

Too old to Rock'n'Roll too young to die.

AG_corcho
Starting Member

5 Posts

Posted - 2012-11-13 : 05:58:19

Hello
I performing all tasks physically on server, so it is not that case.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-13 : 07:16:31

maybe then it is a bug like posted here:
https://connect.microsoft.com/SQLServer/feedback/details/286298/the-timeout-for-query-execution-is-difficult-to-set-for-local-connections

Your solution could be:
1.) In the backup dialog when ready don't click ok --> click the script button (upper left corner of the dialog) instead
2.) Close the backup dialog (with X, not with OK)
3.) In that new query window (with the backup command generated by the dialog) do a right click
4.) Choose "Query Options"
5.) In the upcoming dialog set the "execution time out" to 6000 and click ok
6.) Now run the backup statement by hitting F5 or click on execute query
7.) good luck!

Too old to Rock'n'Roll too young to die.

AG_corcho
Starting Member

5 Posts

Posted - 2012-11-13 : 08:00:07

Last suggestion is ok.
Backup was start and finish well.
But if it can be use in case of manual backup from management studio ?
Can I set "execution time out" to value 6000 as default ?

Thanks a lot

AG_corcho
Starting Member

5 Posts

Posted - 2012-11-13 : 08:03:48

One question more.
Better thing is to repair cause than effect. So what cause it can be - or how to determine it ?

Tanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-13 : 08:08:38

Glad it works for you

You can try to set a default in management studio as follows:
- Menu: Tools
- Options...
- Query Execution (on the left side)
- Enter the value for "Execution time-out" (on the right side)
- click OK

but I don't know if it works for that problem and I don't know if it is a good idea always to have 6000 as default - just try!

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-13 : 08:10:07

quote:
Originally posted by AG_corcho

One question more.
Better thing is to repair cause than effect. So what cause it can be - or how to determine it ?

Tanks

As I already posted the link --> it is a known bug so you can't do anything other than have a workaround...

Too old to Rock'n'Roll too young to die.

AG_corcho
Starting Member

5 Posts

Posted - 2012-11-13 : 08:37:19

Again thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources