SQL Server Performance Forum – Threads Archive
Maintenance Plans questions
Hi folks I am looking for how to create Full Backup and Transactional Backup through one Maintenance Plan on separate time and folder. Also remove few days or (whatever time) older backups exactly you can do it using SQL Server 2000. I could not find this options in SQL server 2005’s Maintenance Plan. Secondly..generating objects scripts as separate file when you choose more than one objects in one go as there is option for one file only rather separate fils (PRC,DF extension files so on…) similiarily in SQL server 2000. Thanks in Advancesonny
You can erase the older files by using Maitenance Cleanup task. Is there any particular reason why you are looking to have your backups created using maintenance plans, because people normally prefer having them created by scripting jobs. I did not understand your second question. Satya
Cleanup task uses undocumented extended procedure xp_delete_file…
You can use it if you want to delete files from different folders… http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=718788&SiteID=1 DECLARE @ThreeDaysAgo VARCHAR(50) SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR) — NB this will delete any "bak" file not only the maintenance plan ones. — in case the backups are failing check we have at least 2 backups in the folder — check > 3 as we always have a final null record for the dir listing – note also that — its > 2 backups and not necessarily 2 backups from 3 days ago. Using 2 as its going — to run before the backup job, but the most likely cause of failure is lack of disk space — but we don’t want a failure happening and end up with no backups EXEC dbo.xp_cmdshell ‘dir D:Backupsdb*.bak /b’ IF @@ROWCOUNT > 3 BEGIN EXECUTE dbo.xp-delete_file 0,N’D:Backupsdb’,N’bak’,@ThreeDaysAgo
— only do log if deleting the main backup EXEC dbo.xp_cmdshell ‘dir D:Backupsdblog*.trn /b’ — for logs we do 3 backups a day so check > 9 IF @@ROWCOUNT > 9 EXECUTE dbo.xp-delete_file 0,N’D:Backupsdblog’,N’trn’,@ThreeDaysAgo END
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thanks guys… Satya.. second question about.. generating script option when you right click on database–>task option form context menu–>Generate Script….
There is different between SQL Server 2000 and 2005. in 2000 you 2 options to save files as one files or separate files for objects (if you you scripting more than one objects). but you do not have in SQL Server 2005.. so I need to know that if we have option available like 2000 in SQL Server 2005 in case I am not aware of it. thanks again,
sonny
Right the database you want to script/Tasks/Generate scritps/
and follow the wizard… you should the options you are looking for…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
See thishttp://www.sqlmag.com/articles/index.cfm?articleid=94510& fyi on Scripting too.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks guys.. MohammedU…
like said we have only one file option in SQL Server 2005 unless I am missing something…(script to file with 2 option ‘file name’ and ‘save as’) not like 2000 save as one file or separate file each objects if you choose to make script of more than 1 objects..
What is the service pack level on client tools?
Having 9.00.2047 I can reproduce the scripts to individual files.
Also mention the method you are trying to do. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I am using Standarad Edition of SQl server 2005 with windows 2000 Server wth SP4. not using any Sql Server service pack (I think i should use SP2????) Thanks..
I am also using sql 2005 SE and I have the option for individual file.. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
SP2 on client tools as well, most of out there will miss this. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>