backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your helpHi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:
> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
> > I appologize if this question seems too basic but I need some advice
> >
> > On one of the SQL servers I manage, I do backups on disk. I need to keep
a
> > historyof backups for at least several weeks.
> > I currently do full backups twice a week, differential backup on days
that I
> > don't do full backup and transaction log backup every 10 minutes (this
is
> > for a high traffic DB).
> > If I replace the current backup with every new full backup, then I can't
> > keep a history of backups but if I keep on appending, my backup files
get
> > huge.
> > How can I configure my backups to keep only, let say, 3 weeks of
backups?
> > Can that be done all in SQL server or will I have to write scripts
outside
> > of SQL server to archive backup files?
> >
> > Thanks for your help
> >
> >
> >|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
> > Hi, you can modify the time expiration of your backups and configure the
> > files like non overwrite.
> >
> > Bye
> >
> >
> > "Benoit Martin" wrote:
> >
> > > I appologize if this question seems too basic but I need some advice
> > >
> > > On one of the SQL servers I manage, I do backups on disk. I need to
keep
> a
> > > historyof backups for at least several weeks.
> > > I currently do full backups twice a week, differential backup on days
> that I
> > > don't do full backup and transaction log backup every 10 minutes (this
> is
> > > for a high traffic DB).
> > > If I replace the current backup with every new full backup, then I
can't
> > > keep a history of backups but if I keep on appending, my backup files
> get
> > > huge.
> > > How can I configure my backups to keep only, let say, 3 weeks of
> backups?
> > > Can that be done all in SQL server or will I have to write scripts
> outside
> > > of SQL server to archive backup files?
> > >
> > > Thanks for your help
> > >
> > >
> > >
>

backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your help
Hi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:

> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>
|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...[vbcol=seagreen]
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
a[vbcol=seagreen]
that I[vbcol=seagreen]
is[vbcol=seagreen]
get[vbcol=seagreen]
backups?[vbcol=seagreen]
outside[vbcol=seagreen]
|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
keep[vbcol=seagreen]
> a
> that I
> is
can't
> get
> backups?
> outside
>

backup strategy question

I appologize if this question seems too basic but I need some advice
On one of the SQL servers I manage, I do backups on disk. I need to keep a
historyof backups for at least several weeks.
I currently do full backups twice a week, differential backup on days that I
don't do full backup and transaction log backup every 10 minutes (this is
for a high traffic DB).
If I replace the current backup with every new full backup, then I can't
keep a history of backups but if I keep on appending, my backup files get
huge.
How can I configure my backups to keep only, let say, 3 weeks of backups?
Can that be done all in SQL server or will I have to write scripts outside
of SQL server to archive backup files?
Thanks for your helpHi, you can modify the time expiration of your backups and configure the
files like non overwrite.
Bye
"Benoit Martin" wrote:

> I appologize if this question seems too basic but I need some advice
> On one of the SQL servers I manage, I do backups on disk. I need to keep a
> historyof backups for at least several weeks.
> I currently do full backups twice a week, differential backup on days that
I
> don't do full backup and transaction log backup every 10 minutes (this is
> for a high traffic DB).
> If I replace the current backup with every new full backup, then I can't
> keep a history of backups but if I keep on appending, my backup files get
> huge.
> How can I configure my backups to keep only, let say, 3 weeks of backups?
> Can that be done all in SQL server or will I have to write scripts outside
> of SQL server to archive backup files?
> Thanks for your help
>
>|||Thanks John,
when you talk about modifying the time of expiration of the backup, are you
talking about the "Backup set will expire:" section under the "options" tab
when adding a new backup?
If that's the case, this option is available only when I choose to overwrite
the existing media which go against your second advice which was not to
overwrite.
Am I missing something?
PS: in case it matters, I forgot to mention that I am using SQL Server 2000
Thanks
"John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in message
news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...[vbcol=seagreen]
> Hi, you can modify the time expiration of your backups and configure the
> files like non overwrite.
> Bye
>
> "Benoit Martin" wrote:
>
a[vbcol=seagreen]
that I[vbcol=seagreen]
is[vbcol=seagreen]
get[vbcol=seagreen]
backups?[vbcol=seagreen]
outside[vbcol=seagreen]|||You can not use the expiration dates in the fashion that you want. When you
append backups to the same device (file) you have an all or nothing
situation when it comes to deleting old backups. You can not delete or
overwrite individual backups from a single device. You are best served by
creating a new backup file each time and either deleting them by their
timestamp at the file level or by naming them with a convention that will
allow you to tell when they expire. You should seriously consider using SQL
LiteSpeed for your backups. It will not only speed them up but same a lot
of disk space. http://www.imceda.com/
But here is some code examples to backup to a different file each night and
some code to remove older backups.
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Benoit Martin" <benoit@.digitalmediums.com> wrote in message
news:e6sG0$ZgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Thanks John,
> when you talk about modifying the time of expiration of the backup, are
you
> talking about the "Backup set will expire:" section under the "options"
tab
> when adding a new backup?
> If that's the case, this option is available only when I choose to
overwrite
> the existing media which go against your second advice which was not to
> overwrite.
> Am I missing something?
> PS: in case it matters, I forgot to mention that I am using SQL Server
2000
> Thanks
> "John Bocachica" <John Bocachica@.discussions.microsoft.com> wrote in
message
> news:EDF7465B-0A19-4D6A-B1AE-C51358A24F4E@.microsoft.com...
keep[vbcol=seagreen]
> a
> that I
> is
can't[vbcol=seagreen]
> get
> backups?
> outside
>

backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours
,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing th
e
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirrori
ng
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principa
l
> server, a) full backup once a day, b) differential backup once every 4 hou
rs,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generatio
ns of the backups, and
whether to only have those on tape and also disk will influence this. I assu
me you are aware of the
INIT and NOINIT options.

> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had t
he
> same 3 agent jobs on the mirroring server, the jobs would fail? But what i
f
> the principal server fails over, and mirroring server becomes the principa
l
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the
mirroring catalog view
whether that server is primary or not. If not primary, exit with success, el
se do the backup.

> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing
the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine th
e backup came. This
would work in faviour for using the same backup devices.

> 4. Last question, not particular related to backup Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to
be local, SAN or ISCSI.

> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>

backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing the
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup :) Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirroring
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principal
> server, a) full backup once a day, b) differential backup once every 4 hours,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generations of the backups, and
whether to only have those on tape and also disk will influence this. I assume you are aware of the
INIT and NOINIT options.
> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had the
> same 3 agent jobs on the mirroring server, the jobs would fail? But what if
> the principal server fails over, and mirroring server becomes the principal
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the mirroring catalog view
whether that server is primary or not. If not primary, exit with success, else do the backup.
> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine the backup came. This
would work in faviour for using the same backup devices.
> 4. Last question, not particular related to backup :) Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to be local, SAN or ISCSI.
> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
>> Hi guys.
>> We have a DB system that is relatively small and transactions are not
>> very
>> big, but very important, losing data will be very costly to the business.
>> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
>> to
>> implement the DB mirroring with the High Protection mode.
>> I have some questions regarding backup and restore.
>> Suppose we utilize 3 machines, A is the principal server, B is the
>> mirroring
>> server, and C is the file server on which the backup files are stored.
>> My questions are:
>> 1. Currently I have created 3 SQL Server Agent jobs to backup the
>> principal
>> server, a) full backup once a day, b) differential backup once every 4
>> hours,
>> c) transaction log back once every 15 minutes. The question is: should I
>> change the backup file (device) every day? Or I can use one backup
>> file(device) for all the backups day in and day out?
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
>> 2. How do I backup the mirroring server? I think I can not do anything on
>> the mirroring server when it is in the Mirroring/Sync mode. And if I had
>> the
>> same 3 agent jobs on the mirroring server, the jobs would fail? But what
>> if
>> the principal server fails over, and mirroring server becomes the
>> principal
>> server, do I have to create the backup agent jobs after failover?
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
>> 3. When creating the mirroring server backup, can I reuse the same backup
>> file name(s) that I used on the principal server? or I better off storing
>> the
>> backup file from the mirroring server on a different location?
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
>> 4. Last question, not particular related to backup :) Should I store the
>> .MDF/.LDF file for the principal server and/or mirror server on machine
>> C?
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>> Thanks a lot!
>> Wenbiao
>

Backup SQL Server Failures

I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).

Please help!!!

BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.|||Thanks for your reply.

I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.|||What is the SP[service pack] level on the SQL Server & OS?|||Windows 2000 Server SP3

SQL Server 2000 SP3 (Standard Edition not Enterprise)|||Then how about RECOVERY MODEL on these DBs.|||All of the user databases on this server have the Recovery Model option set to FULL.|||Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.|||Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)|||As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

For a test try to redesing maint.plan for each database and see the results.|||The drive has 21.0 GB used. It has 38.7 GB free.

There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

Email database = 263.88 mb (data and log)
ESP database = 316.25 mb (data and log)
WebLogs database = 5,069.76 gb (data and log)

(note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.|||Sorry for wasting your time.

I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

There I found that one of the Raid disks was going bad. That explains a lot.

Thanks for all of your help.

You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

I'm betting you agree that a bad disk could cause all of the quirky backup errors?|||Dont' be sorry.... Glad to hear your resolution and efforts, keep it up.
No second thought for these failures, but anytime if you've them again then follow the listings above.

Good luck.

Backup SQL Files

Hi,

We are about to install MSSQL Server 2000, on a Windows XP Home
Machine. However, we have servers we could set routine backups of files
to be done to. What what be the best way of doing this?

Is there functionality in SQL Server 2000, where we can say dump all
data definitions, accounts, and data to files on this drive at regular
intervals?

What other suggestions do you have apart from obviously the usual RAID,
and Tape Drive stuff?

Thanks

DavidDavid (david.goodyear@.gmail.com) writes:
> We are about to install MSSQL Server 2000, on a Windows XP Home
> Machine. However, we have servers we could set routine backups of files
> to be done to. What what be the best way of doing this?
> Is there functionality in SQL Server 2000, where we can say dump all
> data definitions, accounts, and data to files on this drive at regular
> intervals?

You could set up a job that runs from SQL Server Agent that backups
the database to a disk somewhere using the BACKUP command.

--
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|||What "flavour" of SQL Server 2000 (e.g. Enterprise, Developer) will you
be installing on this machine? I only ask because certain flavours
won't let you install the server components on an XP machine...

Anyway, you can use the Database Maintenance Plan Wizard to set up a
regular full backup of a database. This will preserve all the data in
the database (including the schema), objects such as functions and
stored procedures and the user accounts you have defined in the
database.

In addition to backing up any user defined databases, you should also
consider doing a backup of the system databases (master, msdb, model)
via the Database Maintenance Plan Wizard. Doing this should allow you
to have a backup of the server logins, SQL Server Agent jobs,
maintenance plans etc.

Hope that helps a bit

Backup Server

I would like to have a backup server ready to take over just in case I
experience an issue with one my SQL servers.
The idea is that if one server goes down, the other one is working in the
background and is seemless to the end user. I have the NLB manager running
with 2 ip address linking the 2 machines, however I am trying to figure out
the best way to SYNC the SQL data. Any ideas?
Thanks in advance!!
Mike,
this article might help clarify some of the considerations:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison
|||Have you investigated clustering? Microsoft site has all the info you need
to understand and get up and running with clustering.
Also, you could use logshipping with manual failover to maintain a standby
server.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:53C7587E-55BE-4D64-892D-4E5C523856D3@.microsoft.com...
I would like to have a backup server ready to take over just in case I
experience an issue with one my SQL servers.
The idea is that if one server goes down, the other one is working in the
background and is seemless to the end user. I have the NLB manager running
with 2 ip address linking the 2 machines, however I am trying to figure out
the best way to SYNC the SQL data. Any ideas?
Thanks in advance!!
|||"" wrote:
> I would like to have a backup server ready to take over just
> in case I
> experience an issue with one my SQL servers.
> The idea is that if one server goes down, the other one is
> working in the
> background and is seemless to the end user. I have the NLB
> manager running
> with 2 ip address linking the 2 machines, however I am trying
> to figure out
> the best way to SYNC the SQL data. Any ideas?
> Thanks in advance!!
I have a set up in a production environment where availability of the
DB at all times is paramount.
I have transactional replication running so that at any time if the
main DB server goes down, the backup server will only be about 4
seconds behind so its just a matter of pointing the applications to
the new server. The whole process of swapping to the second DB takes
about 30 seconds.
It has been a very reliable solution and whilst not as seemless as
having a cluster set up it is no way near as expensive.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict242154.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=842318

Backup Question

I have some SQL 2000 and SQL 2005 servers as part of my network and I am
including the SQL databases are part of my nightly backup routines. How I d
o
this is set up SQL jobs to create .bak files early in the evening so that th
e
nightly backup "sweep" then includes them in the backup since the .mdf and
.ldf are open files. This work great but is it the best way? Does not
Volume Shadow Copy help me work around this issue so that I can backup open
.mdf and .ldf files? If so then how does that work since I'm not familiar
with it.
Basically, what does everyone else do as part of the backup rountines to
make sure SQL Server data gets backed up and what databases (outside of your
application databases) do you backup (master etc.)?
Thanks!
-Richard KHello,
There are a few of ways you can tackle this...
1. Use a third party tool that can back up the database directly to tape
like Backup Exec or Lite Speed etc.
2. Create a maintenance plan to back up the database at a schedule time to
your directory.
3. Online Back ups
4. Etc.
There are lots of good information on how to set up a maintenance plan in
the SQL Books online or check out
http://searchsqlserver.techtarget.c...1076630,00.html
I Hope this helps.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Sorry I left out a few things..
If you are doing replication you will want to back up the Back up the
Publisher, Distributor, Subscriber(s) and Master DBs and all your SQL Users.
ESSAG" target="_blank">http://www.microsoft.com/technet/pr...x#
ESSAG
Also, depending how important your data is you may want to do Trans Logs in
between full backups.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Richard K wrote:
> I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
do
> this is set up SQL jobs to create .bak files early in the evening so that
the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup ope
n
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of yo
ur
> application databases) do you backup (master etc.)?
I saw Christina's answers but decided to describe what I normally do.
1. The \Data directory is excluded from the file system (FS) backup
2. A management plan (or several plans if you need to do the transaction
log backups for some databases and not for others) puts all the backup
files into \Backup directory and deletes them after N days (depending on
the space capacities).
3. A file system backup writes to tapes those *.bak and *.trn files
created by the maintenance routine.
The timing and specifics depend on the business needs and probability of
disasters.
If the most probable disaster is a data loss caused by the
application/operator (i.e. the SQL Server is up and running but the data
was lost), you better have SQL backups ready and copy them to the tape
even 20+ hours after they are created.
If the most probable disaster is a hardware failure and the backups need
to be restored on a separate SQL Server, then it is better to schedule
the SQL backups prior to the FS or trigger the FS backup at the end of
the SQL backup.|||"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
>
Don't back up the .MDF and .LDF files.
Even with volume shadow copy you won't get a consistent backup.
Imagine this scenario. You backup MyDB.MDF at 11:00 PM.
By the time you get around to backing up MyDB.LDF at 11:15 PM, new
transactions have been completed in MyDB and some new ones have been
started, but not completed.
Now things crash.
You restore MyDB.MDF. Ok, all's good and fine. You restore MyDB.LDF.
Now you try to start up the DB. It can't. Why? Because MyDB.LDF has open
transactions that don't even exist in MyDB.MDF yet and MyDBF has open
transactions that MyDB.LDF things are completed.
So, yes, simply backup to disk as you seem to want to do. Simply figure out
how long it takes to do a full backup.
Generally I did full backups of my user databases 3 nights a week. And then
transaction log backups every 15 minutes.
WORST case scenario for recovery was to restore the most recent full backup
and then 288 or so transaction log backups.
Now, if I'm then backing them up to tape, well I simply make sure I keep at
least 3 days worth of tapes around.
Generally though I like to backup to a snap server or other NAS device and
keep them there for a week (with tape backups as desired). This makes
recovery even faster.

> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
I backed up the system databases daily since they're small and generally can
only have simple recovery mode, so to capture changes, I wanted to make sure
we had a fairly recent copy.

> Thanks!
> -Richard K
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup Question

I have some SQL 2000 and SQL 2005 servers as part of my network and I am
including the SQL databases are part of my nightly backup routines. How I do
this is set up SQL jobs to create .bak files early in the evening so that the
nightly backup "sweep" then includes them in the backup since the .mdf and
..ldf are open files. This work great but is it the best way? Does not
Volume Shadow Copy help me work around this issue so that I can backup open
..mdf and .ldf files? If so then how does that work since I'm not familiar
with it.
Basically, what does everyone else do as part of the backup rountines to
make sure SQL Server data gets backed up and what databases (outside of your
application databases) do you backup (master etc.)?
Thanks!
-Richard K
Hello,
There are a few of ways you can tackle this...
1. Use a third party tool that can back up the database directly to tape
like Backup Exec or Lite Speed etc.
2. Create a maintenance plan to back up the database at a schedule time to
your directory.
3. Online Back ups
4. Etc.
There are lots of good information on how to set up a maintenance plan in
the SQL Books online or check out
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1076630,00.html
I Hope this helps.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K
|||Sorry I left out a few things..
If you are doing replication you will want to back up the Back up the
Publisher, Distributor, Subscriber(s) and Master DBs and all your SQL Users.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ESSAG
Also, depending how important your data is you may want to do Trans Logs in
between full backups.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K
|||Richard K wrote:
> I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I do
> this is set up SQL jobs to create .bak files early in the evening so that the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of your
> application databases) do you backup (master etc.)?
I saw Christina's answers but decided to describe what I normally do.
1. The \Data directory is excluded from the file system (FS) backup
2. A management plan (or several plans if you need to do the transaction
log backups for some databases and not for others) puts all the backup
files into \Backup directory and deletes them after N days (depending on
the space capacities).
3. A file system backup writes to tapes those *.bak and *.trn files
created by the maintenance routine.
The timing and specifics depend on the business needs and probability of
disasters.
If the most probable disaster is a data loss caused by the
application/operator (i.e. the SQL Server is up and running but the data
was lost), you better have SQL backups ready and copy them to the tape
even 20+ hours after they are created.
If the most probable disaster is a hardware failure and the backups need
to be restored on a separate SQL Server, then it is better to schedule
the SQL backups prior to the FS or trigger the FS backup at the end of
the SQL backup.
|||"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
>
Don't back up the .MDF and .LDF files.
Even with volume shadow copy you won't get a consistent backup.
Imagine this scenario. You backup MyDB.MDF at 11:00 PM.
By the time you get around to backing up MyDB.LDF at 11:15 PM, new
transactions have been completed in MyDB and some new ones have been
started, but not completed.
Now things crash.
You restore MyDB.MDF. Ok, all's good and fine. You restore MyDB.LDF.
Now you try to start up the DB. It can't. Why? Because MyDB.LDF has open
transactions that don't even exist in MyDB.MDF yet and MyDBF has open
transactions that MyDB.LDF things are completed.
So, yes, simply backup to disk as you seem to want to do. Simply figure out
how long it takes to do a full backup.
Generally I did full backups of my user databases 3 nights a week. And then
transaction log backups every 15 minutes.
WORST case scenario for recovery was to restore the most recent full backup
and then 288 or so transaction log backups.
Now, if I'm then backing them up to tape, well I simply make sure I keep at
least 3 days worth of tapes around.
Generally though I like to backup to a snap server or other NAS device and
keep them there for a week (with tape backups as desired). This makes
recovery even faster.

> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
I backed up the system databases daily since they're small and generally can
only have simple recovery mode, so to capture changes, I wanted to make sure
we had a fairly recent copy.

> Thanks!
> -Richard K
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup Question

I have some SQL 2000 and SQL 2005 servers as part of my network and I am
including the SQL databases are part of my nightly backup routines. How I do
this is set up SQL jobs to create .bak files early in the evening so that the
nightly backup "sweep" then includes them in the backup since the .mdf and
.ldf are open files. This work great but is it the best way? Does not
Volume Shadow Copy help me work around this issue so that I can backup open
.mdf and .ldf files? If so then how does that work since I'm not familiar
with it.
Basically, what does everyone else do as part of the backup rountines to
make sure SQL Server data gets backed up and what databases (outside of your
application databases) do you backup (master etc.)?
Thanks!
-Richard KHello,
There are a few of ways you can tackle this...
1. Use a third party tool that can back up the database directly to tape
like Backup Exec or Lite Speed etc.
2. Create a maintenance plan to back up the database at a schedule time to
your directory.
3. Online Back ups
4. Etc.
There are lots of good information on how to set up a maintenance plan in
the SQL Books online or check out
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1076630,00.html
I Hope this helps.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Sorry I left out a few things..
If you are doing replication you will want to back up the Back up the
Publisher, Distributor, Subscriber(s) and Master DBs and all your SQL Users.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ESSAG
Also, depending how important your data is you may want to do Trans Logs in
between full backups.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Richard K wrote:
> I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I do
> this is set up SQL jobs to create .bak files early in the evening so that the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of your
> application databases) do you backup (master etc.)?
I saw Christina's answers but decided to describe what I normally do.
1. The \Data directory is excluded from the file system (FS) backup
2. A management plan (or several plans if you need to do the transaction
log backups for some databases and not for others) puts all the backup
files into \Backup directory and deletes them after N days (depending on
the space capacities).
3. A file system backup writes to tapes those *.bak and *.trn files
created by the maintenance routine.
The timing and specifics depend on the business needs and probability of
disasters.
If the most probable disaster is a data loss caused by the
application/operator (i.e. the SQL Server is up and running but the data
was lost), you better have SQL backups ready and copy them to the tape
even 20+ hours after they are created.
If the most probable disaster is a hardware failure and the backups need
to be restored on a separate SQL Server, then it is better to schedule
the SQL backups prior to the FS or trigger the FS backup at the end of
the SQL backup.|||"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
>
Don't back up the .MDF and .LDF files.
Even with volume shadow copy you won't get a consistent backup.
Imagine this scenario. You backup MyDB.MDF at 11:00 PM.
By the time you get around to backing up MyDB.LDF at 11:15 PM, new
transactions have been completed in MyDB and some new ones have been
started, but not completed.
Now things crash.
You restore MyDB.MDF. Ok, all's good and fine. You restore MyDB.LDF.
Now you try to start up the DB. It can't. Why? Because MyDB.LDF has open
transactions that don't even exist in MyDB.MDF yet and MyDBF has open
transactions that MyDB.LDF things are completed.
So, yes, simply backup to disk as you seem to want to do. Simply figure out
how long it takes to do a full backup.
Generally I did full backups of my user databases 3 nights a week. And then
transaction log backups every 15 minutes.
WORST case scenario for recovery was to restore the most recent full backup
and then 288 or so transaction log backups.
Now, if I'm then backing them up to tape, well I simply make sure I keep at
least 3 days worth of tapes around.
Generally though I like to backup to a snap server or other NAS device and
keep them there for a week (with tape backups as desired). This makes
recovery even faster.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
I backed up the system databases daily since they're small and generally can
only have simple recovery mode, so to capture changes, I wanted to make sure
we had a fairly recent copy.
> Thanks!
> -Richard K
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup one server, restore to another

I frequently restore the backups of my production server to my standby/test
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>

Backup one server, restore to another

I frequently restore the backups of my production server to my standby/test
server. They (the servers) aren't completely identical, however. Production
has 3 logical disks for OS, data and logs while standby has only two disks.
The real problem though is the time it takes to do a restore using EM. It
seems that I have to restore the full backup then each transaction log
separately. It would be very convenient to be able to specify (using add
device) the full backup and all of the logs I want at once, then only have
to change the paths to the database and log files one time. I've read
through BOL but it is silent on this issue. Is it possible through EM? Or
should I be looking at a third-party app from e.g. Red Gate?You could write a SQL script that implements the WITH MOVE option of the
backup command. Enterprise Manager is a nice tool, but I haven't figured
out how to automate a series of mouse clicks. For repeatability, you have
to go to T_SQL scripts.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:e3vH%23OT0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>I frequently restore the backups of my production server to my standby/test
> server. They (the servers) aren't completely identical, however.
> Production
> has 3 logical disks for OS, data and logs while standby has only two
> disks.
> The real problem though is the time it takes to do a restore using EM. It
> seems that I have to restore the full backup then each transaction log
> separately. It would be very convenient to be able to specify (using add
> device) the full backup and all of the logs I want at once, then only have
> to change the paths to the database and log files one time. I've read
> through BOL but it is silent on this issue. Is it possible through EM? Or
> should I be looking at a third-party app from e.g. Red Gate?
>

Backup of mirrored databases using TSM

Hi,

I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.

Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?

Thanks.

That depends on how you would like to do the restore process. The restore process will dictate how you may want to do your backups and not the other way around. In our case we just use the native SQL Server agent to generate the backup files and have TSM to pick up the backup files. This is a disadvantage if your backing up terrabytes of data. In this case, LightSpeed will help decrease backup time.|||

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.

Backup of mirrored databases using TSM

Hi,

I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.

Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?

Thanks.

That depends on how you would like to do the restore process. The restore process will dictate how you may want to do your backups and not the other way around. In our case we just use the native SQL Server agent to generate the backup files and have TSM to pick up the backup files. This is a disadvantage if your backing up terrabytes of data. In this case, LightSpeed will help decrease backup time.|||

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.

Backup Maintenance Plan - Best Practices

Hi All,

I'm about to embark on creating a maintenance plan to back up all databases on one of our SQL 2005 servers. I am looking for some advice on best practices for doing this.

I have it in my mind that i want to be taking a full database backup once a week, with differential backups on a daily basis and transactional backups performed every 2 to 4 hours.

Do i need to create three maintenance plans for this, i.e. 1 for full, 1 for differential, and 1 for transactional?

If i want to only keep the backups from the last week, is this done by setting up a maintenance cleanup task in the full backup plan to clear all bak files that are a week old?
If so i'll also probably require one to remove the trn files also.

When using the backup command from the context menu in SSMS there is an option to name the backup set. How does this work when using maintenance plans as i haven't been able to find this option whilst trying out some of the features?

I'm sure to have more questions on this subject, but any help on the above queries would be most appreciated.

TIA,

GrantNo. As long as you are running SP2, you can do all of this using subtasks. So, you can create a single maintenance plan and then add subtasks for the full, differential, and tran log. Each of the subtasks can have their own schedule.

Backup Log and Shrinking of the ldf file.

Hi:
On of our Production servers, we have noticed that the actual shrinking of
the physical log file (.ldf) does not happen if we execute the backup log
statement only once. It seems we have to run the transaction log backup more
than once or multiple times to shrink the log file. The first time we
execute the backup log statement it seems that the DBCC Loginfo shows the
status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
subsequent execution of the backup log statement again a second time, the
file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases
many times to shrink the file? Is this by design? I am trying to understand
how the log and VLFs occur when it comes to backup and shrink and the need
to run the backup log statement twice to shrink the file physically. I would
like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side configuration
that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly
appreciated.
Thanks
MThis is a multi-part message in MIME format.
--010809070604000608040104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Firstly, why are you shrinking a production transaction log? This is a
bad thing to do. Just set it to the max size to which you want it to
grow, back it up regularly enough so that it doesn't exceed that size
and then leave it alone. (See Tibor's "don't shrink" page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
Now that that's out of the way... The transaction log (logical) is a
circular structure. When it gets to the end of the physical file it
wraps back around to the start of the physical file (assuming there are
no transactions at the start of the physical file, ie. that they've been
truncated from a previous BACKUP statement, otherwise it will try to do
an auto-grow if you haven't restricted it). When you backup the
transaction log, it is automatically truncated but some logical part of
it (VLF) is still the "active" logical file. This is where the new
transactions start from (not the beginning of the physical file). When
you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical
file from the end back to that last VLF that contains transactions. So,
if the active VLF is somewhere near the end of the physical file, it's
not going to shrink very much, but when more transactions are committed,
the log will "wrap" back around to the start of the physical file. If
you did a BACKUP LOG and DBCC SHRINKFILE at this point then the physical
file would be able to shrink down considerably.
This is most likely the reason you're needing to do multiple BACKUPs
(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a
point - because you need the transactions to wrap back around to the
start of the physical file.
However, it should all be a moot point because you shouldn't be
shrinking production transaction logs - bad, bad, naughty, naughty.
Just set it and leave it alone. And if you want it to stay tiny just
back it up more regularly. Or if you want it to stay tiny and don't
care about keeping the transaction log, then put the database in SIMPLE
recovery mode (just make sure you do full DB backups often enough to
minimise data loss).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Meher wrote:
>Hi:
>On of our Production servers, we have noticed that the actual shrinking of
>the physical log file (.ldf) does not happen if we execute the backup log
>statement only once. It seems we have to run the transaction log backup more
>than once or multiple times to shrink the log file. The first time we
>execute the backup log statement it seems that the DBCC Loginfo shows the
>status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
>subsequent execution of the backup log statement again a second time, the
>file is shrunk (dbcc loginfo shows the status as 0).
>Any reason why we need to run the Tran log backup twice or in some cases
>many times to shrink the file? Is this by design? I am trying to understand
>how the log and VLFs occur when it comes to backup and shrink and the need
>to run the backup log statement twice to shrink the file physically. I would
>like to avoid that if I can and if it is possible.
>Additionally i am wondering if there is any db or server side configuration
>that causes this to happen.
>The Servers are all running SQL Server 2000 (both SP3 and SP4).
>MVPS, Please provide your valuable knowledge. Any insight is highly
>appreciated.
>Thanks
>M
>
>
>
--010809070604000608040104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Firstly, why are you shrinking a production transaction log? This
is a bad thing to do. Just set it to the max size to which you want it
to grow, back it up regularly enough so that it doesn't exceed that
size and then leave it alone. (See Tibor's "don't shrink" page:
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.karaszi.com/SQLServer/info_dont_shrink.asp</a>)<br>">http://www.karaszi.com/SQLServer/info_dont_shrink.asp">http://www.karaszi.com/SQLServer/info_dont_shrink.asp</a>)<br>
<br>
Now that that's out of the way... The transaction log (logical) is a
circular structure. When it gets to the end of the physical file it
wraps back around to the start of the physical file (assuming there are
no transactions at the start of the physical file, ie. that they've
been truncated from a previous BACKUP statement, otherwise it will try
to do an auto-grow if you haven't restricted it). When you backup the
transaction log, it is automatically truncated but some logical part of
it (VLF) is still the "active" logical file. This is where the new
transactions start from (not the beginning of the physical file). When
you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical
file from the end back to that last VLF that contains transactions.
So, if the active VLF is somewhere near the end of the physical file,
it's not going to shrink very much, but when more transactions are
committed, the log will "wrap" back around to the start of the physical
file. If you did a BACKUP LOG and DBCC SHRINKFILE at this point then
the physical file would be able to shrink down considerably.<br>
<br>
This is most likely the reason you're needing to do multiple BACKUPs
(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a
point - because you need the transactions to wrap back around to the
start of the physical file.<br>
<br>
However, it should all be a moot point because you shouldn't be
shrinking production transaction logs - bad, bad, naughty, naughty.
Just set it and leave it alone. And if you want it to stay tiny just
back it up more regularly. Or if you want it to stay tiny and don't
care about keeping the transaction log, then put the database in SIMPLE
recovery mode (just make sure you do full DB backups often enough to
minimise data loss).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Meher wrote:
<blockquote cite="midelPEWe1YGHA.1352@.TK2MSFTNGP05.phx.gbl" type="cite">
<pre wrap="">Hi:
On of our Production servers, we have noticed that the actual shrinking of
the physical log file (.ldf) does not happen if we execute the backup log
statement only once. It seems we have to run the transaction log backup more
than once or multiple times to shrink the log file. The first time we
execute the backup log statement it seems that the DBCC Loginfo shows the
status of the VLF as 2 and therefore we cannot shrink the ldf file. On a
subsequent execution of the backup log statement again a second time, the
file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases
many times to shrink the file? Is this by design? I am trying to understand
how the log and VLFs occur when it comes to backup and shrink and the need
to run the backup log statement twice to shrink the file physically. I would
like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side configuration
that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly
appreciated.
Thanks
M
</pre>
</blockquote>
</body>
</html>
--010809070604000608040104--|||Hi,
What is the size of production database.
Make a matinance plan and configure to shrink the logfile after full
backup.Then schedule transaction log backup.
this will help u.
from
Doller|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C6632E.B6BDDF20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Well its not me who is shrinking the files but my customer. However I =should say the Mike this is a beautiful explanation that I have read =about why i need to do multiple backups. Thank you very much for your =explanation and suggestions. "Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:elRBaP2YGHA.2376@.TK2MSFTNGP03.phx.gbl...
Firstly, why are you shrinking a production transaction log? This is =a bad thing to do. Just set it to the max size to which you want it to =grow, back it up regularly enough so that it doesn't exceed that size =and then leave it alone. (See Tibor's "don't shrink" page: =http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
Now that that's out of the way... The transaction log (logical) is a =circular structure. When it gets to the end of the physical file it =wraps back around to the start of the physical file (assuming there are =no transactions at the start of the physical file, ie. that they've been =truncated from a previous BACKUP statement, otherwise it will try to do =an auto-grow if you haven't restricted it). When you backup the =transaction log, it is automatically truncated but some logical part of =it (VLF) is still the "active" logical file. This is where the new =transactions start from (not the beginning of the physical file). When =you shrink the log file (with DBCC SHRINKFILE) it shrinks the physical =file from the end back to that last VLF that contains transactions. So, =if the active VLF is somewhere near the end of the physical file, it's =not going to shrink very much, but when more transactions are committed, =the log will "wrap" back around to the start of the physical file. If =you did a BACKUP LOG and DBCC SHRINKFILE at this point then the physical =file would be able to shrink down considerably.
This is most likely the reason you're needing to do multiple BACKUPs =(and correspondingly DBCC SHRINKFILEs) in order to shrink it past a =point - because you need the transactions to wrap back around to the =start of the physical file.
However, it should all be a moot point because you shouldn't be =shrinking production transaction logs - bad, bad, naughty, naughty. =Just set it and leave it alone. And if you want it to stay tiny just =back it up more regularly. Or if you want it to stay tiny and don't =care about keeping the transaction log, then put the database in SIMPLE =recovery mode (just make sure you do full DB backups often enough to =minimise data loss).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Meher wrote: Hi:
On of our Production servers, we have noticed that the actual shrinking =of the physical log file (.ldf) does not happen if we execute the backup =log statement only once. It seems we have to run the transaction log backup =more than once or multiple times to shrink the log file. The first time we execute the backup log statement it seems that the DBCC Loginfo shows =the status of the VLF as 2 and therefore we cannot shrink the ldf file. On a =
subsequent execution of the backup log statement again a second time, =the file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases =
many times to shrink the file? Is this by design? I am trying to =understand how the log and VLFs occur when it comes to backup and shrink and the =need to run the backup log statement twice to shrink the file physically. I =would like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side =configuration that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly appreciated.
Thanks
M
--=_NextPart_000_000F_01C6632E.B6BDDF20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Well its not me who is shrinking the =files but my customer. However I should say the Mike this is a beautiful explanation =that I have read about why i need to do multiple backups. Thank you very much =for your explanation and suggestions.
"Mike Hodgson" wrote =in message news:elRBaP2YGHA.2376=@.TK2MSFTNGP03.phx.gbl...Firstly, why are you shrinking a production transaction log? This is a =bad thing to do. Just set it to the max size to which you want it to grow, =back it up regularly enough so that it doesn't exceed that size and then leave =it alone. (See Tibor's "don't shrink" page: http://www.karaszi.com/SQLServer/info_dont_shrink.asp">http://www=.karaszi.com/SQLServer/info_dont_shrink.asp)Now that that's out of the way... The transaction log (logical) is a = circular structure. When it gets to the end of the physical file =it wraps back around to the start of the physical file (assuming there =are no transactions at the start of the physical file, ie. that they've been truncated from a previous BACKUP statement, otherwise it will try to =do an auto-grow if you haven't restricted it). When you backup the =transaction log, it is automatically truncated but some logical part of it (VLF) =is still the "active" logical file. This is where the new transactions =start from (not the beginning of the physical file). When you shrink the =log file (with DBCC SHRINKFILE) it shrinks the physical file from the end back =to that last VLF that contains transactions. So, if the active VLF is =somewhere near the end of the physical file, it's not going to shrink very much, =but when more transactions are committed, the log will "wrap" back around =to the start of the physical file. If you did a BACKUP LOG and DBCC =SHRINKFILE at this point then the physical file would be able to shrink down considerably.This is most likely the reason you're needing to =do multiple BACKUPs (and correspondingly DBCC SHRINKFILEs) in order to =shrink it past a point - because you need the transactions to wrap back around =to the start of the physical file.However, it should all be a moot =point because you shouldn't be shrinking production transaction logs - bad, =bad, naughty, naughty. Just set it and leave it alone. And if =you want it to stay tiny just back it up more regularly. Or if you want =it to stay tiny and don't care about keeping the transaction log, then put =the database in SIMPLE recovery mode (just make sure you do full DB =backups often enough to minimise data loss).
--mike =hodgsonhttp://sqlnerd.blogspot.com Meher wrote: Hi:
On of our Production servers, we have noticed that the actual shrinking =of the physical log file (.ldf) does not happen if we execute the backup =log statement only once. It seems we have to run the transaction log backup =more than once or multiple times to shrink the log file. The first time we execute the backup log statement it seems that the DBCC Loginfo shows =the status of the VLF as 2 and therefore we cannot shrink the ldf file. On a =subsequent execution of the backup log statement again a second time, =the file is shrunk (dbcc loginfo shows the status as 0).
Any reason why we need to run the Tran log backup twice or in some cases =many times to shrink the file? Is this by design? I am trying to =understand how the log and VLFs occur when it comes to backup and shrink and the =need to run the backup log statement twice to shrink the file physically. I =would like to avoid that if I can and if it is possible.
Additionally i am wondering if there is any db or server side =configuration that causes this to happen.
The Servers are all running SQL Server 2000 (both SP3 and SP4).
MVPS, Please provide your valuable knowledge. Any insight is highly appreciated.
Thanks
M

---

--=_NextPart_000_000F_01C6632E.B6BDDF20--