Hi,
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
JerryYou are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copied
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
--
spam999free@.rrohio.com
remove 999 in order to email me
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Thursday, March 29, 2012
Backup Strategy
Hi,
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
Jerry
You are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>
|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copied
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
spam999free@.rrohio.com
remove 999 in order to email me
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
Jerry
You are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>
|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copied
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
spam999free@.rrohio.com
remove 999 in order to email me
Backup Strategy
Hi,
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
JerryYou are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copi
ed
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
spam999free@.rrohio.com
remove 999 in order to email me
I'm finalizing a backup strategy for a production SQL Server with several
databases. I intend to use the same setup for several new production SQL
Servers. Please let me know if there are any "holes" in this plan or if you
have any suggestions.
I've been advised not to use remote backups to shares but instead to backup
locally to disk. The reasons for this:
-performance of the backups
-integrity of the backups
-faster recovery from local backups
I'm going to be using SQL Server to backup locally to disk and then using
Veritas to backup the local backup files to a remote share and then to tape
for offsite storage.
I'll be using Database Maintanence Plans for Full and Transaction Log
backups based on the requirements for each database. The files will be
automatically removed over time (as part of the Maint plan) following the
file copies to the remote share. I may use custom backup scripts in the
future if I require additional functionality not exposed through the Maint
plans. The files for the last one or two backups for each database will be
retained locally.
I'll be adding a seperate drive to each machine to seperate the location of
the backups from the data to help maximize the recoverability of the data.
I could backup directly the array containing the data however a seperate
drive seems like a better option (?)
I'm considering adding two drives in a RAID 1 configuration for the local
backups. This would provide fault tolerance for the backups until the
backups can be copied to the remote share (nightly). Is this a common
practice? Adding two seperate drives in a RAID 1 configuration for each
production SQL Server will increase cost and I'm not sure it is required -
feedback?
Thanks.
JerryYou are taking some risk with this setup...Until the backup files are copied
OFF to another server you will be unable to restore...
I prefer to back up to a file share directly ( yes it is slower), but the
window of risk has been eliminated...
If you must put your backups on the same server, use a different disk
controller than the data disks, mirroring as you suggest will give you
redundancy on the hard drives, but will not account for entire server
failures...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O29MjifZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm finalizing a backup strategy for a production SQL Server with several
> databases. I intend to use the same setup for several new production SQL
> Servers. Please let me know if there are any "holes" in this plan or if
> you have any suggestions.
> I've been advised not to use remote backups to shares but instead to
> backup locally to disk. The reasons for this:
> -performance of the backups
> -integrity of the backups
> -faster recovery from local backups
> I'm going to be using SQL Server to backup locally to disk and then using
> Veritas to backup the local backup files to a remote share and then to
> tape for offsite storage.
> I'll be using Database Maintanence Plans for Full and Transaction Log
> backups based on the requirements for each database. The files will be
> automatically removed over time (as part of the Maint plan) following the
> file copies to the remote share. I may use custom backup scripts in the
> future if I require additional functionality not exposed through the Maint
> plans. The files for the last one or two backups for each database will
> be retained locally.
> I'll be adding a seperate drive to each machine to seperate the location
> of the backups from the data to help maximize the recoverability of the
> data. I could backup directly the array containing the data however a
> seperate drive seems like a better option (?)
> I'm considering adding two drives in a RAID 1 configuration for the local
> backups. This would provide fault tolerance for the backups until the
> backups can be copied to the remote share (nightly). Is this a common
> practice? Adding two seperate drives in a RAID 1 configuration for each
> production SQL Server will increase cost and I'm not sure it is required -
> feedback?
> Thanks.
> Jerry
>|||In article <#QPCAqiZFHA.3220@.TK2MSFTNGP14.phx.gbl>,
wayne.nospam.snyder@.mariner-usa.com says...
> You are taking some risk with this setup...Until the backup files are copi
ed
> OFF to another server you will be unable to restore...
> I prefer to back up to a file share directly ( yes it is slower), but the
> window of risk has been eliminated...
> If you must put your backups on the same server, use a different disk
> controller than the data disks, mirroring as you suggest will give you
> redundancy on the hard drives, but will not account for entire server
> failures...
The OP was talking about backups of SQL DB's not the server. I always
backup to a local drive (separate physical drive) and then backup to
tape. The backup to tape of a SQL server DB is slow and can present a
number of issues - the backup to disk is quick, gives an on-line
accessible backup, does not load the network, and can be copied to tape
quickly at any time.
In a production environment it's often a good idea to have a local
backup on the server in case you need to do a restore quickly. The
likelihood of a drive failure is slimmer than a network problem and the
performance is much higher.
--
spam999free@.rrohio.com
remove 999 in order to email me
Tuesday, March 27, 2012
BACKUP SQL SERVER DATABASES
I have setup a backup device that backs up a databases daily but i want to
overwrite the data weekly right now its has two months of data in the device.
How do i set it to overwrite
Use the INIT option of the BACKUP command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lyle" <lyle@.discussions.microsoft.com> wrote in message
news:683AAFF2-4F8C-44F3-A195-F85A9881C047@.microsoft.com...
> I have setup a backup device that backs up a databases daily but i want to
> overwrite the data weekly right now its has two months of data in the device.
> How do i set it to overwrite
overwrite the data weekly right now its has two months of data in the device.
How do i set it to overwrite
Use the INIT option of the BACKUP command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lyle" <lyle@.discussions.microsoft.com> wrote in message
news:683AAFF2-4F8C-44F3-A195-F85A9881C047@.microsoft.com...
> I have setup a backup device that backs up a databases daily but i want to
> overwrite the data weekly right now its has two months of data in the device.
> How do i set it to overwrite
Sunday, March 25, 2012
Backup Setup
I am trying to setup the following and was hoping if someone could tell me a more efficient way to set this up. I am not using the maintenance plan or wizard cause Ive had nothing but issues with them:
1. Create a backup device for each day of the week (Sunday - Saturday)
2. Create a job using T-SQL to run maintenance (Rebuild Indexes) and then at the conclusion of the maintenance run a full backup every night at midnight. I would need once job for each day of the week and store them on the proper backup device.
BACKUP DATABASE [DBS1] TO [Sunday Backup] WITH NOFORMAT, NOINIT, NAME = N'DBS1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GO
3. Create a job using T-SQL for transaction log backups to run every hour and store them in the proper backup device. I would need one job for each day of the week.
BACKUP LOG [DBS1 [Sunday Backup] WITH NOFORMAT, NOINIT,
NAME = N'DBS1-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1"' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GO
This method would give me 7 backups devices and 14 jobs but at least things would be organized nice and neat. Im sure there is a better way to do this so I am looking for some help. Any help would be much appreciated.
Thanks
One simplification would be to have the script build the name of the backup device based on the current day of the week. This way you have one backup job and one log backup job that each run every day.
The following would be an example of building the backup device name:
DECLARE @.BackupDev AS NVARCHAR(30)
select @.BackupDev = DATENAME(dw,GETDATE()) + N'_BACKUP_DEV'
Thursday, March 22, 2012
Backup serious appending problem
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
LauraYou have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>sql
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
LauraYou have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>sql
Backup serious appending problem
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
You have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
You have to tell us how the backups are done. Are they happening from a
Maintenance plan job or plain BACKUP commands? You can find this by looking
at the job step.
Anyway, you have to use the WITH INIT option for the BACKUP command, if you
don't want to append.
Also, you should think about moving the database backups to a different
machine, to be able to recover from a failure on the SQL Server box.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"laura" <lauras@.microtechsystems.com> wrote in message
news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
We are running Small Business Server 2003 and using a SQL
database .. At some point in the setup of this, another
person apparently setup from 1 to 3 backup jobs .. the
problem is that there have been 3 backup jobs running
nightly and that 2 of the 3 are appending to the previous
backup.. this resulted in our hard drive filling up almost
to the crash point before we realized there was a
problem .. I don't know where to look for this ..
To recover space on the server we had deleted all of the
backups which are in the c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP folder...
I don't work with SQL much and this has become my problem
to fix .. any help would be appreciated
Thank you
Laura
|||Thank you much for the reply..
As I said I do not work with SQL except to troubleshoot
when there is a problem that no one else is trying to
solve, but with your suggestion I did find out 1. What a
job step is
2. Where to look for this information under the
Management ->SQL Server Agent -> Jobs (at least I think I
am looking in the right place..
3. Now I will try to find why there are 4 Backup jobs
listed in this category..
Any other info you can send would be appreciated..
Again, Thank you
Laura
>--Original Message--
>You have to tell us how the backups are done. Are they
happening from a
>Maintenance plan job or plain BACKUP commands? You can
find this by looking
>at the job step.
>Anyway, you have to use the WITH INIT option for the
BACKUP command, if you
>don't want to append.
>Also, you should think about moving the database backups
to a different
>machine, to be able to recover from a failure on the SQL
Server box.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"laura" <lauras@.microtechsystems.com> wrote in message
>news:0a7601c48b78$202e7b30$a301280a@.phx.gbl...
>We are running Small Business Server 2003 and using a SQL
>database .. At some point in the setup of this, another
>person apparently setup from 1 to 3 backup jobs .. the
>problem is that there have been 3 backup jobs running
>nightly and that 2 of the 3 are appending to the previous
>backup.. this resulted in our hard drive filling up almost
>to the crash point before we realized there was a
>problem .. I don't know where to look for this ..
>To recover space on the server we had deleted all of the
>backups which are in the c:\Program Files\Microsoft SQL
>Server\MSSQL\BACKUP folder...
>I don't work with SQL much and this has become my problem
>to fix .. any help would be appreciated
>Thank you
>Laura
>
>.
>
Sunday, March 11, 2012
Backup Priority level
I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.
Thank you and yes, you are correct...it's noon.
We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.
One alternative you may want to investigate is to do a differntial
backup. That may tax your server less.
Argh, you're really stressing that machine.
Yeah, I agree. Unfortunately, it's not in the budget for us to expand
at this time. I will say this though, to me (and my mere two years
experience) it seems to be running very stable. Since we only run with
10 users, I've set SQL Server's memory allocation to 600MB and set
SBSMONITORING to 100MB, Exchange is being kind and staying between 575
- 600 MB and Symantec is occupying another 400 - 500. All together
I've still got about 800 - 1,000 MB of free RAM. My server report
shows an average of 18% busy disk time and both CPU's (shown as 4)
averaging about 8-10% use. Again, I only assume this is good because
I'm new to IT. I just got my degree two years ago. Would you say
our server is doing 'Good', 'Satisfactory' or 'Poor'?
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.
>
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
I suddenly have a reason for concern...
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
>
I suddenly have a reason for concern...
>
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.
Depends on your network of course but it doesn't surprise me that
sending a large file over a network would take longer than the local
backup, especially during the day. Have you considered getting a tape
drive to do your backups - they're not all that expensive, certainly a
lot cheaper than losing your data would be :)
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky. You would need to play with it on a test
box, so that you are confident with the process when the disaster
strikes.
>
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky.
Not tricky at all. Restore the full backup, then restore the diff.
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.
Thanks,
Gabegabe101 (gabe101@.gmail.com) writes:
Quote:
Originally Posted by
I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.
It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.
Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
>
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.
Thank you and yes, you are correct...it's noon.
Quote:
Originally Posted by
It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.
We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).
Quote:
Originally Posted by
Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.
Thanks Erland.|||gabe101 (gabe101@.gmail.com) writes:
Quote:
Originally Posted by
We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).
One alternative you may want to investigate is to do a differntial
backup. That may tax your server less.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"gabe101" <gabe101@.gmail.comwrote in message
news:1166551052.960302.151960@.80g2000cwy.googlegro ups.com...
Quote:
Originally Posted by
Erland Sommarskog wrote:
>
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.
Argh, you're really stressing that machine.
Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
A backup on the same machine won't do you much good if say the RAID
controller fails.
Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.
Quote:
Originally Posted by
>
Thanks Erland.
>
Quote:
Originally Posted by
Argh, you're really stressing that machine.
Yeah, I agree. Unfortunately, it's not in the budget for us to expand
at this time. I will say this though, to me (and my mere two years
experience) it seems to be running very stable. Since we only run with
10 users, I've set SQL Server's memory allocation to 600MB and set
SBSMONITORING to 100MB, Exchange is being kind and staying between 575
- 600 MB and Symantec is occupying another 400 - 500. All together
I've still got about 800 - 1,000 MB of free RAM. My server report
shows an average of 18% busy disk time and both CPU's (shown as 4)
averaging about 8-10% use. Again, I only assume this is good because
I'm new to IT. I just got my degree two years ago. Would you say
our server is doing 'Good', 'Satisfactory' or 'Poor'?
Quote:
Originally Posted by
Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
Quote:
Originally Posted by
Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.
My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.
Thank you both for your comments and if you have anything more to say,
I'm still listening (and learning).|||Yes, this is a response to my own response...
;-)
gabe101 wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
A backup on the same machine won't do you much good if say the RAID
controller fails.
>
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
I suddenly have a reason for concern...
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.|||gabe101 wrote:
Quote:
Originally Posted by
Yes, this is a response to my own response...
>
;-)
>
gabe101 wrote:
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.
>
I suddenly have a reason for concern...
>
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.
Depends on your network of course but it doesn't surprise me that
sending a large file over a network would take longer than the local
backup, especially during the day. Have you considered getting a tape
drive to do your backups - they're not all that expensive, certainly a
lot cheaper than losing your data would be :)
Also, as someone else mentioned, consider doing a differential backup
during the day rather than the full backup. It's just one more step if
you need to restore, and it should be much smaller and faster than your
full backup.|||gabe101 (gabe101@.gmail.com) writes:
Quote:
Originally Posted by
My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky. You would need to play with it on a test
box, so that you are confident with the process when the disaster
strikes.
A differential backup only includs the changed pages since the last
full backup, so it would be a lot smaller and thus tax the system
less.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
gabe101 (gabe101@.gmail.com) writes:
Quote:
Originally Posted by
My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.
>
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky.
Not tricky at all. Restore the full backup, then restore the diff.
backup Plan SQL 2000
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
YOu could use a network share using the UNC path, but if you want to make sure that the data is transfered correctly, I would stick saving the backup file to a local drive and copying it over afterwards using a restartable copying mechanism like robocopy or xcopy.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||You don't need to do this if the SQL Server is running under a domain account. You simply need to ensure the SQL Server account has rights to the share you want to save your backups to. Then you can specify the network path to the share when creating the backup job/maintenance plan.backup Plan SQL 2000
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
YOu could use a network share using the UNC path, but if you want to make sure that the data is transfered correctly, I would stick saving the backup file to a local drive and copying it over afterwards using a restartable copying mechanism like robocopy or xcopy.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||You don't need to do this if the SQL Server is running under a domain account. You simply need to ensure the SQL Server account has rights to the share you want to save your backups to. Then you can specify the network path to the share when creating the backup job/maintenance plan.Sunday, February 19, 2012
Backup Maintenance Plan - Best Practices for SQL 2000
I was once told that I was not to use the GUI to setup a maintenance plan.What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap.
Has anyone ever heard of this or is this an old wise tale told by only one person?
I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!
It's basically a tradeoff of ease of use vs level of control, and that last increment of performance.
The GUI will produce a workable maintenance plan to do what you tell it to.
It will not be optimized to your particular situation.
So, by taking more control and scripting the actions you can arrive at a configuration which is more tuned to your environment, and therefore more efficient.
On the other hand, you can use the GUI and be done with it.
You need to make those tradeoffs for your own situation.
Backup Maintenance Plan
I am using SQL 2005 std in a cluster configuration.
The problem is running a backup using the maint. plan. I can setup the plan
a schedule the job, however, when the job runs the message I get is
Date 5/12/2006 8:39:01 AM
Log Job History (Maint Plan)
Step ID 1
Server <Server Name>
Job Name Maint Plan
Step Name Subplan
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <domain name>\sqlsvc. The package execution failed. The
step failed.
I have extended logging turned on but this is the only message I get. I got
this to run once, and that was the first time I setup the plan, I ran it by
right clicking and start job. I have SSIS installed and the SQL agent and
DTC are running.
Thanks for any help.
BrandonDatabase backup errors are logged in the SQL Server Log. Look there for more
information. If the backup was succesfull the information should be there to
o.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Brandon" wrote:
> I am using SQL 2005 std in a cluster configuration.
> The problem is running a backup using the maint. plan. I can setup the pl
an
> a schedule the job, however, when the job runs the message I get is
> Date 5/12/2006 8:39:01 AM
> Log Job History (Maint Plan)
> Step ID 1
> Server <Server Name>
> Job Name Maint Plan
> Step Name Subplan
> Duration 00:00:01
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: <domain name>\sqlsvc. The package execution failed. The
> step failed.
> I have extended logging turned on but this is the only message I get. I g
ot
> this to run once, and that was the first time I setup the plan, I ran it b
y
> right clicking and start job. I have SSIS installed and the SQL agent and
> DTC are running.
> Thanks for any help.
> Brandon|||I have checked the SQL server log but does not say anything. The only log i
s
in the job log.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Database backup errors are logged in the SQL Server Log. Look there for mo
re
> information. If the backup was succesfull the information should be there
too.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Brandon" wrote:
>|||I found out the issue. You can not create a manitenance plan from a
workstation, you have to be logged into the server and create it there. Eve
n
more if you have a cluster server the active server needs to be the creator.
If you need to edit the plan it also has to be done from the active server.
If you try to edit it from a workstation it will break.
Brandon
"Brandon" wrote:
[vbcol=seagreen]
> I have checked the SQL server log but does not say anything. The only log
is
> in the job log.
> "Ben Nevarez" wrote:
>
The problem is running a backup using the maint. plan. I can setup the plan
a schedule the job, however, when the job runs the message I get is
Date 5/12/2006 8:39:01 AM
Log Job History (Maint Plan)
Step ID 1
Server <Server Name>
Job Name Maint Plan
Step Name Subplan
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <domain name>\sqlsvc. The package execution failed. The
step failed.
I have extended logging turned on but this is the only message I get. I got
this to run once, and that was the first time I setup the plan, I ran it by
right clicking and start job. I have SSIS installed and the SQL agent and
DTC are running.
Thanks for any help.
BrandonDatabase backup errors are logged in the SQL Server Log. Look there for more
information. If the backup was succesfull the information should be there to
o.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Brandon" wrote:
> I am using SQL 2005 std in a cluster configuration.
> The problem is running a backup using the maint. plan. I can setup the pl
an
> a schedule the job, however, when the job runs the message I get is
> Date 5/12/2006 8:39:01 AM
> Log Job History (Maint Plan)
> Step ID 1
> Server <Server Name>
> Job Name Maint Plan
> Step Name Subplan
> Duration 00:00:01
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: <domain name>\sqlsvc. The package execution failed. The
> step failed.
> I have extended logging turned on but this is the only message I get. I g
ot
> this to run once, and that was the first time I setup the plan, I ran it b
y
> right clicking and start job. I have SSIS installed and the SQL agent and
> DTC are running.
> Thanks for any help.
> Brandon|||I have checked the SQL server log but does not say anything. The only log i
s
in the job log.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Database backup errors are logged in the SQL Server Log. Look there for mo
re
> information. If the backup was succesfull the information should be there
too.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Brandon" wrote:
>|||I found out the issue. You can not create a manitenance plan from a
workstation, you have to be logged into the server and create it there. Eve
n
more if you have a cluster server the active server needs to be the creator.
If you need to edit the plan it also has to be done from the active server.
If you try to edit it from a workstation it will break.
Brandon
"Brandon" wrote:
[vbcol=seagreen]
> I have checked the SQL server log but does not say anything. The only log
is
> in the job log.
> "Ben Nevarez" wrote:
>
Backup Maintenance Plan
I am using SQL 2005 std in a cluster configuration.
The problem is running a backup using the maint. plan. I can setup the plan
a schedule the job, however, when the job runs the message I get is
Date 5/12/2006 8:39:01 AM
Log Job History (Maint Plan)
Step ID 1
Server <Server Name>
Job Name Maint Plan
Step Name Subplan
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <domain name>\sqlsvc. The package execution failed. The
step failed.
I have extended logging turned on but this is the only message I get. I got
this to run once, and that was the first time I setup the plan, I ran it by
right clicking and start job. I have SSIS installed and the SQL agent and
DTC are running.
Thanks for any help.
BrandonDatabase backup errors are logged in the SQL Server Log. Look there for more
information. If the backup was succesfull the information should be there too.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Brandon" wrote:
> I am using SQL 2005 std in a cluster configuration.
> The problem is running a backup using the maint. plan. I can setup the plan
> a schedule the job, however, when the job runs the message I get is
> Date 5/12/2006 8:39:01 AM
> Log Job History (Maint Plan)
> Step ID 1
> Server <Server Name>
> Job Name Maint Plan
> Step Name Subplan
> Duration 00:00:01
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: <domain name>\sqlsvc. The package execution failed. The
> step failed.
> I have extended logging turned on but this is the only message I get. I got
> this to run once, and that was the first time I setup the plan, I ran it by
> right clicking and start job. I have SSIS installed and the SQL agent and
> DTC are running.
> Thanks for any help.
> Brandon|||I have checked the SQL server log but does not say anything. The only log is
in the job log.
"Ben Nevarez" wrote:
> Database backup errors are logged in the SQL Server Log. Look there for more
> information. If the backup was succesfull the information should be there too.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Brandon" wrote:
> > I am using SQL 2005 std in a cluster configuration.
> >
> > The problem is running a backup using the maint. plan. I can setup the plan
> > a schedule the job, however, when the job runs the message I get is
> >
> > Date 5/12/2006 8:39:01 AM
> > Log Job History (Maint Plan)
> >
> > Step ID 1
> > Server <Server Name>
> > Job Name Maint Plan
> > Step Name Subplan
> > Duration 00:00:01
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > Executed as user: <domain name>\sqlsvc. The package execution failed. The
> > step failed.
> >
> > I have extended logging turned on but this is the only message I get. I got
> > this to run once, and that was the first time I setup the plan, I ran it by
> > right clicking and start job. I have SSIS installed and the SQL agent and
> > DTC are running.
> >
> > Thanks for any help.
> > Brandon|||I found out the issue. You can not create a manitenance plan from a
workstation, you have to be logged into the server and create it there. Even
more if you have a cluster server the active server needs to be the creator.
If you need to edit the plan it also has to be done from the active server.
If you try to edit it from a workstation it will break.
Brandon
"Brandon" wrote:
> I have checked the SQL server log but does not say anything. The only log is
> in the job log.
> "Ben Nevarez" wrote:
> >
> > Database backup errors are logged in the SQL Server Log. Look there for more
> > information. If the backup was succesfull the information should be there too.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Brandon" wrote:
> >
> > > I am using SQL 2005 std in a cluster configuration.
> > >
> > > The problem is running a backup using the maint. plan. I can setup the plan
> > > a schedule the job, however, when the job runs the message I get is
> > >
> > > Date 5/12/2006 8:39:01 AM
> > > Log Job History (Maint Plan)
> > >
> > > Step ID 1
> > > Server <Server Name>
> > > Job Name Maint Plan
> > > Step Name Subplan
> > > Duration 00:00:01
> > > Sql Severity 0
> > > Sql Message ID 0
> > > Operator Emailed
> > > Operator Net sent
> > > Operator Paged
> > > Retries Attempted 0
> > >
> > > Message
> > > Executed as user: <domain name>\sqlsvc. The package execution failed. The
> > > step failed.
> > >
> > > I have extended logging turned on but this is the only message I get. I got
> > > this to run once, and that was the first time I setup the plan, I ran it by
> > > right clicking and start job. I have SSIS installed and the SQL agent and
> > > DTC are running.
> > >
> > > Thanks for any help.
> > > Brandon
The problem is running a backup using the maint. plan. I can setup the plan
a schedule the job, however, when the job runs the message I get is
Date 5/12/2006 8:39:01 AM
Log Job History (Maint Plan)
Step ID 1
Server <Server Name>
Job Name Maint Plan
Step Name Subplan
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <domain name>\sqlsvc. The package execution failed. The
step failed.
I have extended logging turned on but this is the only message I get. I got
this to run once, and that was the first time I setup the plan, I ran it by
right clicking and start job. I have SSIS installed and the SQL agent and
DTC are running.
Thanks for any help.
BrandonDatabase backup errors are logged in the SQL Server Log. Look there for more
information. If the backup was succesfull the information should be there too.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Brandon" wrote:
> I am using SQL 2005 std in a cluster configuration.
> The problem is running a backup using the maint. plan. I can setup the plan
> a schedule the job, however, when the job runs the message I get is
> Date 5/12/2006 8:39:01 AM
> Log Job History (Maint Plan)
> Step ID 1
> Server <Server Name>
> Job Name Maint Plan
> Step Name Subplan
> Duration 00:00:01
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> Executed as user: <domain name>\sqlsvc. The package execution failed. The
> step failed.
> I have extended logging turned on but this is the only message I get. I got
> this to run once, and that was the first time I setup the plan, I ran it by
> right clicking and start job. I have SSIS installed and the SQL agent and
> DTC are running.
> Thanks for any help.
> Brandon|||I have checked the SQL server log but does not say anything. The only log is
in the job log.
"Ben Nevarez" wrote:
> Database backup errors are logged in the SQL Server Log. Look there for more
> information. If the backup was succesfull the information should be there too.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Brandon" wrote:
> > I am using SQL 2005 std in a cluster configuration.
> >
> > The problem is running a backup using the maint. plan. I can setup the plan
> > a schedule the job, however, when the job runs the message I get is
> >
> > Date 5/12/2006 8:39:01 AM
> > Log Job History (Maint Plan)
> >
> > Step ID 1
> > Server <Server Name>
> > Job Name Maint Plan
> > Step Name Subplan
> > Duration 00:00:01
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > Executed as user: <domain name>\sqlsvc. The package execution failed. The
> > step failed.
> >
> > I have extended logging turned on but this is the only message I get. I got
> > this to run once, and that was the first time I setup the plan, I ran it by
> > right clicking and start job. I have SSIS installed and the SQL agent and
> > DTC are running.
> >
> > Thanks for any help.
> > Brandon|||I found out the issue. You can not create a manitenance plan from a
workstation, you have to be logged into the server and create it there. Even
more if you have a cluster server the active server needs to be the creator.
If you need to edit the plan it also has to be done from the active server.
If you try to edit it from a workstation it will break.
Brandon
"Brandon" wrote:
> I have checked the SQL server log but does not say anything. The only log is
> in the job log.
> "Ben Nevarez" wrote:
> >
> > Database backup errors are logged in the SQL Server Log. Look there for more
> > information. If the backup was succesfull the information should be there too.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Brandon" wrote:
> >
> > > I am using SQL 2005 std in a cluster configuration.
> > >
> > > The problem is running a backup using the maint. plan. I can setup the plan
> > > a schedule the job, however, when the job runs the message I get is
> > >
> > > Date 5/12/2006 8:39:01 AM
> > > Log Job History (Maint Plan)
> > >
> > > Step ID 1
> > > Server <Server Name>
> > > Job Name Maint Plan
> > > Step Name Subplan
> > > Duration 00:00:01
> > > Sql Severity 0
> > > Sql Message ID 0
> > > Operator Emailed
> > > Operator Net sent
> > > Operator Paged
> > > Retries Attempted 0
> > >
> > > Message
> > > Executed as user: <domain name>\sqlsvc. The package execution failed. The
> > > step failed.
> > >
> > > I have extended logging turned on but this is the only message I get. I got
> > > this to run once, and that was the first time I setup the plan, I ran it by
> > > right clicking and start job. I have SSIS installed and the SQL agent and
> > > DTC are running.
> > >
> > > Thanks for any help.
> > > Brandon
Backup log With Truncate Only (Log Shipping)
SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.c...og_shipping.asp
Which truncates the log before doing the database backup then does the backu
p
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db back
up
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would yo
u
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.c...og_shipping.asp
Which truncates the log before doing the database backup then does the backu
p
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db back
up
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would yo
u
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
quote:
>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.c...og_shipping.asp
>Which truncates the log before doing the database backup then does the back
up
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automati
c
>truncate
>after backup my initial thought is to drop the log backup before the db bac
kup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.
Backup log With Truncate Only (Log Shipping)
SQL Server 2000 Standard Edition running on Win2k
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Which truncates the log before doing the database backup then does the backup
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db backup
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would you
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.com/sql_server_log_shipping.asp
>Which truncates the log before doing the database backup then does the backup
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automatic
>truncate
>after backup my initial thought is to drop the log backup before the db backup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.
I've setup log shipping according to an article I found here.
http://www.sql-server-performance.com/sql_server_log_shipping.asp
Which truncates the log before doing the database backup then does the backup
and
subsequent log backups without truncating. Of course the truncate causes an
error
to be shown in the event viewer.
Since books describes the default behavior of log backups to be an automatic
truncate
after backup my initial thought is to drop the log backup before the db backup
and just let
the truncate happen automatically with the normal log shipping backups.
Remove This -- BACKUP LOG database_name WITH TRUNCATE_ONLY
Remove This -- WAITFOR DELAY '00:00:05'
BACKUP DATABASE database_name TO database_name_backup_device WITH INIT
Remove the NO_TRUNCATE and let the log truncate automatically.
BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE
They do mention in the article that you don't want to truncate the log after
you do
your normal log shipping backups because you might need them later. Would you
possibly
need them later? I archive all my log and db backups each time they run.Brad <seveni7@.yahoo.com> wrote:
>SQL Server 2000 Standard Edition running on Win2k
>I've setup log shipping according to an article I found here.
>http://www.sql-server-performance.com/sql_server_log_shipping.asp
>Which truncates the log before doing the database backup then does the backup
>and
>subsequent log backups without truncating. Of course the truncate causes an
>error
>to be shown in the event viewer.
>Since books describes the default behavior of log backups to be an automatic
>truncate
>after backup my initial thought is to drop the log backup before the db backup
>and just let
>the truncate happen automatically with the normal log shipping backups.
>
My bad!
Found it on MS Knowledge Base Article - 818202
Just a warning.
Friday, February 10, 2012
Backup hangs in wait-state MSSEARCH
I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.
The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.
When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.
I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.
The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.
Kim,
This is very likely a bug. I suggest you contact Microsoft support.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.
There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.
The quick solution is to cycle the MSSEARCH service, which should get backups moving.
If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.
|||Hi Kevin,
We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.
The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.
|||I'm assuming that you have fulltext catalogs in your database?
So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.
If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com
Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}
Thanks for the all the good will shown in these forums.
Kim Brandt Jensen
Backup hangs in wait-state MSSEARCH
I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.
The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.
When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.
I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.
The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.
Kim,
This is very likely a bug. I suggest you contact Microsoft support.
Regards,
Matt Hollingsworth
Sr. Program Manager
Microsoft SQL Server
|||That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.
There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.
The quick solution is to cycle the MSSEARCH service, which should get backups moving.
If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.
|||Hi Kevin,
We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.
The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.
|||I'm assuming that you have fulltext catalogs in your database?
So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.
If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com
Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}
Thanks for the all the good will shown in these forums.
Kim Brandt Jensen
Subscribe to:
Posts (Atom)