backup strategy question (sql 2005)

Hi,
im thinking about the backup strategy for our new sql 2005 database. I would
like to know how you would do it. The scenario is the following:
- the db consists of 2 file groups (primary + another one)
- the second file group is pretty large (250 GB) and contains a lot of blob
data that changes rarely
- the primary group is rather small (3 GB), but changes frequently
Since the first file group is small, I plan to backup it every day (full
backup). The secound group should be fully backuped every 2 weeks. In the
meantime I would backup the daily changes of the second group using
differncial backups. Any better ideas?
What I dont understand is how the transaction log behaves in this case. The
log contains the changes for all file groups. So what happens if I backup
only one file group? Are the changes of that group removed from the log and
the changes of the other group stay logged? I wonder how this works.
thanks in advance,
BenjaminThe only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
diff, file, filegroup, filegrup with diff etc) does not empty the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case. The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin|||Hi,
ok, interesting. But if this is the case, why should I create full database
backups at all? I mean, I dont want to store the logs forever. If I backup
the entire database or a part of it I don't want to keep the old log files.
Can you tell me how to achieve this?
"Tibor Karaszi" wrote:
> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
> diff, file, filegroup, filegrup with diff etc) does not empty the log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> > Hi,
> >
> > im thinking about the backup strategy for our new sql 2005 database. I would
> > like to know how you would do it. The scenario is the following:
> >
> > - the db consists of 2 file groups (primary + another one)
> > - the second file group is pretty large (250 GB) and contains a lot of blob
> > data that changes rarely
> > - the primary group is rather small (3 GB), but changes frequently
> >
> > Since the first file group is small, I plan to backup it every day (full
> > backup). The secound group should be fully backuped every 2 weeks. In the
> > meantime I would backup the daily changes of the second group using
> > differncial backups. Any better ideas?
> >
> > What I dont understand is how the transaction log behaves in this case. The
> > log contains the changes for all file groups. So what happens if I backup
> > only one file group? Are the changes of that group removed from the log and
> > the changes of the other group stay logged? I wonder how this works.
> >
> > thanks in advance,
> > Benjamin
>|||Hmm, I'm afraid that I don't get the question...
Are you saying that you don't want to perform transaction log backups? Find, just set the recovery
model for the database to simple.
Or are you saying something else?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
news:EF2882DD-C711-4F3B-A092-BD34C4DDD723@.microsoft.com...
> Hi,
> ok, interesting. But if this is the case, why should I create full database
> backups at all? I mean, I dont want to store the logs forever. If I backup
> the entire database or a part of it I don't want to keep the old log files.
> Can you tell me how to achieve this?
>
> "Tibor Karaszi" wrote:
>> The only backup operation that removes log records is BACKUP LOG. The other types of backup (db,
>> diff, file, filegroup, filegrup with diff etc) does not empty the log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in message
>> news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
>> > Hi,
>> >
>> > im thinking about the backup strategy for our new sql 2005 database. I would
>> > like to know how you would do it. The scenario is the following:
>> >
>> > - the db consists of 2 file groups (primary + another one)
>> > - the second file group is pretty large (250 GB) and contains a lot of blob
>> > data that changes rarely
>> > - the primary group is rather small (3 GB), but changes frequently
>> >
>> > Since the first file group is small, I plan to backup it every day (full
>> > backup). The secound group should be fully backuped every 2 weeks. In the
>> > meantime I would backup the daily changes of the second group using
>> > differncial backups. Any better ideas?
>> >
>> > What I dont understand is how the transaction log behaves in this case. The
>> > log contains the changes for all file groups. So what happens if I backup
>> > only one file group? Are the changes of that group removed from the log and
>> > the changes of the other group stay logged? I wonder how this works.
>> >
>> > thanks in advance,
>> > Benjamin
>>|||I have no idea if this would work or not. What are your business
requirements for availability? How much data loss is acceptable? How long
can the system be down for a recovery operation? What type of hardware are
you using?
Sure, you can simply backup the databases using virtually any method that
you choose. But, that doesn't mean the backups are going to accomplish
something. If your business rules state that you can only be offline for 5
minutes and you setup backups that are going to take 1 hour to restore, then
your backups are essentially worthless to the business, because they do not
meet business needs.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:3D7098EB-A725-4F11-B188-A03F0BC49959@.microsoft.com...
> Hi,
> im thinking about the backup strategy for our new sql 2005 database. I
> would
> like to know how you would do it. The scenario is the following:
> - the db consists of 2 file groups (primary + another one)
> - the second file group is pretty large (250 GB) and contains a lot of
> blob
> data that changes rarely
> - the primary group is rather small (3 GB), but changes frequently
> Since the first file group is small, I plan to backup it every day (full
> backup). The secound group should be fully backuped every 2 weeks. In the
> meantime I would backup the daily changes of the second group using
> differncial backups. Any better ideas?
> What I dont understand is how the transaction log behaves in this case.
> The
> log contains the changes for all file groups. So what happens if I backup
> only one file group? Are the changes of that group removed from the log
> and
> the changes of the other group stay logged? I wonder how this works.
> thanks in advance,
> Benjamin

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 MSSQL

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!We back our stuff up nightly, however you have to decide how much info you are willing to lose should your system crash. If weekly backups have been working and you feel confident in your hardware then stick to that schedule.

HTH,
Aric|||

Quote:

Originally Posted by DbAFtW

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!

Hi there,

Backup / recovery plan should be designed based on the importance of your data. As a consultant i am managing 17 database servers, some servers are configured to run backup routine every hour, whereas, some servers are configured to run backup routine every day. Good luck & Take care.|||Thanks folks for the replies.
Well, the vendors that put the web server and the MSSQL DB in place were using a Simple Recovery Model and I felt the nature of the data dictated transaction log backups. The system accepts college applications and I felt any data loss was something I wanted to avoid due to the importance of that data! The intention is that we can experience minimal loss utilizing the strategy to restore from the weekly full, nightly differential and every half hour transaction log backups.|||</bumpitybump>sql

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 strategy for a 1000GB database

Hi,

Could anyone tell me the backup strategy for a 1000GB database?

Thank you!

Peter Wang

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!The size of the database is one consideration, but probably more important
is how much data loss and downtime you can afford in the event of a problem.
You really need to get this information first, so you can decide how often
to do full/differential/log backups. This will also help you to decide how
much money you can reasonably allocate to your backup/availability solution.
There is a lot of information in BOL in the section called "Desiging a
Backup and Restore Strategy".

If you have the database on a NAS or SAN already, you may be able to use
features of the hardware (some storage devices support MSSQL snapshot
backup/restore). On the other hand, backing up to multiple disk devices
might be a good choice, depending on your requirements and storage
configuration.

Finally, don't forget that as a rule, backing up is easier than restoring.
So also consider how easily your solution will allow you to restore a
database if you need to. It's hard to be specific, as a lot depends on your
environment and your application/user requirements.

Simon

"peter wang" <anonymous@.devdex.com> wrote in message
news:3ef88c70$0$201$75868355@.news.frii.net...
> Hi,
> Could anyone tell me the backup strategy for a 1000GB database?
> Thank you!
> Peter Wang
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Backup Strategy Clarifications

Hello,
I just wanted to check if my backup strategy is on the right-track or not,
so that I can improve on it. I am using SQL 2000 SP4.
I have the following backup strategy in place: -
STEP 1: MODEL
==========
Database: model
Recovery Model: Full
Log Backup Frequency: Once a day at 20:00
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:05
Database Backup Mode: Overwritten
STEP 2: MSDB
==========
Database: msdb
Recovery Model: Simple
Log Backup Frequency: N/A
Log Backup Mode: N/A
Database Backup Frequency: Once a day at 20:05 (same time as model)
Database Backup Mode: Overwritten
STEP 3: MASTER
===========
Database: msdb
Recovery Model: Full
Log Backup Frequency: Once a day at 20:08
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:10
Database Backup Mode: Overwritten
STEP 4: TEMPDB
===========
Database: tempdb
Recovery Model: Simple
I am not doing any backups on this database!
STEP 5: LIVE
===========
Database: live
Recovery Model: Full
I am doing 2 types of Log backups on all User databases:
---
Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
10:00 AM, 11:00 AM)
Type 2. Log Backup Mode: Overwritten
---
Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
10:05 AM, 11:05 AM)
Database Backup Mode: Overwritten
My reasoning was that I do frequent log backups (appended) every 20 mins
and do 1 log backup (overwritten) when it reaches the exact hour. Then
after 5 mins past the hour, do a Full Database backup (overwritten).
Please mention STEP 1, STEP 2, etc. to refer to the proper item and provide
your suggestions.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.comHi,
Your strategy is good enough, but instead of 2 types of transaction log
backups I recommend you to perform only one type of transaction log
backup, but reduce the frequency of trqansaction log backup to 10 Minutes.
Since you are performing the Transaction log backup it is not
required to a full database backup every hour. Full database backup once a
day is more thana enough:-
Steps:-
1. System Database backup strategy is good enough
2. For Live db
A. Schedule a full database backup at 02 AM
B. Schedule the Transaction log backup from 02:15 AM to 01:50 AM every 10
Minutes.
Best way is use the database Maintenence plan to create the jobs for this.
This will create unique transaction log backup files. You could also
enable house keeping mechanism.
Thanks
Hari
SQL SERVER MVP
"Sameer Premji via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:d0f492b8d6dd48f497b96708d471369d@.SQLMonster.com...
> Hello,
> I just wanted to check if my backup strategy is on the right-track or not,
> so that I can improve on it. I am using SQL 2000 SP4.
> I have the following backup strategy in place: -
>
> STEP 1: MODEL
> ==========> Database: model
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:00
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:05
> Database Backup Mode: Overwritten
>
> STEP 2: MSDB
> ==========> Database: msdb
> Recovery Model: Simple
> Log Backup Frequency: N/A
> Log Backup Mode: N/A
> Database Backup Frequency: Once a day at 20:05 (same time as model)
> Database Backup Mode: Overwritten
>
> STEP 3: MASTER
> ===========> Database: msdb
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:08
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:10
> Database Backup Mode: Overwritten
>
> STEP 4: TEMPDB
> ===========> Database: tempdb
> Recovery Model: Simple
> I am not doing any backups on this database!
>
> STEP 5: LIVE
> ===========> Database: live
> Recovery Model: Full
> I am doing 2 types of Log backups on all User databases:
> ---
> Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
> hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
> Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
>
> Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
> 10:00 AM, 11:00 AM)
> Type 2. Log Backup Mode: Overwritten
> ---
> Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
> 10:05 AM, 11:05 AM)
> Database Backup Mode: Overwritten
>
> My reasoning was that I do frequent log backups (appended) every 20 mins
> and do 1 log backup (overwritten) when it reaches the exact hour. Then
> after 5 mins past the hour, do a Full Database backup (overwritten).
>
> Please mention STEP 1, STEP 2, etc. to refer to the proper item and
> provide
> your suggestions.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Hari but I have more questions: -
1. What is the best practice ? - Perform Log backup first and then Database
backup or vice-versa?
2. For System databases, you mentioned it was good enough. So the
sequence/order of backing model first, then msdb, then master is OK or
should I change the order ?
3. For User databases, you mentioned that I should do a Log backup every 10
mins and Database backup once a day. What kind of backup mode should I use
for Log backup - Overwritten or Appended ?
4. By Performing Log backups every 10 mins, will it increase the size of
the Tran Log file ?
- If yes, how can I control the size automatically without performing a
shrink on the database (I read bad things about shrinking as it defragments
pages & disk)?
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.com|||> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because backup history is in msdb
and I want to have backup of latest backup history. I also do log backup for msdb, but since Agent
sets msdb to simple recovery, I have a job that Agent autostart to set it to full. Some think that
this is overkill, though.
> 3. For User databases, you mentioned that I should do a Log backup every 10
> mins and Database backup once a day.
Be aware that you can potentially have a situation where you need to restore 24*6 = 144 log backups.
I.e., latest db backup and all subsequent log backups. This might be fine with you, but you should
be aware of it. One thing to consider is to do a differential backup perhaps every 2 hours.
> What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
What is important is that you need all log backups since latest database backup in order to do
restore. And, you also want some generations of backups (a few days back or so). Etc. Based on this,
you can then plan how you do your backups, new devices all the time, or re-use same device (with
append), etc.
> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied everytime you backup the
log.
I suggest you spend an hour or two reading the sections in Books Online that deals with backup and
restore. Just to you feel confident that you understand the backup and restore architecture in SQL
Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1a93f786c3c5435eafa4d96f81eb5393@.SQLMonster.com...
> Thanks Hari but I have more questions: -
> 1. What is the best practice ? - Perform Log backup first and then Database
> backup or vice-versa?
>
> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
>
> 3. For User databases, you mentioned that I should do a Log backup every 10
> mins and Database backup once a day. What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
>
> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
> - If yes, how can I control the size automatically without performing a
> shrink on the database (I read bad things about shrinking as it defragments
> pages & disk)?
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Hello Mr. Karaszi,
Just to let you know that I have had your site bookmarked since a couple of
yrs ago as its extremely useful. Thanks for your genuine advise.
From the suggestions and reading many forum threads on here, I'm realizing
that my Backup/Restore logic and how I'm doing it, is not up to par.
(A.)
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
backup history is in msdb and I want to have backup of latest backup
history. I also do log backup for msdb, but since Agent sets msdb to simple
recovery, I have a job that Agent autostart to set it to full. Some think
that this is overkill, though.
I think your sequence makes a lot of sense. For msdb, I have had this
problem of recovery model being reset from Full to Simple automatically, in
the past. Then I gave up and just kept it as Simple. But I'm curious if you
could share how and when exactly you are resetting msdb's recovery model.
(B.)
Be aware that you can potentially have a situation where you need to
restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
log backups. This might be fine with you, but you should
be aware of it. One thing to consider is to do a differential backup
perhaps every 2 hours.
I think 144 log backups is overkill. My current Userdb size is 289MB (since
2 yrs!) and it is used daily by approx. 100 employees via our data-driven
web intranet. Given the scenario, what are your recommendations ?
(C.)
What is important is that you need all log backups since latest database
backup in order to do restore. And, you also want some generations of
backups (a few days back or so). Etc. Based on this, you can then plan how
you do your backups, new devices all the time, or re-use same device (with
append), etc.
I'll tell you exactly how I established my backup jobs.
1. I created a group of folders on our backup server (not SQL Server box),
each named after the corresponding database in SQL Server.
2. Inside SQL Server, I created 2 named Backup devices for each database,
pointing to its corresponding network folder. I created 1 for Database
Backup file and 1 for Tran Backup Log file.
e.g
deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb1Data.bak
deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.bak
.
.
.
etc.
3. For Full Database backup, I selected the database in question, right-
click, All Tasks, Backup Database..., General Tab, selected 'Database
Complete', selected the appropriate Backup Device name (suffix with 'Data')
, selected Overwritten, scheduled it to run every hour. Options tab,
selected 1st, 3rd and 4th checkbox.
I repeated the same step 3 for Transaction Log and selected Overwritten
that runs every 15 mins.
As a result, what I end up having is only 2 files for each database
constantly being overwritten. I don't have "series" of Data and Log backup
files.
As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
development machine (as I haven't used this wizard at all) and it created a
"series" of data and log backup files with a timestamp at the end.
Should I use this wizard and if so, how do I limit/recycle/overwrite the
number of data and log files created with unique timestamps ?
(D.)
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
everytime you backup the log.
As I understand, the log gets emptied but the physical file size doesn't
shrink right ? I read your article about NOT shrinking the file.
--
Message posted via http://www.sqlmonster.com|||> Just to let you know that I have had your site bookmarked since a couple of
> yrs ago as its extremely useful.
I'm happy to hear that.
> But I'm curious if you
> could share how and when exactly you are resetting msdb's recovery model.
Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.
> I think 144 log backups is overkill. My current Userdb size is 289MB (since
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
No can do. Only your client can decide that. The frequency of backup doesn't (ultimately) have to do
with amount of data or number of users. In the end it is the amount of data you can afford to lose
if worst come to worst.
> I'll tell you exactly how I established my backup jobs.
<snip>
It seems that each transaction log backup is overwriting the prior tlog backup. That makes the tlog
backups useless. You need to sit down and think how you want to handle this. Maint wiz is good (for
backup) in the sense that it will create a new file each time it does backup, and thanks to that it
can delete old files. You can do the very same in your own TSQL code. Or you can use several backup
devices (as I assume you want to keep a few days worth of backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d4e1768dbf05435ca12c31fd4d9b9551@.SQLMonster.com...
> Hello Mr. Karaszi,
> Just to let you know that I have had your site bookmarked since a couple of
> yrs ago as its extremely useful. Thanks for your genuine advise.
> From the suggestions and reading many forum threads on here, I'm realizing
> that my Backup/Restore logic and how I'm doing it, is not up to par.
> (A.)
> I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
> backup history is in msdb and I want to have backup of latest backup
> history. I also do log backup for msdb, but since Agent sets msdb to simple
> recovery, I have a job that Agent autostart to set it to full. Some think
> that this is overkill, though.
> I think your sequence makes a lot of sense. For msdb, I have had this
> problem of recovery model being reset from Full to Simple automatically, in
> the past. Then I gave up and just kept it as Simple. But I'm curious if you
> could share how and when exactly you are resetting msdb's recovery model.
>
> (B.)
> Be aware that you can potentially have a situation where you need to
> restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
> log backups. This might be fine with you, but you should
> be aware of it. One thing to consider is to do a differential backup
> perhaps every 2 hours.
> I think 144 log backups is overkill. My current Userdb size is 289MB (since
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
>
> (C.)
> What is important is that you need all log backups since latest database
> backup in order to do restore. And, you also want some generations of
> backups (a few days back or so). Etc. Based on this, you can then plan how
> you do your backups, new devices all the time, or re-use same device (with
> append), etc.
> I'll tell you exactly how I established my backup jobs.
> 1. I created a group of folders on our backup server (not SQL Server box),
> each named after the corresponding database in SQL Server.
> 2. Inside SQL Server, I created 2 named Backup devices for each database,
> pointing to its corresponding network folder. I created 1 for Database
> Backup file and 1 for Tran Backup Log file.
> e.g
> deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb1Data.bak
> deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
> deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
> deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.bak
> .
> .
> .
> etc.
> 3. For Full Database backup, I selected the database in question, right-
> click, All Tasks, Backup Database..., General Tab, selected 'Database
> Complete', selected the appropriate Backup Device name (suffix with 'Data')
> , selected Overwritten, scheduled it to run every hour. Options tab,
> selected 1st, 3rd and 4th checkbox.
> I repeated the same step 3 for Transaction Log and selected Overwritten
> that runs every 15 mins.
> As a result, what I end up having is only 2 files for each database
> constantly being overwritten. I don't have "series" of Data and Log backup
> files.
> As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
> development machine (as I haven't used this wizard at all) and it created a
> "series" of data and log backup files with a timestamp at the end.
> Should I use this wizard and if so, how do I limit/recycle/overwrite the
> number of data and log files created with unique timestamps ?
>
> (D.)
> Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
> everytime you backup the log.
> As I understand, the log gets emptied but the physical file size doesn't
> shrink right ? I read your article about NOT shrinking the file.
> --
> Message posted via http://www.sqlmonster.com|||Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.
Thanks a lot. So I assume that Agent starts everyday (or Agent resets it to
'Simple' everyday or at some specific time), otherwise the following Data
and Log backups on msdb would fail ?
No can do. Only your client can decide that. The frequency of backup
doesn't (ultimately) have to do with amount of data or number of users. In
the end it is the amount of data you can afford to lose if worst come to
worst.
Just spoke to the client and they are willing to lose 1 hour of data.
It seems that each transaction log backup is overwriting the prior tlog
backup. That makes the tlog backups useless. You need to sit down and think
how you want to handle this.
Would an append on tlog help (on the same backup device) instead of
overwrite?
Maint wiz is good (for backup) in the sense that it will create a new file
each time it does backup, and thanks to that it can delete old files. You
can do the very same in your own TSQL code. Or you can use several backup
devices (as I assume you want to keep a few days worth of backups).
I use the same backup device (network folder) for each database because
every night, those folders get backed up on tape. So I don't need to keep
several days worth of backups on the same folder.
I would like to use Maint Wizard to backup data and tlog on the same backup
device due to its other helpful features like integrity checks,
optimizations, etc.
Given my scenario, could you please guide me how to achieve this so that I
don't have several copies of the backup files on the same folder?
Thank you so much.
--
Message posted via http://www.sqlmonster.com|||Agent set recovery model to simple for msdb at startup. This is why scheduling the job at startup
work so good.
If you append backups all the time the backup files will grow until they fill your disks. Decide
what you want to do, pick the tool (maint wiz, your own jobs with TSQL code in them having proper
overwrite etc) based on that. I do this on a consultancy basis here in Sweden, talking to the client
picking the right tool (or code) for the job etc. I don't have the time to ask questions enough to
know what I have to know and whip up the code over a newsgroup conversation, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff7a1b3783be47078fdfb81576a687a7@.SQLMonster.com...
> Create an Agent job with one jobstep:
> ALTER DATABASE msdb SET RECOVERY FULL
> Create a schedule for the job, schedule it as autostart.
> Thanks a lot. So I assume that Agent starts everyday (or Agent resets it to
> 'Simple' everyday or at some specific time), otherwise the following Data
> and Log backups on msdb would fail ?
>
> No can do. Only your client can decide that. The frequency of backup
> doesn't (ultimately) have to do with amount of data or number of users. In
> the end it is the amount of data you can afford to lose if worst come to
> worst.
> Just spoke to the client and they are willing to lose 1 hour of data.
>
> It seems that each transaction log backup is overwriting the prior tlog
> backup. That makes the tlog backups useless. You need to sit down and think
> how you want to handle this.
> Would an append on tlog help (on the same backup device) instead of
> overwrite?
>
> Maint wiz is good (for backup) in the sense that it will create a new file
> each time it does backup, and thanks to that it can delete old files. You
> can do the very same in your own TSQL code. Or you can use several backup
> devices (as I assume you want to keep a few days worth of backups).
> I use the same backup device (network folder) for each database because
> every night, those folders get backed up on tape. So I don't need to keep
> several days worth of backups on the same folder.
> I would like to use Maint Wizard to backup data and tlog on the same backup
> device due to its other helpful features like integrity checks,
> optimizations, etc.
> Given my scenario, could you please guide me how to achieve this so that I
> don't have several copies of the backup files on the same folder?
> Thank you so much.
> --
> Message posted via http://www.sqlmonster.com|||Given that my client is willing to lose the data for 1 hour, I chose to go
with the Simple Recovery Model for both user dbs and system dbs (master,
model, msdb).
Are there any concerns in backing up "system" dbs in Simple mode ?
I used Maintenance Wizard and it works fine.
--
Message posted via http://www.sqlmonster.com|||> Are there any concerns in backing up "system" dbs in Simple mode ?
Not really. The general consideration applies to system databases the same way as for user
databases; how much data can you afford to lose. So it is all about what information you have in the
system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b11b9f76f284d2198959fde3f8412f3@.SQLMonster.com...
> Given that my client is willing to lose the data for 1 hour, I chose to go
> with the Simple Recovery Model for both user dbs and system dbs (master,
> model, msdb).
> Are there any concerns in backing up "system" dbs in Simple mode ?
> I used Maintenance Wizard and it works fine.
> --
> Message posted via http://www.sqlmonster.com|||Tibor,
Thank you very much for your help.
Now, I have a very good understanding of what kind of Recovery Model to
choose, depending on one's tolerance for data loss.
I highly appreciate for sharing your wisdom.
Sameer.
--
Message posted via http://www.sqlmonster.com|||At work we have one rule. Don't expect the most recent backup to be good,
but rather the one before that. You never know what could lead to data loss,
and probably the last backup is too late if you have corruption.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f03b84bcad554daa936eaad4548e735d@.SQLMonster.com...
> Tibor,
> Thank you very much for your help.
> Now, I have a very good understanding of what kind of Recovery Model to
> choose, depending on one's tolerance for data loss.
> I highly appreciate for sharing your wisdom.
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Good point, Mike. This is yet another advantage using log backups. Assume you have a corruption, and
that the most recent db backup is also corrupt. In many cases, you can do a last log backup, apply
the next most recent db backup and all subsequent log backup, resulting in zero data loss.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e$5Ud7KXFHA.2256@.TK2MSFTNGP14.phx.gbl...
> At work we have one rule. Don't expect the most recent backup to be good, but rather the one
> before that. You never know what could lead to data loss, and probably the last backup is too late
> if you have corruption.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:f03b84bcad554daa936eaad4548e735d@.SQLMonster.com...
>> Tibor,
>> Thank you very much for your help.
>> Now, I have a very good understanding of what kind of Recovery Model to
>> choose, depending on one's tolerance for data loss.
>> I highly appreciate for sharing your wisdom.
>> Sameer.
>> --
>> Message posted via http://www.sqlmonster.com
>|||Thanks for bringing up a very critical point.
Currently, I do database backups every hour (Simple mode) and I have
specified inside Maintenance Plan to remove last hour's backup file.
I will modify it to remove last 2 hour's instead.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.com|||Hi
Any reason you are not using transaction log dumps every xx minutes and a
full DB dump every day?
It uses so much less resources doing a transaction log backup compared to a
full DB backup. On day, the DB backup time will get to a point where it runs
longer than 1 hour due to data size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:516f642290114678b620b9579ea8b265@.SQLMonster.com...
> Thanks for bringing up a very critical point.
> Currently, I do database backups every hour (Simple mode) and I have
> specified inside Maintenance Plan to remove last hour's backup file.
> I will modify it to remove last 2 hour's instead.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Hi Mike,
Say, if I were to do Tran Log backups every 15 mins, that would dump 4
files per hour * 24 hours = 96 log files ! and thats just for 1 database.
And I have 2 SQL Servers - 2K and 7, for a total of 12 databases (system &
user dbs).
Having a dependency on 96 files is somewhat uneasing for me and I'm sure as
soon as my Network Admin sees those files in every database-sub-folder,
he'll freak out and create a hoopla.
But you are correct that a log backup consumes less resources and its much
quicker than doing a full database backup every hour.
My biggest database size so far is 290 MB and it usually takes 2 mins & 50
secs.
What do you think ?
--
Message posted via http://www.sqlmonster.com

Backup Strategy Clarifications

Hello,
I just wanted to check if my backup strategy is on the right-track or not,
so that I can improve on it. I am using SQL 2000 SP4.
I have the following backup strategy in place: -
STEP 1: MODEL
==========
Database: model
Recovery Model: Full
Log Backup Frequency: Once a day at 20:00
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:05
Database Backup Mode: Overwritten
STEP 2: MSDB
==========
Database: msdb
Recovery Model: Simple
Log Backup Frequency: N/A
Log Backup Mode: N/A
Database Backup Frequency: Once a day at 20:05 (same time as model)
Database Backup Mode: Overwritten
STEP 3: MASTER
===========
Database: msdb
Recovery Model: Full
Log Backup Frequency: Once a day at 20:08
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:10
Database Backup Mode: Overwritten
STEP 4: TEMPDB
===========
Database: tempdb
Recovery Model: Simple
I am not doing any backups on this database!
STEP 5: LIVE
===========
Database: live
Recovery Model: Full
I am doing 2 types of Log backups on all User databases:
Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
10:00 AM, 11:00 AM)
Type 2. Log Backup Mode: Overwritten
Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
10:05 AM, 11:05 AM)
Database Backup Mode: Overwritten
My reasoning was that I do frequent log backups (appended) every 20 mins
and do 1 log backup (overwritten) when it reaches the exact hour. Then
after 5 mins past the hour, do a Full Database backup (overwritten).
Please mention STEP 1, STEP 2, etc. to refer to the proper item and provide
your suggestions.
Thanks,
Sameer.
Message posted via http://www.droptable.com
Hi,
Your strategy is good enough, but instead of 2 types of transaction log
backups I recommend you to perform only one type of transaction log
backup, but reduce the frequency of trqansaction log backup to 10 Minutes.
Since you are performing the Transaction log backup it is not
required to a full database backup every hour. Full database backup once a
day is more thana enough:-
Steps:-
1. System Database backup strategy is good enough
2. For Live db
A. Schedule a full database backup at 02 AM
B. Schedule the Transaction log backup from 02:15 AM to 01:50 AM every 10
Minutes.
Best way is use the database Maintenence plan to create the jobs for this.
This will create unique transaction log backup files. You could also
enable house keeping mechanism.
Thanks
Hari
SQL SERVER MVP
"Sameer Premji via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:d0f492b8d6dd48f497b96708d471369d@.droptable.co m...
> Hello,
> I just wanted to check if my backup strategy is on the right-track or not,
> so that I can improve on it. I am using SQL 2000 SP4.
> I have the following backup strategy in place: -
>
> STEP 1: MODEL
> ==========
> Database: model
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:00
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:05
> Database Backup Mode: Overwritten
>
> STEP 2: MSDB
> ==========
> Database: msdb
> Recovery Model: Simple
> Log Backup Frequency: N/A
> Log Backup Mode: N/A
> Database Backup Frequency: Once a day at 20:05 (same time as model)
> Database Backup Mode: Overwritten
>
> STEP 3: MASTER
> ===========
> Database: msdb
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:08
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:10
> Database Backup Mode: Overwritten
>
> STEP 4: TEMPDB
> ===========
> Database: tempdb
> Recovery Model: Simple
> I am not doing any backups on this database!
>
> STEP 5: LIVE
> ===========
> Database: live
> Recovery Model: Full
> I am doing 2 types of Log backups on all User databases:
> Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
> hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
> Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
>
> Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
> 10:00 AM, 11:00 AM)
> Type 2. Log Backup Mode: Overwritten
> Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
> 10:05 AM, 11:05 AM)
> Database Backup Mode: Overwritten
>
> My reasoning was that I do frequent log backups (appended) every 20 mins
> and do 1 log backup (overwritten) when it reaches the exact hour. Then
> after 5 mins past the hour, do a Full Database backup (overwritten).
>
> Please mention STEP 1, STEP 2, etc. to refer to the proper item and
> provide
> your suggestions.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com
|||Thanks Hari but I have more questions: -
1. What is the best practice ? - Perform Log backup first and then Database
backup or vice-versa?
2. For System databases, you mentioned it was good enough. So the
sequence/order of backing model first, then msdb, then master is OK or
should I change the order ?
3. For User databases, you mentioned that I should do a Log backup every 10
mins and Database backup once a day. What kind of backup mode should I use
for Log backup - Overwritten or Appended ?
4. By Performing Log backups every 10 mins, will it increase the size of
the Tran Log file ?
- If yes, how can I control the size automatically without performing a
shrink on the database (I read bad things about shrinking as it defragments
pages & disk)?
Thanks,
Sameer.
Message posted via http://www.droptable.com
|||> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because backup history is in msdb
and I want to have backup of latest backup history. I also do log backup for msdb, but since Agent
sets msdb to simple recovery, I have a job that Agent autostart to set it to full. Some think that
this is overkill, though.

> 3. For User databases, you mentioned that I should do a Log backup every 10
> mins and Database backup once a day.
Be aware that you can potentially have a situation where you need to restore 24*6 = 144 log backups.
I.e., latest db backup and all subsequent log backups. This might be fine with you, but you should
be aware of it. One thing to consider is to do a differential backup perhaps every 2 hours.

> What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
What is important is that you need all log backups since latest database backup in order to do
restore. And, you also want some generations of backups (a few days back or so). Etc. Based on this,
you can then plan how you do your backups, new devices all the time, or re-use same device (with
append), etc.

> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied everytime you backup the
log.
I suggest you spend an hour or two reading the sections in Books Online that deals with backup and
restore. Just to you feel confident that you understand the backup and restore architecture in SQL
Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:1a93f786c3c5435eafa4d96f81eb5393@.droptable.co m...
> Thanks Hari but I have more questions: -
> 1. What is the best practice ? - Perform Log backup first and then Database
> backup or vice-versa?
>
> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
>
> 3. For User databases, you mentioned that I should do a Log backup every 10
> mins and Database backup once a day. What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
>
> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
> - If yes, how can I control the size automatically without performing a
> shrink on the database (I read bad things about shrinking as it defragments
> pages & disk)?
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com
|||Hello Mr. Karaszi,
Just to let you know that I have had your site bookmarked since a couple of
yrs ago as its extremely useful. Thanks for your genuine advise.
From the suggestions and reading many forum threads on here, I'm realizing
that my Backup/Restore logic and how I'm doing it, is not up to par.
(A.)
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
backup history is in msdb and I want to have backup of latest backup
history. I also do log backup for msdb, but since Agent sets msdb to simple
recovery, I have a job that Agent autostart to set it to full. Some think
that this is overkill, though.
I think your sequence makes a lot of sense. For msdb, I have had this
problem of recovery model being reset from Full to Simple automatically, in
the past. Then I gave up and just kept it as Simple. But I'm curious if you
could share how and when exactly you are resetting msdb's recovery model.
(B.)
Be aware that you can potentially have a situation where you need to
restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
log backups. This might be fine with you, but you should
be aware of it. One thing to consider is to do a differential backup
perhaps every 2 hours.
I think 144 log backups is overkill. My current Userdb size is 289MB (since
2 yrs!) and it is used daily by approx. 100 employees via our data-driven
web intranet. Given the scenario, what are your recommendations ?
(C.)
What is important is that you need all log backups since latest database
backup in order to do restore. And, you also want some generations of
backups (a few days back or so). Etc. Based on this, you can then plan how
you do your backups, new devices all the time, or re-use same device (with
append), etc.
I'll tell you exactly how I established my backup jobs.
1. I created a group of folders on our backup server (not SQL Server box),
each named after the corresponding database in SQL Server.
2. Inside SQL Server, I created 2 named Backup devices for each database,
pointing to its corresponding network folder. I created 1 for Database
Backup file and 1 for Tran Backup Log file.
e.g
deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb1Data.bak
deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.bak
..
..
..
etc.
3. For Full Database backup, I selected the database in question, right-
click, All Tasks, Backup Database..., General Tab, selected 'Database
Complete', selected the appropriate Backup Device name (suffix with 'Data')
, selected Overwritten, scheduled it to run every hour. Options tab,
selected 1st, 3rd and 4th checkbox.
I repeated the same step 3 for Transaction Log and selected Overwritten
that runs every 15 mins.
As a result, what I end up having is only 2 files for each database
constantly being overwritten. I don't have "series" of Data and Log backup
files.
As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
development machine (as I haven't used this wizard at all) and it created a
"series" of data and log backup files with a timestamp at the end.
Should I use this wizard and if so, how do I limit/recycle/overwrite the
number of data and log files created with unique timestamps ?
(D.)
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
everytime you backup the log.
As I understand, the log gets emptied but the physical file size doesn't
shrink right ? I read your article about NOT shrinking the file.
Message posted via http://www.droptable.com
|||> Just to let you know that I have had your site bookmarked since a couple of
> yrs ago as its extremely useful.
I'm happy to hear that.

> But I'm curious if you
> could share how and when exactly you are resetting msdb's recovery model.
Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.

> I think 144 log backups is overkill. My current Userdb size is 289MB (since
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
No can do. Only your client can decide that. The frequency of backup doesn't (ultimately) have to do
with amount of data or number of users. In the end it is the amount of data you can afford to lose
if worst come to worst.

> I'll tell you exactly how I established my backup jobs.
<snip>
It seems that each transaction log backup is overwriting the prior tlog backup. That makes the tlog
backups useless. You need to sit down and think how you want to handle this. Maint wiz is good (for
backup) in the sense that it will create a new file each time it does backup, and thanks to that it
can delete old files. You can do the very same in your own TSQL code. Or you can use several backup
devices (as I assume you want to keep a few days worth of backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:d4e1768dbf05435ca12c31fd4d9b9551@.droptable.co m...
> Hello Mr. Karaszi,
> Just to let you know that I have had your site bookmarked since a couple of
> yrs ago as its extremely useful. Thanks for your genuine advise.
> From the suggestions and reading many forum threads on here, I'm realizing
> that my Backup/Restore logic and how I'm doing it, is not up to par.
> (A.)
> I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
> backup history is in msdb and I want to have backup of latest backup
> history. I also do log backup for msdb, but since Agent sets msdb to simple
> recovery, I have a job that Agent autostart to set it to full. Some think
> that this is overkill, though.
> I think your sequence makes a lot of sense. For msdb, I have had this
> problem of recovery model being reset from Full to Simple automatically, in
> the past. Then I gave up and just kept it as Simple. But I'm curious if you
> could share how and when exactly you are resetting msdb's recovery model.
>
> (B.)
> Be aware that you can potentially have a situation where you need to
> restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
> log backups. This might be fine with you, but you should
> be aware of it. One thing to consider is to do a differential backup
> perhaps every 2 hours.
> I think 144 log backups is overkill. My current Userdb size is 289MB (since
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
>
> (C.)
> What is important is that you need all log backups since latest database
> backup in order to do restore. And, you also want some generations of
> backups (a few days back or so). Etc. Based on this, you can then plan how
> you do your backups, new devices all the time, or re-use same device (with
> append), etc.
> I'll tell you exactly how I established my backup jobs.
> 1. I created a group of folders on our backup server (not SQL Server box),
> each named after the corresponding database in SQL Server.
> 2. Inside SQL Server, I created 2 named Backup devices for each database,
> pointing to its corresponding network folder. I created 1 for Database
> Backup file and 1 for Tran Backup Log file.
> e.g
> deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb1Data.bak
> deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
> deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
> deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.bak
> .
> .
> .
> etc.
> 3. For Full Database backup, I selected the database in question, right-
> click, All Tasks, Backup Database..., General Tab, selected 'Database
> Complete', selected the appropriate Backup Device name (suffix with 'Data')
> , selected Overwritten, scheduled it to run every hour. Options tab,
> selected 1st, 3rd and 4th checkbox.
> I repeated the same step 3 for Transaction Log and selected Overwritten
> that runs every 15 mins.
> As a result, what I end up having is only 2 files for each database
> constantly being overwritten. I don't have "series" of Data and Log backup
> files.
> As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
> development machine (as I haven't used this wizard at all) and it created a
> "series" of data and log backup files with a timestamp at the end.
> Should I use this wizard and if so, how do I limit/recycle/overwrite the
> number of data and log files created with unique timestamps ?
>
> (D.)
> Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
> everytime you backup the log.
> As I understand, the log gets emptied but the physical file size doesn't
> shrink right ? I read your article about NOT shrinking the file.
> --
> Message posted via http://www.droptable.com
|||Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.
Thanks a lot. So I assume that Agent starts everyday (or Agent resets it to
'Simple' everyday or at some specific time), otherwise the following Data
and Log backups on msdb would fail ?
No can do. Only your client can decide that. The frequency of backup
doesn't (ultimately) have to do with amount of data or number of users. In
the end it is the amount of data you can afford to lose if worst come to
worst.
Just spoke to the client and they are willing to lose 1 hour of data.
It seems that each transaction log backup is overwriting the prior tlog
backup. That makes the tlog backups useless. You need to sit down and think
how you want to handle this.
Would an append on tlog help (on the same backup device) instead of
overwrite?
Maint wiz is good (for backup) in the sense that it will create a new file
each time it does backup, and thanks to that it can delete old files. You
can do the very same in your own TSQL code. Or you can use several backup
devices (as I assume you want to keep a few days worth of backups).
I use the same backup device (network folder) for each database because
every night, those folders get backed up on tape. So I don't need to keep
several days worth of backups on the same folder.
I would like to use Maint Wizard to backup data and tlog on the same backup
device due to its other helpful features like integrity checks,
optimizations, etc.
Given my scenario, could you please guide me how to achieve this so that I
don't have several copies of the backup files on the same folder?
Thank you so much.
Message posted via http://www.droptable.com
|||Agent set recovery model to simple for msdb at startup. This is why scheduling the job at startup
work so good.
If you append backups all the time the backup files will grow until they fill your disks. Decide
what you want to do, pick the tool (maint wiz, your own jobs with TSQL code in them having proper
overwrite etc) based on that. I do this on a consultancy basis here in Sweden, talking to the client
picking the right tool (or code) for the job etc. I don't have the time to ask questions enough to
know what I have to know and whip up the code over a newsgroup conversation, I'm afraid.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:ff7a1b3783be47078fdfb81576a687a7@.droptable.co m...
> Create an Agent job with one jobstep:
> ALTER DATABASE msdb SET RECOVERY FULL
> Create a schedule for the job, schedule it as autostart.
> Thanks a lot. So I assume that Agent starts everyday (or Agent resets it to
> 'Simple' everyday or at some specific time), otherwise the following Data
> and Log backups on msdb would fail ?
>
> No can do. Only your client can decide that. The frequency of backup
> doesn't (ultimately) have to do with amount of data or number of users. In
> the end it is the amount of data you can afford to lose if worst come to
> worst.
> Just spoke to the client and they are willing to lose 1 hour of data.
>
> It seems that each transaction log backup is overwriting the prior tlog
> backup. That makes the tlog backups useless. You need to sit down and think
> how you want to handle this.
> Would an append on tlog help (on the same backup device) instead of
> overwrite?
>
> Maint wiz is good (for backup) in the sense that it will create a new file
> each time it does backup, and thanks to that it can delete old files. You
> can do the very same in your own TSQL code. Or you can use several backup
> devices (as I assume you want to keep a few days worth of backups).
> I use the same backup device (network folder) for each database because
> every night, those folders get backed up on tape. So I don't need to keep
> several days worth of backups on the same folder.
> I would like to use Maint Wizard to backup data and tlog on the same backup
> device due to its other helpful features like integrity checks,
> optimizations, etc.
> Given my scenario, could you please guide me how to achieve this so that I
> don't have several copies of the backup files on the same folder?
> Thank you so much.
> --
> Message posted via http://www.droptable.com
|||Given that my client is willing to lose the data for 1 hour, I chose to go
with the Simple Recovery Model for both user dbs and system dbs (master,
model, msdb).
Are there any concerns in backing up "system" dbs in Simple mode ?
I used Maintenance Wizard and it works fine.
Message posted via http://www.droptable.com
|||> Are there any concerns in backing up "system" dbs in Simple mode ?
Not really. The general consideration applies to system databases the same way as for user
databases; how much data can you afford to lose. So it is all about what information you have in the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:4b11b9f76f284d2198959fde3f8412f3@.droptable.co m...
> Given that my client is willing to lose the data for 1 hour, I chose to go
> with the Simple Recovery Model for both user dbs and system dbs (master,
> model, msdb).
> Are there any concerns in backing up "system" dbs in Simple mode ?
> I used Maintenance Wizard and it works fine.
> --
> Message posted via http://www.droptable.com
sql