August 4, 2010 at 9:45 am
Our current database product is shipped to customers with stored procedures that create scheduled backups. These backups are done using xp_sqlmaint to ensure that we check the database, only backup if it is clean, verify the backup, backup to the default directory, and create subdirectories for each database. Note that we do NOT create maintenance plans, but rather create jobs that have as there step code the execution of xp_sqlmaint.
What are the options now that Microsoft has deprecated xp_sqlmaint?
Remember we need to deploy this as some sort of script or procedure to customers around the world, each with unique disk setups. Is there a way to programatically do this short of writing our own backup program in C++ / C#?
Why does it seem Microsoft wants all this done by hand, using a Wizard or a designer? What happened to command line functionallity that duplicates everything you can do in a GUI?
Thanks for any insights.
August 4, 2010 at 10:33 am
Could you use a script that executes sp_add_job, sp_add_jobstep, sp_add_jobschedule, and sp_add_jobserver? I don't know what kind of database checks you include, but you can put DBCC
commands in a stored procedure and run it in a T-SQL job step.
I think it would be easiest to create the job using the wizard in SSMS and script it out to a file.
Greg
August 4, 2010 at 11:10 am
Yes, that is exactly what we do now. We Call sp_add_jobstep with @command = N'EXECUTE master.dbo.xp_sqlmaint N''-D ' + DB_NAME (DB_ID ()) + ' -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB -UseDefDir -CrBkSubDir -BkExt "BAK"'''. Similar steps are done for MASTER, MSDB, and Distribution databases, as well as some steps to delete old backups.
How do we accomplish all of this without the use of xp_sqlmaint? I guess we could do a bunch of conditional job steps for each of the tasks (DBCC CHECKDB, etc.). Just seems a shame to throw out the ability to perform all this as a single command just because the command can also be used to launch old-style maintenance plans.
Oh well, thanks for the input.
September 28, 2011 at 8:51 am
To anybody still coming across this issue, SQL Server 2008 generates SSIS scripts to handle all the functionality of the old xp_sqlmaint. These are generated by and managed under Maintenance Plans.
See: http://msdn.microsoft.com/en-us/library/ms191002.aspx
The wizard provides a nice interface as it walks you through setting up a number of different maintenance tasks, including rotating backups and purging of old files. After you use the wizard to create the maintenance plan, you can then open it up and edit it using a pseudo-BIDS interface.
Not sure how the original poster would script that for his task, but I'm sure it's possible.
Neil
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply