I was setting up the backup for a new database last evening and seem
to have created a small problem. Although I was able to create a
backup and restore it, in the process I seem to have left a couple of
other copies of the database files in a state where they cannot be
moved, deleted or copied.
I started by right clicking on the database and selecting Back Up. I
then did a Full Backup of the Database (not the Files and
Filegroups). I then detached the original database (probably a
mistake) and went to the Databases level, right-clicked and selected
Restore Database. I did not find the name of my database in the "To
Database:" list so I typed it in and proceeded to select the From
Database (which did have the name of my database and the correct
backup time) and restored the database.
The database seemed to work fine.
However when I checked the files I found not only my original
DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
ones
DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
the
actual files being used by the restored database.
I would now like to do two things:
1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
files to a new location, and
2) Rename the files that are actually being used from
DatabaseName.MDF
and DatabaseName_1.LDF to DatabaseName_Data.MDF and
DatabaseName_Log.LDF as they were before.
However I can't seem to do anything with the original files because
they seem to still be attached to the server. How can I SAFELY
accomplish what I want without endangering my good database (with the
wrong file names)?
Thanks.Detach the databases and do whatevery you want. Move them, rename them or
whatever and then reattach whichever one you want to use.
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188538011.871157.326450@.q5g2000prf.googlegroups.com...
> I was setting up the backup for a new database last evening and seem
> to have created a small problem. Although I was able to create a
> backup and restore it, in the process I seem to have left a couple of
> other copies of the database files in a state where they cannot be
> moved, deleted or copied.
> I started by right clicking on the database and selecting Back Up. I
> then did a Full Backup of the Database (not the Files and
> Filegroups). I then detached the original database (probably a
> mistake) and went to the Databases level, right-clicked and selected
> Restore Database. I did not find the name of my database in the "To
> Database:" list so I typed it in and proceeded to select the From
> Database (which did have the name of my database and the correct
> backup time) and restored the database.
>
> The database seemed to work fine.
>
> However when I checked the files I found not only my original
> DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
> ones
> DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
> the
> actual files being used by the restored database.
>
> I would now like to do two things:
>
> 1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
> files to a new location, and
> 2) Rename the files that are actually being used from
> DatabaseName.MDF
> and DatabaseName_1.LDF to DatabaseName_Data.MDF and
> DatabaseName_Log.LDF as they were before.
>
> However I can't seem to do anything with the original files because
> they seem to still be attached to the server. How can I SAFELY
> accomplish what I want without endangering my good database (with the
> wrong file names)?
>
> Thanks.
>
Showing posts with label old. Show all posts
Showing posts with label old. Show all posts
Tuesday, March 20, 2012
Sunday, March 11, 2012
Backup plan didnt remove the old files
Hello, everyone:
I have a backup plan that remove the old backup files more than two days. Last Monday, the SQL Server restart. Since that, the backup is fine, but it didn't remove the old backup files. Why and how to handle that? Thanks
ZYTIt happens. Manually delete them.|||If you used the database maintenance wizard to create the maintenance plan, check the log files (under %install directory%\MSSQL\LOGS) and look through the entries. There should be something in there to indicate why the files were not deleted.
Regards,
hmscott
I have a backup plan that remove the old backup files more than two days. Last Monday, the SQL Server restart. Since that, the backup is fine, but it didn't remove the old backup files. Why and how to handle that? Thanks
ZYTIt happens. Manually delete them.|||If you used the database maintenance wizard to create the maintenance plan, check the log files (under %install directory%\MSSQL\LOGS) and look through the entries. There should be something in there to indicate why the files were not deleted.
Regards,
hmscott
Thursday, March 8, 2012
Backup permission
Dear All,
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
Ivor
Ivor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
Ivor
Ivor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Backup permission
Dear All,
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
IvorIvor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
--
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
IvorIvor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
--
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Backup permission
Dear All,
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
IvorIvor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
IvorIvor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>
Friday, February 24, 2012
Backup Microsoft SQL 6.0
Hi there
|||
I have a old MS SQL 6.0 Server. I wanted to do a full backup and migrate to MS SQL Express!
My objective for now is to find guides on how to backup MS SQL 6.0. Anyone can enlighten me as I can't find a guide/tips on this area.
MS SQL
Version: 6.0.0.121
Many thanks.
Regards,
makan007
Wow - interesting. To get to SQL Express you need to come from no earlier than 7.0 - upgrade from 7.0 to 9.0 (SQL Server 2005) is the most backward-looking upgrade path that's supported. I discussed how to get to 7.0 with my wife Kimberly Tripp, as it's before my time, and she advises installing 7.0 and then using the migration wizard from 6.0 to 7.0 - if that's supported. One issue you have is that nothing that old is supported at all, so if you run into any issues you'll have a hard time finding support. I know some people in Product Support who were around then - let me know if you hit any problems and I may be able to to get you some limited help.
|||What I meant was I am going to restore my SQL 6.0 data to another server (w/ MS SQL Express). Will there be any issue?|||
Yes - you can't directly attach/restore a 6.0 database to a 2005 server without first upgrading it to 7.0 as there's a big conversion that has to take place between 6.5 and 7.0.
Thanks
Backup Microsoft SQL 6.0
Hi there
|||
I have a old MS SQL 6.0 Server. I wanted to do a full backup and migrate to MS SQL Express!
My objective for now is to find guides on how to backup MS SQL 6.0. Anyone can enlighten me as I can't find a guide/tips on this area.
MS SQL
Version: 6.0.0.121
Many thanks.
Regards,
makan007
Wow - interesting. To get to SQL Express you need to come from no earlier than 7.0 - upgrade from 7.0 to 9.0 (SQL Server 2005) is the most backward-looking upgrade path that's supported. I discussed how to get to 7.0 with my wife Kimberly Tripp, as it's before my time, and she advises installing 7.0 and then using the migration wizard from 6.0 to 7.0 - if that's supported. One issue you have is that nothing that old is supported at all, so if you run into any issues you'll have a hard time finding support. I know some people in Product Support who were around then - let me know if you hit any problems and I may be able to to get you some limited help.
|||What I meant was I am going to restore my SQL 6.0 data to another server (w/ MS SQL Express). Will there be any issue?|||
Yes - you can't directly attach/restore a 6.0 database to a 2005 server without first upgrading it to 7.0 as there's a big conversion that has to take place between 6.5 and 7.0.
Thanks
Sunday, February 19, 2012
Backup Maintenance job failing (-1073548784)
Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
--
Ekrem Ã?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
>>Maintenance Cleanup on Local server connection
>>Cleanup Database Backup files
>>Age: Older than 3 Days
> Error Number:
>>-1073548784
> Error Message:
>>Executing the query "EXECUTE master.dbo.xp_delete_file
>>0,N'',N'',N'2007-12-09T11:00:24'
>>" failed with the following error: "Error executing extended stored
>>procedure: Invalid Parameter". Possible failure reasons: Problems with the
>>query, "ResultSet" property not set correctly, parameters not set
>>correctly, or connection not established correctly.
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem Ã?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem Ã?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > Hi,
> >
> > I have created a maintenance job that fails at cleaning up the old backup
> > files. Messages of the Maintenance job are:
> >
> > Task detail:
> >>Maintenance Cleanup on Local server connection
> >>Cleanup Database Backup files
> >>Age: Older than 3 Days
> > Error Number:
> >>-1073548784
> > Error Message:
> >>Executing the query "EXECUTE master.dbo.xp_delete_file
> >>0,N'',N'',N'2007-12-09T11:00:24'
> >>" failed with the following error: "Error executing extended stored
> >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> >>query, "ResultSet" property not set correctly, parameters not set
> >>correctly, or connection not established correctly.
> >
> > Any ideas why the cleanup is not working, and how to fix this ?
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem Ã?nsoy" wrote:
> > Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> > window. It's empty by default and if you leave it empty then you get this
> > error.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Hen" <Hen@.discussions.microsoft.com> wrote in message
> > news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > > Hi,
> > >
> > > I have created a maintenance job that fails at cleaning up the old backup
> > > files. Messages of the Maintenance job are:
> > >
> > > Task detail:
> > >>Maintenance Cleanup on Local server connection
> > >>Cleanup Database Backup files
> > >>Age: Older than 3 Days
> > > Error Number:
> > >>-1073548784
> > > Error Message:
> > >>Executing the query "EXECUTE master.dbo.xp_delete_file
> > >>0,N'',N'',N'2007-12-09T11:00:24'
> > >>" failed with the following error: "Error executing extended stored
> > >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> > >>query, "ResultSet" property not set correctly, parameters not set
> > >>correctly, or connection not established correctly.
> > >
> > > Any ideas why the cleanup is not working, and how to fix this ?
> >
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
--
Ekrem Ã?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
>>Maintenance Cleanup on Local server connection
>>Cleanup Database Backup files
>>Age: Older than 3 Days
> Error Number:
>>-1073548784
> Error Message:
>>Executing the query "EXECUTE master.dbo.xp_delete_file
>>0,N'',N'',N'2007-12-09T11:00:24'
>>" failed with the following error: "Error executing extended stored
>>procedure: Invalid Parameter". Possible failure reasons: Problems with the
>>query, "ResultSet" property not set correctly, parameters not set
>>correctly, or connection not established correctly.
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem Ã?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem Ã?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > Hi,
> >
> > I have created a maintenance job that fails at cleaning up the old backup
> > files. Messages of the Maintenance job are:
> >
> > Task detail:
> >>Maintenance Cleanup on Local server connection
> >>Cleanup Database Backup files
> >>Age: Older than 3 Days
> > Error Number:
> >>-1073548784
> > Error Message:
> >>Executing the query "EXECUTE master.dbo.xp_delete_file
> >>0,N'',N'',N'2007-12-09T11:00:24'
> >>" failed with the following error: "Error executing extended stored
> >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> >>query, "ResultSet" property not set correctly, parameters not set
> >>correctly, or connection not established correctly.
> >
> > Any ideas why the cleanup is not working, and how to fix this ?
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem Ã?nsoy" wrote:
> > Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> > window. It's empty by default and if you leave it empty then you get this
> > error.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Hen" <Hen@.discussions.microsoft.com> wrote in message
> > news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > > Hi,
> > >
> > > I have created a maintenance job that fails at cleaning up the old backup
> > > files. Messages of the Maintenance job are:
> > >
> > > Task detail:
> > >>Maintenance Cleanup on Local server connection
> > >>Cleanup Database Backup files
> > >>Age: Older than 3 Days
> > > Error Number:
> > >>-1073548784
> > > Error Message:
> > >>Executing the query "EXECUTE master.dbo.xp_delete_file
> > >>0,N'',N'',N'2007-12-09T11:00:24'
> > >>" failed with the following error: "Error executing extended stored
> > >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> > >>query, "ResultSet" property not set correctly, parameters not set
> > >>correctly, or connection not established correctly.
> > >
> > > Any ideas why the cleanup is not working, and how to fix this ?
> >
Backup Maintenance job failing (-1073548784)
Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?
Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?
|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>
|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?
Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?
|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>
|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:
Backup Maintenance job failing (-1073548784)
Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-
09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Inva
lid Parameter". Possible failure reasons: Problems with the query, "ResultSet" prope
rty not set correctly, parameters not set correctly, or connection not established c
orr
ectly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Ta
sk
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenan
ce
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:
>
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-
09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Inva
lid Parameter". Possible failure reasons: Problems with the query, "ResultSet" prope
rty not set correctly, parameters not set correctly, or connection not established c
orr
ectly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Ta
sk
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenan
ce
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:
>
Monday, February 13, 2012
backup job can't remove old .BAK from disk
Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
John
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||Get rid of the maint plan and code it yourself. You only keep 1 copy so
you can just delete it before the backup.
It would be quicker to write the job than to have posted your question
.
I assume you are copying the backup somewhere otherwise if the backup
fails or is corrupt you are left without a backup and can lose the
database.
I try to keep at least 3 backups on disk and will zip them if they are
large.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:%23v0oMCi2EHA.1392@.tk2msftngp13.phx.gbl...
> Get rid of the maint plan and code it yourself. You only keep 1 copy so
> you can just delete it before the backup.
>
Umm, can I recommend deleting it AFTER a successful backup.
Otherwise you risk deleting your one good backup and having the new one
fail. :-)
> It would be quicker to write the job than to have posted your question
>.
> I assume you are copying the backup somewhere otherwise if the backup
> fails or is corrupt you are left without a backup and can lose the
> database.
> I try to keep at least 3 backups on disk and will zip them if they are
> large.
> Nigel Rivett
> www.nigelrivett.net
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
John
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||Get rid of the maint plan and code it yourself. You only keep 1 copy so
you can just delete it before the backup.
It would be quicker to write the job than to have posted your question
.I assume you are copying the backup somewhere otherwise if the backup
fails or is corrupt you are left without a backup and can lose the
database.
I try to keep at least 3 backups on disk and will zip them if they are
large.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:%23v0oMCi2EHA.1392@.tk2msftngp13.phx.gbl...
> Get rid of the maint plan and code it yourself. You only keep 1 copy so
> you can just delete it before the backup.
>
Umm, can I recommend deleting it AFTER a successful backup.
Otherwise you risk deleting your one good backup and having the new one
fail. :-)
> It would be quicker to write the job than to have posted your question
>
.> I assume you are copying the backup somewhere otherwise if the backup
> fails or is corrupt you are left without a backup and can lose the
> database.
> I try to keep at least 3 backups on disk and will zip them if they are
> large.
> Nigel Rivett
> www.nigelrivett.net
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
backup job can't remove old .BAK from disk
Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
JohnCould you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>|||We have a space problem, but more because of our Storage Administration
team.
The default behaviour of the Maintenance Plans is to backup up and then
verify each database in the plan. Only after all backups are completed,
without error, the file delete portion runs for yesterday's backup. So, you
need space for at least 2 Full backups and any retained transaction log
backups in between.
The way we've coded it is to go ahead and set the Maintenance Plans, but
then code another job that runs several times per day checking the Archive
Bit on the prior backup files. Whenever the tape client runs, it will reset
this bit allowing our job to remove it. This reduces our space requirements
to just 1 set of Full backups and intra-day transaction log backups.
Also, we write our backups to dedicated disk partitions that are compressed.
You might also consider something like SQL Litespeed, which further
compresses the database backup files but integrates nicely withing the
Maintenance Plans already created.
Hope this helps.
Sincerely,
Anthony Thomas
"randy" <randydoub@.yahoo.com> wrote in message
news:1beb01c4dc68$9f66cb00$a301280a@.phx.gbl...
I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
JohnCould you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>|||We have a space problem, but more because of our Storage Administration
team.
The default behaviour of the Maintenance Plans is to backup up and then
verify each database in the plan. Only after all backups are completed,
without error, the file delete portion runs for yesterday's backup. So, you
need space for at least 2 Full backups and any retained transaction log
backups in between.
The way we've coded it is to go ahead and set the Maintenance Plans, but
then code another job that runs several times per day checking the Archive
Bit on the prior backup files. Whenever the tape client runs, it will reset
this bit allowing our job to remove it. This reduces our space requirements
to just 1 set of Full backups and intra-day transaction log backups.
Also, we write our backups to dedicated disk partitions that are compressed.
You might also consider something like SQL Litespeed, which further
compresses the database backup files but integrates nicely withing the
Maintenance Plans already created.
Hope this helps.
Sincerely,
Anthony Thomas
"randy" <randydoub@.yahoo.com> wrote in message
news:1beb01c4dc68$9f66cb00$a301280a@.phx.gbl...
I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>
Friday, February 10, 2012
Backup files
Hi,
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.
Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?
thanksWhat I have done here is to have two scheduled maintenance plans for backing up. One that runs every 24 hours that overwrites every time, and a second that appends an incremental every three hours. We then store everything on tape for two weeks.
When you set up the job there is a radio button group to select overwrite or append. If you want to edit your job step change NOINIT to INIT and then the job will overwrite.
Originally posted by Sia
Hi,
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.
Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?
thanks
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.
Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?
thanksWhat I have done here is to have two scheduled maintenance plans for backing up. One that runs every 24 hours that overwrites every time, and a second that appends an incremental every three hours. We then store everything on tape for two weeks.
When you set up the job there is a radio button group to select overwrite or append. If you want to edit your job step change NOINIT to INIT and then the job will overwrite.
Originally posted by Sia
Hi,
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.
Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?
thanks
Subscribe to:
Posts (Atom)