"Backup and restore operations are not allowed on database tempdb"

Question

Hi all

 

SQL 2005 SP2.  Upgraded from 2000 and migrated maintenance plan.  I am now getting an error when running the maintenance plan for all System databases:

 

failed with the following error: "Backup and restore operations are not allowed on database tempdb.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

Someone else has the same problem and reported it here:

 

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303848&wa=wsignin1.0

 

Any thoughts on this?

 

Tony

MVP - Directory Services

Answer 1

Do you know how TEMPDB works?

 

don't make sense to backup this database.

 

If we consider that there is a bug when the SYSTEM DATABASES is checked on maintenance plan because TEMPDB is included, so you just need to choice each system database (MSDB, MASTER, MODEL)

 

Answer 2

Why would anyone want to back up tempdb? It's what it says on the tin, a temporary database, used internally and for join/sort operations or by any queries specifying the use of temporary tables.

 

SQL Server destroys and recreates tempdb on start-up.

 

You could never back this db up in any version, so it's got nothing to do with the migration.

 

The bug report is actually complaining about the fact that tempdb is included in the system db maintenance plan, when it shouldn't be, because as well as a backup of tempdb being pointless, it's also actually prohibited by SQL Server.

 

Answer 3

 

Thanks for the responses.

 

I now understand more about the role of the tempdb, having done some reading on it.  What threw me was it's inclusion in the system database maintenance plan. 

 

Tony

Answer 4

To answer "why", consider the following scenario.  There is zero money in the budget to purchase more disk space and even if you could, the disk size in the server is maxed out.   Given this scenario, disk space is at a premium.  The tempdb raises flags every day and this is a production server where restarting the server takes it off line.   It is not clustered and should not be scheduled for a restart.   There is less than 20 GIG of space available on the disk and the 200 GIG database could, at any time, require an extra 200 GIG of dataspace on a transaction lock. 

So the question is not "why", it is "how" can you reduce that file regardless of the consequences.   Either the server goes down (not an option) or something else must take place.   If he could force a backup of the tempdb, it may reduce the log file to a decent size.  I'm sure there are better ways to reduce the size of the tempdb but everything I have looked at online indicates it cannot be done without a server restart.

---

R, J

Answer 5

Hello

Looks like the maintenance plan has a bug that is including the tempdb for backups. Edit the maintenance plan and remove tempdb if it is there, then save it and retry

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you