I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.
What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.
|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
|||Specify a report file for the maint plan and check the report file for the error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
Showing posts with label older. Show all posts
Showing posts with label older. Show all posts
Thursday, March 29, 2012
Backup Failure - Delete old files
I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
> > I have setup my backup job to delete file older than 12 hours. I want to
> > keep atleast 2 backup files saved. The backup runs every night.
> > My backup job fails on alternate days.
> >
> > The day it fails, it completes just 2 steps 'backup database' and 'verify
> > database'.
> >
> > The day is completes sucessfully, it completes 4 steps 'backup database' and
> > 'verify database' and 'delete old backup files' and 'check data and index
> > linkage'.
> >
> > It started failing for last few months. The same setting used to work fine
> > before.
> >
> > Any kind of help is greatly appreciated.|||Specify a report file for the maint plan and check the report file for the error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>> What is the error message you get at 3rd step?
>>
>> "helpplease" wrote:
>> > I have setup my backup job to delete file older than 12 hours. I want to
>> > keep atleast 2 backup files saved. The backup runs every night.
>> > My backup job fails on alternate days.
>> >
>> > The day it fails, it completes just 2 steps 'backup database' and 'verify
>> > database'.
>> >
>> > The day is completes sucessfully, it completes 4 steps 'backup database' and
>> > 'verify database' and 'delete old backup files' and 'check data and index
>> > linkage'.
>> >
>> > It started failing for last few months. The same setting used to work fine
>> > before.
>> >
>> > Any kind of help is greatly appreciated.
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
> > I have setup my backup job to delete file older than 12 hours. I want to
> > keep atleast 2 backup files saved. The backup runs every night.
> > My backup job fails on alternate days.
> >
> > The day it fails, it completes just 2 steps 'backup database' and 'verify
> > database'.
> >
> > The day is completes sucessfully, it completes 4 steps 'backup database' and
> > 'verify database' and 'delete old backup files' and 'check data and index
> > linkage'.
> >
> > It started failing for last few months. The same setting used to work fine
> > before.
> >
> > Any kind of help is greatly appreciated.|||Specify a report file for the maint plan and check the report file for the error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>> What is the error message you get at 3rd step?
>>
>> "helpplease" wrote:
>> > I have setup my backup job to delete file older than 12 hours. I want to
>> > keep atleast 2 backup files saved. The backup runs every night.
>> > My backup job fails on alternate days.
>> >
>> > The day it fails, it completes just 2 steps 'backup database' and 'verify
>> > database'.
>> >
>> > The day is completes sucessfully, it completes 4 steps 'backup database' and
>> > 'verify database' and 'delete old backup files' and 'check data and index
>> > linkage'.
>> >
>> > It started failing for last few months. The same setting used to work fine
>> > before.
>> >
>> > Any kind of help is greatly appreciated.
Backup Failure - Delete old files
I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' a
nd
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fin
e
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
>|||Specify a report file for the maint plan and check the report file for the e
rror messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on
:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' a
nd
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fin
e
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
>|||Specify a report file for the maint plan and check the report file for the e
rror messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on
:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>
Tuesday, March 20, 2012
Backup Dependent file remove
In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.
Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.
Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.
Backup Dependent file remove
In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.
Saturday, February 25, 2012
backup and restore
On a production server, I do a full backup monthly and then delete data
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf'
;
>
> David|||Or would log shipping work?
danielp wrote:[vbcol=seagreen]
> Do you have an example of the merge?
> David Browne wrote:|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf'
;
>
> David|||Or would log shipping work?
danielp wrote:[vbcol=seagreen]
> Do you have an example of the merge?
> David Browne wrote:|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David
backup and restore
On a production server, I do a full backup monthly and then delete data
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > On a production server, I do a full backup monthly and then delete data
> > from the tables that is older than the current date. On a separate
> > non-production server I restored the initial full backup. My question
> > is how can I add/restore the subsequent backups to the non-production
> > server with out stepping on the data that was initially restored?
> >
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
>
> David|||Or would log shipping work?
danielp wrote:
> Do you have an example of the merge?
> David Browne wrote:
> > <danielsmith611@.gmail.com> wrote in message
> > news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > > On a production server, I do a full backup monthly and then delete data
> > > from the tables that is older than the current date. On a separate
> > > non-production server I restored the initial full backup. My question
> > > is how can I add/restore the subsequent backups to the non-production
> > > server with out stepping on the data that was initially restored?
> > >
> >
> >
> > RESTORE WITH MOVE, followed perhaps by merging the data.
> >
> >
> > EG
> >
> > BACKUP DATABASE AdventureWorks
> > TO AdventureWorksBackups ;
> >
> >
> > RESTORE DATABASE AdventureWorks_200608
> > FROM AdventureWorksBackups
> > WITH MOVE 'AdventureWorks_Data' TO
> > 'C:\MySQLServer\AdventureWorks_200608.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
> >
> >
> > David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David
from the tables that is older than the current date. On a separate
non-production server I restored the initial full backup. My question
is how can I add/restore the subsequent backups to the non-production
server with out stepping on the data that was initially restored?
Thanks,
Daniel<danielsmith611@.gmail.com> wrote in message
news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> On a production server, I do a full backup monthly and then delete data
> from the tables that is older than the current date. On a separate
> non-production server I restored the initial full backup. My question
> is how can I add/restore the subsequent backups to the non-production
> server with out stepping on the data that was initially restored?
>
RESTORE WITH MOVE, followed perhaps by merging the data.
EG
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups ;
RESTORE DATABASE AdventureWorks_200608
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO
'C:\MySQLServer\AdventureWorks_200608.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
David|||Do you have an example of the merge?
David Browne wrote:
> <danielsmith611@.gmail.com> wrote in message
> news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > On a production server, I do a full backup monthly and then delete data
> > from the tables that is older than the current date. On a separate
> > non-production server I restored the initial full backup. My question
> > is how can I add/restore the subsequent backups to the non-production
> > server with out stepping on the data that was initially restored?
> >
>
> RESTORE WITH MOVE, followed perhaps by merging the data.
>
> EG
> BACKUP DATABASE AdventureWorks
> TO AdventureWorksBackups ;
>
> RESTORE DATABASE AdventureWorks_200608
> FROM AdventureWorksBackups
> WITH MOVE 'AdventureWorks_Data' TO
> 'C:\MySQLServer\AdventureWorks_200608.mdf',
> MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
>
> David|||Or would log shipping work?
danielp wrote:
> Do you have an example of the merge?
> David Browne wrote:
> > <danielsmith611@.gmail.com> wrote in message
> > news:1155141011.175566.161350@.m73g2000cwd.googlegroups.com...
> > > On a production server, I do a full backup monthly and then delete data
> > > from the tables that is older than the current date. On a separate
> > > non-production server I restored the initial full backup. My question
> > > is how can I add/restore the subsequent backups to the non-production
> > > server with out stepping on the data that was initially restored?
> > >
> >
> >
> > RESTORE WITH MOVE, followed perhaps by merging the data.
> >
> >
> > EG
> >
> > BACKUP DATABASE AdventureWorks
> > TO AdventureWorksBackups ;
> >
> >
> > RESTORE DATABASE AdventureWorks_200608
> > FROM AdventureWorksBackups
> > WITH MOVE 'AdventureWorks_Data' TO
> > 'C:\MySQLServer\AdventureWorks_200608.mdf',
> > MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\AdventureWorks_200608.ldf';
> >
> >
> > David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155158638.423828.3310@.h48g2000cwc.googlegroups.com...
> Or would log shipping work?
No. Your deletes would get shipped.
David|||"danielp" <danielsmith611@.gmail.com> wrote in message
news:1155149678.373420.184490@.i3g2000cwc.googlegroups.com...
> Do you have an example of the merge?
It's just SQL, or perhaps an SSIS package. What do the tables look like?
David
Sunday, February 12, 2012
Backing Up Transaction Logs
Dear sirs/ma'am
When setting up a maintenance plan in 2000, I can remove files older than 1 day.
When creating a maintenance task in 2005, I do not have that option (the database is in full mode). The only thing I can do (it seems) is to;
1. create a job with a step that uses a script from going through the motions of creating a log backup directly off the database. This is where I can say that the backup set will expire after 1 day, but in the destination field, the backup filename is hard-coded.
2. Then I schedule this script to run every hour. Sure enough, the log backup is overwritten due to the hard coded name.
The script reads;
BACKUP LOG [CSEPPWebCADB] TO DISK = N'D:\MSSQL\BACKUP\CSEPPWebCADB_backup.trn' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'WebCADB_backup', SKIP, REWIND, NOUNLOAD, STATS = 10
How can I maintain a days worth of log backups or better yet, set up a maintenance plan to give me log backups every hour & delete them after they are a day old?
Sorry, I'm just not seeing how to do this.
Thanks for your help.
what is the srevice pack on this instance ? there are many enhancement in Maintenaceplan component in SP2 and the patch released after sp2 release. post back the result of
select @.@.Version
Madhu
|||I don't believe that the person setting this up put SP 2 or the patch. I'll look into this and reply.
Thanks for your suggestion.
|||You need the correct SP2 loaded on your machine to start with. When you build a maintenance plan, you need to put in 2 tasks. One task is the backup database task. The second task is a clean up task. The clean up task is what removes the files older than X.|||Thank you sir!
JDA
Subscribe to:
Posts (Atom)