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
Thursday, March 29, 2012
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
> > >
> > >
> > >
>
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
>
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
>
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:
Hi there,
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.
>
>
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
>
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
>
Subscribe to:
Posts (Atom)