April 24, 2008 at 3:12 am
Hi,
I need help to create a backup plan with following info:-
1) Full Backup Every Day (Mid Night 12:00)
2) Diff Back Up (1:00 PM and 7 PM)
3) Log BackUp (every 15 min 24 hrs start from 12:00 MidNight)
But what I need that all backup will overwrite previous backup..
So suppose if I start backup
1) Monday 12:00 PM Full Backup
2) Tuesday 1:00 and 7:00 Diff backup (it should delete all transcation backup upto 7:00) so at 8:00 it should have only didff back up upto 7 and transcation backup from 7 to 8 with full backup of monday)
3) Tuesday 12:00 PM (This should have only one full backup :- onerwrite diff bacup and remove all transcation log).
There is some option INIT to get this but I am not sure what is all this option.
It would be great help if someone list down all steps in details as I am new to DBA Work to get what is expected.
Thanks!
April 24, 2008 at 3:15 am
if u cant write the script then use the GUI..
there every thing is clear
..>>..
MobashA
April 24, 2008 at 3:39 am
I can do overwrite for full backup but how to delete all transcation log files ....
like if i take full backup on monday and tuesday then on tuesday when I take backup i can overwrite existing backup and same time I want to delete all trn files also ... how to delete ... I didn't find any option and all these things I want automated not go and manually delete...
April 24, 2008 at 4:17 am
1- create a batch file to delete files older than like 7 days.
2- create a job to run this batch.
try to use this one to delete files(copy the code and past it into a batch file, change the directory which u want to delete the files from, change the number of days, then run and test):
forfiles /P D:\ProjectServerBackup\PWAbackup /S /D -7 /C "cmd /C del /q @path"
..>>..
MobashA
April 24, 2008 at 4:20 am
and this is the job script:
USE [msdb]
GO
/****** Object: Job [delete old backup file] Script Date: 04/24/2008 13:19:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 04/24/2008 13:19:40 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'delete old backup file',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'older than seven days',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SRV-25\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [exec the batch] Script Date: 04/24/2008 13:19:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'exec the batch',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'xp_cmdshell ''D:\bat\del.bat''',
@database_name=N'master',
@output_file_name=N'D:\bat\output.txt',
@flags=22
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every day',
@enabled=1,
@freq_type=8,
@freq_interval=127,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080117,
@active_end_date=99991231,
@active_start_time=30000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
..>>..
MobashA
April 24, 2008 at 5:18 am
Thanks ! only one small help.. I want to copy these database backup files to other network...
I have two folder
1)FullDataBaseBackUp :- It will have Full DB Back up and I will append DIFF Back up also here
2) Trans Back Up Log Files
Now I want to copy these from one server to other.. Do we have maint plan for this or I need to write any batch files in window scheduler to copy all these files on daily basis...
Thanks
April 24, 2008 at 5:54 am
i think u might create a shared folder and use its path on the job, and make sure that the sql agent have a write permission on the folder.
..>>..
MobashA
April 24, 2008 at 7:19 am
Alternatively if you feel more comfortable using the GUI you can create a maintenance plan to backup databases which can have a step in it to determine how many days you want it to keep backups for and it will automatically delete them for you after 'n' days. You can backup to a backup device which you can set to be the network path.
April 24, 2008 at 7:20 am
What would be TSQL Query for copy one folder to another
Server :- Server1
Folder :- Test1
Server :- Server2
Folder :- Test2
Want to copy from server1:Test1 folder to Server2:Test2 folder...
Please let me give command or TSQL Code for that...
April 24, 2008 at 8:13 am
I would use xp_cmdshell
April 24, 2008 at 8:24 am
jenny.coombs (4/24/2008)
I would use xp_cmdshell
Could you tell me full syntax... with following info
Server :- Server1
Folder :- Test1
Server :- Server2
Folder :- Test2
Want to copy from server1:Test1 folder to Server2:Test2 folder...
xp_cmdshell ?,?
April 24, 2008 at 9:30 am
exec master..xp_cmdshell 'copy C:\file.txt \\servershare\file.txt'
xp_cmdshell allows you to run dos command prompts. If you are using 2005 you will need to check that this is enabled as it is turned off by default.
April 24, 2008 at 1:40 pm
take a look at this script, it should teach you all you need to know about copying and moving files over the network.
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31932/
Marvin Dillard
Senior Consultant
Claraview Inc
April 28, 2008 at 7:45 am
Hi
this code is not working for me to delete old files.
forfiles /P E:\test\ /S /D -5 /C "cmd /C del /q @path"
Wat to do pls help me.
April 28, 2008 at 7:49 am
anjon
What type of trouble are you having? Is your xp_cmdshell set up and using he correct account?
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply