I create a management plan that includes nothing but a completedatabase backup (i.e. no reorganization of index pages, logging, etc.).I set the backup schedule to occur every Sun at 12:00AM and hit OK.Sometimes I get the following error :"Error 8114: Error converting datatype int to tinyint" sometimes not. Either way, though, the same thinghappens, the schedule for the backup is not saved. If I reopen themanagement plan and go to the "Complete Backup" tab, there is noschedule in the schedule box.
I have found this link:
http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there isno resolution there. I was wondering if anyone had any ideas.
Jason
just a quick dirty question: can you perform a normal backup? Is there a job for the backup? If so will this force run?|||Yes, I can perform manual backups without a problem.And, in fact, I can successfully create a maintenence plan withschedules for things like "Index pages" etc. And these schedules worksuccessfully. However, I am only interested in making a maintenenceplan for complete backups on a schedule and that does not work. Oneworkaround that would probably work is to just use the SQL ServerBackup dialog and set a schedule in there. However, this does not allowme the flexibility I require to manage the backups, such as saving thefiles with their dates appended and triaging the backups into seperatefolders for each database. It also does not allow any extensibility fordown the line when I would like to automate the cleaning andoptimization tasks as well as logging.Finally, I think I should alsomention, that I do have SQL Server Agent running, because this isrequired for the schedules to work correctly.
Jason
|||Have you thought of writing back-up scripts for these processes. Thesecould then be controlled by DTS or if you really have to xp_cmdshell?
|||I have thought about it, but have decided that my efforts are betterspent getting the maintenence plan working for several reasons. Firstoff, all I need is a basic backup and I do not require the flexibilitythat custom scripts would provide (nor do I need the overhead ofmaintaining said scripts). Lastly, the maintenance plan SHOULD work, Ijust have to figure out why it is not working rather than determining aworkaround.
Jason
|||
ok, just is case you do want a script here's one you could use.
--Declare variables
declare @.BACKUP varchar (100)
declare @.day varchar(50) -- date dd/mm/yyyy
declare @.hour varchar (4) -- hour hh
declare @.copy varchar (200) -- copy statement to where ever you want
--set day This is used for the day FULL backup
set @.day = convert(varchar(50), getdate(), 103)
set @.day =replace (@.day, '/', '_')
--set hour This is used for the LOG transaction backups Not needed for full backup
--set @.hour = datepart(hour, getdate())
--set the name of the backup
set @.BACKUP = location of backup i.e. 'E:\data\MSSQL\BACKUP\Northwind\Northwind_'+@.day+'.bak'
--backup database SQL
backup database Northwind todisk=@.BACKUP with stats
--copy the file to where
set @.copy = 'copy'+@.BACKUP+ location'\\servername\drive$\folder\
