Schedule Backup Of Specific Database While Only Saving The Last 7 Days In Repository

Apr 15, 2008



Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple

With SQL Server Agent I am running schedule task of the backup script

The question is:

I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on

I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP

I am in situation that the command above only append to the backup file and without deleting the expired backup days.

What I need to change in the parameters that the script will delete the expired backup days.

Thanking in advance everyone who assist.

View 1 Replies


ADVERTISEMENT

Schedule Backup Of Specific Database While Only Saving The Last 7 Days In Repository

Apr 19, 2008



Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple

With SQL Server Agent I am running schedule task of the backup script

The question is:

I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on

I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP

I am in situation that the command above only append to the backup file and without deleting the expired backup days.

What I need to change in the parameters that the script will delete the expired backup days.


It is too much work when working with Database Maintenance Wizard.
lets take a scenario that I have 10 database.
I would like that each database will backup into seperate file.
I need to create 10 * 7 Maintenance plans.

I have created a directory with the name SDBbackup.
Now under the directory SDBbackup I have created 7 directories (1,2,3,4,5,6,7).
Each directory is for Sunday=1 , Monday=2 , and etc.

It's very much work if I need to accomplish it with Database Maintenance Wizard.

This is why I am asking a specific question regarding T-SQL.

Thanking in advance everyone who assist.

View 1 Replies View Related

Saving DTS Packages In The Repository...

Sep 29, 2000

... and not being able to open them anymore.

I am working with SQL Server 7.0 SP 1. Everything seemed to be running OK but from some days ago when I create a new DTS package and I save it in MS Repository, the package doesnīt appear under the "Repository Package" folder even when I refresh it.

I know it is there because I can see it in the Metadata folder, but there I can not modify it. What happened? Are DTS packages secure? Should I better use local packeges and save them in SQL Server?

Thanks a lot.

View 1 Replies View Related

Shrink Database Base On Specific Schedule

Jun 9, 2004

Hi

I want to know what is command that i add to the Query Analyzer until
i tell to the database that it Shrink my DB every 1 day

I know it can be done from all Tasks > Shrink Database ..

But i want to know the Code commands for that for using in Query Analyzer


Thanks

View 5 Replies View Related

Shrink Database Base On Specific Schedule

Jun 9, 2004

Hi

I want to know what is command that i add to the Query Analyzer until
i tell to the database that it Shrink my DB every 1 day

I know it can be done from all Tasks > Shrink Database ..

But i want to know the Code commands for that for using in Query Analyzer


Thanks

View 4 Replies View Related

How Can I Schedule Backup Of Whole Database Annually Without Using Replication?

Sep 20, 2001

Hi,
Is there any method that I can use to backup the whole database to another database without using replication? I have to backup it every half year. Can I do something like setting schedule to run to "Database Backup" module in sql server?


Thanks,
Matt

View 1 Replies View Related

Database Backup On Task Schedule Every 1 Hour

Sep 24, 2013

I want to take a sql server 2005 database backup on task schedule every 1 hour is that possible.

View 1 Replies View Related

How To Schedule A Database Backup In SQL Server Agent Jobs?

Jan 7, 2008

I want to schedule a database backup using SQL Server Agent Jobs how can i do this?

View 1 Replies View Related

Schedule Database Backup And Delete For Server Express 2014

Jul 30, 2015

I would like to auto daily back up for all databases on a SQL Server Express 2014, and keep back up of last 30 days.

View 3 Replies View Related

How Can I Take Backup Of Logfile Of One Specific Database?

Jul 20, 2005

Hi,I want to take backup of database logfile periodically andautomatically. What should i do then.....ThanksHoque

View 1 Replies View Related

DTS: Schedule Package On Different Days Each Month?

Aug 16, 2001

I wish to schedule a DTS package so that it runs on the penultimate day of each month. Is there an advanced option which allows you to choose a different date for each month? If not, is there a way this can be done programatically/

Thanks
Neill

View 1 Replies View Related

DB Engine :: Creating A Schedule Task To Execute After 5 Days From The 28th Of Every Month

Jun 7, 2015

Is it possible to create a SQL schedule task to execute after 5days from the 28th of every month.

View 3 Replies View Related

SQL 2012 :: Schedule Backup / Copy Backup And Rename To Other Folder

Dec 29, 2014

I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?

View 4 Replies View Related

T-SQL (SS2K8) :: Generate Working Schedule For Employees For X-days Ahead Based On Starting Date

May 6, 2014

I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.

I have got 3 relevant tables:

1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)

2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)

3. Table Z with resource data.

An example to clarify (for fake employee 100):

Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)

Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):

Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)

The weekend days show as 00:00 for the hours (same as day 8 in example)

I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.

I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...

I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):

2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6

With this done I can compare the actual daynumber with the daynumber in Table Y.

The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.

View 0 Replies View Related

Can't Schedule Backup

Dec 3, 2004

Hello All,

I using sqlserver 6.5 and I am trying to schedule a backup and I get this error message, how can I resolve this? This is the error message I am getting:

"The SQLExecutive service is not currently running on this server. This prevents task backup-master from being run."

Thanks in Advance.

View 2 Replies View Related

Schedule Backup

Mar 14, 2007

Hi,
In sql server 2005 i have created a maintenance plan to backup a database.
Where do i schedule this backup to run every day please?
Thanks

View 1 Replies View Related

Schedule A Backup

Nov 21, 2007

what is the best way to schedule a bakcup in sql
i'd like it to be a complete backup not an append.

Is there anyway to schedule it to each day backup to a directory for that day?

View 14 Replies View Related

Backup Schedule

Mar 7, 2006

SQL server 2005 is installed but can not schedule a backup job.
In the maintenance plan when selecting a new maintenance plan or maintenance plan wizard an error is produced which is:
'Agent XPs' component is turned off as part of the security configuration for this server.
I can see that the SQL Server Agent (Agent XPs is disabled). When trying to start this by right clicking on it in object explorer, the error message is 'unable to start service SQLAgent$SQL2005 on server serverName'. Service Logon failure

How can I scedule a back up job on this server?

Any thoughts please?

View 4 Replies View Related

Unable To Schedule SQL Backup

Jan 2, 2001

Hi all,

On my server running SQL7.0 with SP2, I try to backup my database to a file.
All works well if I run the backup manually, but when I use the schedule option, when I close the Backup properties window and open the window again, the schedule is gone...
Does anyone of you know how to get this working?

Thanks in advance,

Camiel

View 2 Replies View Related

Need Help In Deleting Backup Schedule

Aug 18, 2000

I created a backup device and then scheduled a recurring backup of one of my SQL databases for SMS. I right-clicked on the database name and chose All Tasks | Backup and set a daily schedule.

Now I want to delete the whole thing and use Database Maintenance Plans instead. The backup device never showed up in Management | Backup so I deleted the backup files (and devices?) from NT Explorer. The backups still occur, I'm guessing, because I never deleted the Schedule. How do I get these backups to stop?

P.S. Forgive me if this is an elementary questions -- I am new to SQL.

Thanks.

View 1 Replies View Related

How To Take Backup Of Last 20 Days Or Last 2 Months

Aug 21, 2015

How do i take full backup of last 20 days transactions, and i can use that backup to restore those 20 days transactions.

View 10 Replies View Related

Network Drive Not Available In Backup Schedule

Oct 24, 2000

I am setting up a backup on one of my DB's and the network drive does not appear as an location that I can backup too. Only the C drive is present. However, on the server I do have a drive mapped to the network drive, so it should be available. Has anyone encountered this issue before?

Thanks!

Scott

View 10 Replies View Related

Schedule Restore From Backup Deivce

Sep 29, 1999

Hi,

I am trying to schedule a daily task to restore the latest backup from a BackupDevice that contains multiple backups. The command to restore is "Load DBName from BackupDevice". Does anyone know how to restore the most current backup from the Backup device?

Thank you for your help.

View 2 Replies View Related

Sql Server 2005 Backup Schedule

Mar 14, 2007

Hi,
In sql server 2005 i have created a maintenance plan to backup a database.
Where do i schedule this backup to run every day please?
Thanks

View 1 Replies View Related

Schedule Backup Of Data/log Files

Sep 5, 2006

Is there an equivelant to the SQL Server Agent in SQL server express edition? If not, how can I schedule the backup of the data/log files?

Thanks,

View 3 Replies View Related

Schedule Backup With SQL 2005 Express

Jun 10, 2007

How do I schedule a backup with SQL Server 2005 Express. When I right-click a database and choose Tasks -> Back Up...
I don't get the option to schedule the backup. How do I do?

View 9 Replies View Related

Schedule Backup SQL Server 2000

Jun 16, 2006

Hi all,How can I schedule backup in SQL Server 2000 for ALL databases?
With SQL Server 2005 I can create maintenance plan to do this,
but in the 2000 version I donīt know.thanks!!!!

View 4 Replies View Related

SQL 2005 Backup Schedule Error

Nov 13, 2007



Hi,

I want to schedule a daily backup of one of my database in SQL 2005 Enterprise edtion but
the problem is, I can't save my job. Please help, here's the error.

===================================
Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.JobCollection.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.Contains(String name)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.JobExists(String jobName)
at Microsoft.SqlServer.Management.SqlManagerUI.JobData.ApplyChanges()
at Microsoft.SqlServer.Management.SqlManagerUI.JobPropertySheet.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)


Thanks.

Russell

View 1 Replies View Related

Schedule Backup In SQL Server 2005

Mar 21, 2007

Anyone knows how to schedule backup of database in SQL server 2005?

View 10 Replies View Related

How To Move BackUp Files By Schedule To Any Computers ?

Jul 25, 2007

I set up MA plan to full back up by schedule
and I would like to move them each completing process to another computer for preventing failing if harddisk of server was damaged.
 
How to move them by schedule to any computers ??
Please give me some suggest. 

View 1 Replies View Related

SQL 2012 :: How To Schedule Daily Backup At 6:00am

Jul 29, 2015

I need to schedule daily backup at 6:00am. how to do this. I'm on Sql Server 2008r2

View 1 Replies View Related

Deleting Backup Files Older Than 2 Days

Jun 23, 2008

Friends -
I am looking for a windows script (bat file) to delete backup files which are older than 2 days.

Please provide scripts on this.

Appreciate your support

Cheers :)
Satish

View 2 Replies View Related

Deleting Backup Files Older Than 5 Days Old.

Mar 14, 2007

I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.

I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved