October 15, 2009 at 3:32 pm
I am very new to SQL and have been tasked with setting up a backup routine of 2 SQL servers.
On one server, I was able to successfully set up a weekly full backup, daily differential, and hourly transaction log backups.
The other server will not cooperate. I made sure all databases were set to full recovery method and attempted to set up a full backup of all databases - it fails. I then tried to backup just one database-it fails. I have changed the backup destination and still have had no luck.
The logs are virtually empty. Below is the only information I get:
- Execute maintenance plan. MaintenancePlan (Error)
Messages
* Execution failed. See the maintenance plan and SQL Server Agent job history logs for
details.
------------------------------
ADDITIONAL INFORMATION:
Job 'MaintenancePlan.Subplan_1' failed. (SqlManagerUI)
Any help will be greatly appreciated!!
October 15, 2009 at 3:53 pm
Hi,
Did you try to run the backup manually?
Also, I think there is more detailed information about the error. You have to click the '+' on the row showing the plan in the history window and select the task that failed. Then you will see more speccific information about the error.
Regards
Piotr
...and your only reply is slàinte mhath
October 16, 2009 at 7:07 am
I ran it by right clicking and choosing execute and I get the same error. I did open the detail and do see some additional information:
"Unable to start execution of step 1 [reason: line[1]: Syntax error]. The step failed."
I used the Maintenance Plan Wizard to set it up, since I am very unfamiliar with the way this works.
If I right click one of the main databases and choose backup, I get a different error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Backup failed for Server 'locationame'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The volume on device 'file location\server name.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
October 16, 2009 at 8:21 am
Are you backing up to a local disk?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 16, 2009 at 8:28 am
Yes, I'm backing up to a local disk. Initially I was trying to backup across our network to another server but switched it to backup to the same hard drive the data was on to eliminate potential issues while debugging.
October 16, 2009 at 3:18 pm
Hi
Can you try running this query through ur new query window to take backup and please check what error you are facing.
BACKUP DATABASE dbname TO DISK = N'C:\dbname.bak', DISK = N'D:\dbname.bak' WITH FORMAT;
Thanks
October 16, 2009 at 11:55 pm
r_prasanna82 (10/16/2009)
HiCan you try running this query through ur new query window to take backup and please check what error you are facing.
BACKUP DATABASE dbname TO DISK = N'C:\dbname.bak', DISK = N'D:\dbname.bak' WITH FORMAT;
Thanks
Whats the point taking a striped backup. OP didnt say she's running out of disk space.
--The problem may also arise if you are taking backup of all databases and if your system databases are in simple recovery model, the plan would fail.
Can u check that out. Also if possible create seperate maintainance plan for full, diff and tran log backups.
October 19, 2009 at 3:37 pm
All databases are in full recovery method. I tried scheduling a full backup of just a single database and it still failed. I also tried to manually run it by right clicking the database and running the backup and it fails. Is there anything else I can run that might provide more detail on the issue?
October 19, 2009 at 4:29 pm
Did you try to see what T-SQL is used to create backup? Right click on the plan, select Modify.
When the plan is open, right click on "Back Up Database" task. Click Edit.
A dialog box appears. Click "View T-SQL" at the bottom of the dialog. Have a look if there is anything wrong with the code. If you have doubts, please send the code here, someone will try to help for sure.
Regards
Piotr
...and your only reply is slàinte mhath
October 20, 2009 at 7:10 am
Here's the T-SQL code from the maintenance plan I set up to run a full backup of a single database, which still fails:
EXECUTE master.dbo.xp_create_subdir N'G:\SQL_backs\psc_comp' GO
BACKUP DATABASE [psc_comp] TO DISK = N'G:\SQL_backs\psc_comp\psc_comp_backup_2009_10_20_085005_6247500.bak' WITH NOFORMAT, NOINIT, NAME = N'psc_comp_backup_2009_10_20_085005_6247500', SKIP, REWIND, NOUNLOAD, STATS = 10
This morning I was successfully able to run a full manual backup of the same database referenced in the code above by right clicking, choosing tasks, then backup. It wouldn't work before, but this time I changed the overwrite media option to "overwrite all existing backup sets". I don't see this type of option when setting up a maintenance plan.
Thanks!!
November 4, 2009 at 7:36 am
I still haven't been able to get these backups working. Is there anyone that has a suggestion??
Thank you for your time!
November 4, 2009 at 3:40 pm
Instead of going through the maintenance plan wizard, what happens if you create a new maintenance plan and just add jobs for the backups that you want done and try running that?
Joie Andrew
"Since 1982"
November 5, 2009 at 7:25 am
If I choose the "backup database task" instead of using the wizard, I still get the same failure. The only way I am able to back anything in this SQL server up is to right click and choose "backup", but this isn't allowing the transaction logs to be backed up.
Since I am very new to SQL, I'm not sure what pieces of information might be important to identifying the issue, but here's some facts about our setup:
We have SQL 2005 and SQL 2008 Developer Edition installed on the same server. Our team started developing in 2008 and ran into some issues so it was abandoned and development was started in 2005. 2008 has not been removed from the machine.
Some of the databases in the SQL server instance that I am trying to back up were initially part of another SQL server instance. Both are 2005.
All user databases are set to full recovery method.
If there is any settings or anything else I should check, please let me know.
November 5, 2009 at 7:53 am
Ideally you should be able to create the backup job using maintainence plan.
However you can create a job manually.
write the following code as a part of a job step and set a schedule for the job
declare @log_name as varchar(50)
set @log_name='c:\backup\test_log_' + convert(varchar,getdate(),112) + '.bak'
backup log test to disk=@log_name
Run the job manually to test if the job runs successfully.
November 5, 2009 at 8:07 am
I'm sorry - I'm not sure how to do that. From the toolbox, do I choose "Execute SQL Server Agent Job Task"? I can't seem to paste the code you suggested any where. I'm still trying to figure all this out...
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply