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 2008 Forums
 SQL Server Administration (2008)
 Stuck @ Restore

Author  Topic 

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2010-08-05 : 11:12:42
An issue just came up with my backups. I currently use Main Plans to do backsup since our databases are fairly small. It works fine for our needs.

However, after a backup is complete, I would refresh my Management Studio and all the databases that were backed up by the Main Plan are stuck in "Restore." Nowhere in my Main Plan is there anything that instructs it to restore any databases. My Plans are to back up and thats it.

To get everything back to normal state, I just run Restore Database db_name With Recovery.

Has anyone run into this issue? If so, what did you do to resolve this?

If no solution, my next step is to delete my current plan and create it again.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-05 : 12:10:46
sure sounds like you have a restore step in the maint plan.

we don't use maint plans as a policy. instead we use our own custom backup scripts.

i know that Tara has a very detailed backup script around here somewhere that you can use as an example for how to do it.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-05 : 12:47:02
Here it is: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

And I can guarantee you mine doesn't do a restore. I hate maintenance plans!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-08-07 : 09:21:50
I would suggest using Profiler to trace what the maintenance plan is actually doing. It might turn up something interesting like this:

BACKUP LOG AdventureWorks TO DISK = '...' WITH NORECOVERY

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page
   

- Advertisement -