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

Hello,
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
== * Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
== * Differential database backup of ALL databases
Daily, Hourly
== * Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
ShawnHi,
> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:
> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==> * Differential database backup of ALL databases
> Daily, Hourly
> ==> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>

Backup strategy

Hello,
I manage a SQL Server that requires frequent backups to insure not
much data is lost in the event of a failure. Here is how backups are
performed currently:
Sunday, 1:00am
==
* Full database backup of ALL databases
* Transaction log backup WITH INIT
Mon-Sat, 1:00am
==
* Differential database backup of ALL databases
Daily, Hourly
==
* Transaction log backup
I am currently having 2 problems. The first problem is that sometimes
on Sunday mornings when the job attempts to backup the transactions
logs WITH INIT the job will fail because the Daily, Hourly process is
also backing up at the same time. The second problem is that the
transaction log backups seem to grow larger and larger and take longer
and longer to complete. Is it really necessary to store the entire
week of transaction log backups when I'll always have a differential I
can apply for each day? The only purpose of the transaction log
backups is to insure we can restore to the latest point in time before
a failure occurs. There isn't a need to revert back to more than the
previous day's transaction log backup.
Is what I'm doing the best way? How can this be improved upon?
Thank you in advance for your time and efforts.
Shawn
Hi,

> Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
Based on these requirements you do not need an entire week of transaction
log backups. You just need the transaction log backups taken after your last
differential backup.
But most important, test your strategy restoring your database to another
database server using this combination of full, differential and transaction
log backups.
Hope this helps,
Ben Nevarez
"ITistic" wrote:

> Hello,
> I manage a SQL Server that requires frequent backups to insure not
> much data is lost in the event of a failure. Here is how backups are
> performed currently:
> Sunday, 1:00am
> ==
> * Full database backup of ALL databases
> * Transaction log backup WITH INIT
> Mon-Sat, 1:00am
> ==
> * Differential database backup of ALL databases
> Daily, Hourly
> ==
> * Transaction log backup
> I am currently having 2 problems. The first problem is that sometimes
> on Sunday mornings when the job attempts to backup the transactions
> logs WITH INIT the job will fail because the Daily, Hourly process is
> also backing up at the same time. The second problem is that the
> transaction log backups seem to grow larger and larger and take longer
> and longer to complete. Is it really necessary to store the entire
> week of transaction log backups when I'll always have a differential I
> can apply for each day? The only purpose of the transaction log
> backups is to insure we can restore to the latest point in time before
> a failure occurs. There isn't a need to revert back to more than the
> previous day's transaction log backup.
> Is what I'm doing the best way? How can this be improved upon?
> Thank you in advance for your time and efforts.
> Shawn
>

Backup Strategy

Hi,
for backups, do we need to back up the Temp DB as well?
What restoration strategy is recommended in the event of Reporting Services
failure?
regards
MattYes, it is a good idea if you want to avoid have to install RS again to
recreate the database.
Backup the ReportServer database, the encryption keys, and the config files
for you report manager and server.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
http://support.microsoft.com/?kbid=842425
--
erik perez
www.solien.com
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi,
> for backups, do we need to back up the Temp DB as well?
> What restoration strategy is recommended in the event of Reporting
Services
> failure?
> regards
> Matt
>|||Thanks Erik. Great!!
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:OEmiesOlEHA.596@.TK2MSFTNGP11.phx.gbl...
> Yes, it is a good idea if you want to avoid have to install RS again to
> recreate the database.
> Backup the ReportServer database, the encryption keys, and the config
files
> for you report manager and server.
> See:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
> http://support.microsoft.com/?kbid=842425
> --
> erik perez
> www.solien.com
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uJhSpvIlEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > for backups, do we need to back up the Temp DB as well?
> >
> > What restoration strategy is recommended in the event of Reporting
> Services
> > failure?
> >
> > regards
> >
> > Matt
> >
> >
>

Backup Strategies

Just wondering what some of the backup strategies are out there. Full
backups, log backups, filegroup backups? What are the sizes of your
databases?
Thanks just looking for some insight on what other people are doing with
large data warehouse databases.
Have a good day,
Jim
FYI
we use different strategies depending on the function of the database, eg
for our datawarehouse (approx 120 GB) I use the simple model, with online
backup of the db every night (takes 1,5 hours), before the nightjobs run. We
can always re-run the nightjobs from a give point in time so this scheme
will do.
For high availability production databases, I do log backups every 60
minutes, and a night backup of the db's. That way, the loss of data should
be maximum 60 minutes should we need to restore the database after a fatal
crash. Alse, the frequent backup of the logs keeps their size relatively
small (up to about 5 GB), backup of logs is first to disk for speed, then to
tape for safety.
Hope this is of some use to you...
"JimW" <JimW@.discussions.microsoft.com> schreef in bericht
news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
> Just wondering what some of the backup strategies are out there. Full
> backups, log backups, filegroup backups? What are the sizes of your
> databases?
> Thanks just looking for some insight on what other people are doing with
> large data warehouse databases.
> --
> Have a good day,
> Jim
|||might be worth looking at SQLLitespeed if you have large backups
http://www.imceda.com/
Andy
"JimW" <JimW@.discussions.microsoft.com> wrote in message
news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
> Just wondering what some of the backup strategies are out there. Full
> backups, log backups, filegroup backups? What are the sizes of your
> databases?
> Thanks just looking for some insight on what other people are doing with
> large data warehouse databases.
> --
> Have a good day,
> Jim
|||Just wondering, why backup the datawarehouse? Normally the data come from
other sources anyway, so it should be suficient to backup the production
db's. Now, what I do is I transfer, without data all the object to a backup
database and backup this every night so that I maintain the structure only.
That saves about 50% of the space.
T
"admin" <admin@.wol.be> escreveu na mensagem
news:412f713f$0$321$ba620e4c@.news.skynet.be...
> FYI
> we use different strategies depending on the function of the database, eg
> for our datawarehouse (approx 120 GB) I use the simple model, with online
> backup of the db every night (takes 1,5 hours), before the nightjobs run.
We
> can always re-run the nightjobs from a give point in time so this scheme
> will do.
> For high availability production databases, I do log backups every 60
> minutes, and a night backup of the db's. That way, the loss of data should
> be maximum 60 minutes should we need to restore the database after a fatal
> crash. Alse, the frequent backup of the logs keeps their size relatively
> small (up to about 5 GB), backup of logs is first to disk for speed, then
to
> tape for safety.
> Hope this is of some use to you...
>
> "JimW" <JimW@.discussions.microsoft.com> schreef in bericht
> news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
>

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.com
Have you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>
sql

Backup Strategies

Just wondering what some of the backup strategies are out there. Full
backups, log backups, filegroup backups? What are the sizes of your
databases?
Thanks just looking for some insight on what other people are doing with
large data warehouse databases.
--
Have a good day,
JimFYI
we use different strategies depending on the function of the database, eg
for our datawarehouse (approx 120 GB) I use the simple model, with online
backup of the db every night (takes 1,5 hours), before the nightjobs run. We
can always re-run the nightjobs from a give point in time so this scheme
will do.
For high availability production databases, I do log backups every 60
minutes, and a night backup of the db's. That way, the loss of data should
be maximum 60 minutes should we need to restore the database after a fatal
crash. Alse, the frequent backup of the logs keeps their size relatively
small (up to about 5 GB), backup of logs is first to disk for speed, then to
tape for safety.
Hope this is of some use to you...
"JimW" <JimW@.discussions.microsoft.com> schreef in bericht
news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
> Just wondering what some of the backup strategies are out there. Full
> backups, log backups, filegroup backups? What are the sizes of your
> databases?
> Thanks just looking for some insight on what other people are doing with
> large data warehouse databases.
> --
> Have a good day,
> Jim|||might be worth looking at SQLLitespeed if you have large backups
http://www.imceda.com/
Andy
"JimW" <JimW@.discussions.microsoft.com> wrote in message
news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
> Just wondering what some of the backup strategies are out there. Full
> backups, log backups, filegroup backups? What are the sizes of your
> databases?
> Thanks just looking for some insight on what other people are doing with
> large data warehouse databases.
> --
> Have a good day,
> Jim|||Just wondering, why backup the datawarehouse? Normally the data come from
other sources anyway, so it should be suficient to backup the production
db's. Now, what I do is I transfer, without data all the object to a backup
database and backup this every night so that I maintain the structure only.
That saves about 50% of the space.
T
"admin" <admin@.wol.be> escreveu na mensagem
news:412f713f$0$321$ba620e4c@.news.skynet.be...
> FYI
> we use different strategies depending on the function of the database, eg
> for our datawarehouse (approx 120 GB) I use the simple model, with online
> backup of the db every night (takes 1,5 hours), before the nightjobs run.
We
> can always re-run the nightjobs from a give point in time so this scheme
> will do.
> For high availability production databases, I do log backups every 60
> minutes, and a night backup of the db's. That way, the loss of data should
> be maximum 60 minutes should we need to restore the database after a fatal
> crash. Alse, the frequent backup of the logs keeps their size relatively
> small (up to about 5 GB), backup of logs is first to disk for speed, then
to
> tape for safety.
> Hope this is of some use to you...
>
> "JimW" <JimW@.discussions.microsoft.com> schreef in bericht
> news:1B4D84BE-5D56-40E1-BFED-859C829F5C01@.microsoft.com...
>

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> nice
>

Backup Strategies

Our environment is development and data warehousing with many non logged
trasactions. Our recover model is simple and our backups are always full
backups.
My question is:
Is there anything in sql that is comparable to the archive flag of a file?
We do have some databases that might not change in a week. It would be nice
not to have to back them up
Robert Alexander
Robert.Alexander@.cca-audit.comHave you looked at differential backups? They only backup the pages that
have changed in the DB, no matter if you are in full, simple or bulk load
mode.
This assumes that you have access to the last full backup.
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/
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Our environment is development and data warehousing with many non logged
> trasactions. Our recover model is simple and our backups are always full
> backups.
> My question is:
> Is there anything in sql that is comparable to the archive flag of a file?
> We do have some databases that might not change in a week. It would be
nice
> not to have to back them up
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>|||No. But I will. I thought differential just backed up the full log.
Thanks for the tip.
Rob
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:um2yVONyEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Have you looked at differential backups? They only backup the pages that
> have changed in the DB, no matter if you are in full, simple or bulk load
> mode.
> This assumes that you have access to the last full backup.
> 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/
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ua#yYMNyEHA.3804@.TK2MSFTNGP10.phx.gbl...
>> Our environment is development and data warehousing with many non logged
>> trasactions. Our recover model is simple and our backups are always
>> full
>> backups.
>> My question is:
>> Is there anything in sql that is comparable to the archive flag of a
>> file?
>> We do have some databases that might not change in a week. It would be
> nice
>> not to have to back them up
>> Robert Alexander
>> Robert.Alexander@.cca-audit.com
>>
>

Backup Standards and Suggestions

Hi Folks,
I haven't worked at multiple SQL Shops so question for those who have;
SQL Backups, which is the perfered way using SQL Server to do the backups or
to use a 3rd backup agent like Backup Execs SQL Agent to do backups?
Right now we use SQL Server to do daily backup to .BAK file; which later
server ops write to tape. And continously we keep running into space
issues; so it was suggested we use Backup Execs SQL Agent to do backups
directly to tape. But I have never used Backup Exec so I couldn't really
say which is better. I know SQL Server has ability to write directly to
tapes, again not something I have used.
Please any insight. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 20053rd party backup softwares has compression feature mostly. So, that may help
you about the storage issues. Check out trial versions of those softwares
and perform your tests. However, compressing databases will be available
with SQL Server 2008.
One tip regarding to taking backups to Tapes... Tapes should be connected to
your SQL Server server directly, you can not take backup to tape when the
tape is on another server.
Taking once full backup daily is not appropriate for most data critical
production servers. First you need to find out what's the tolarable losing
of data for that company. If they say "we do not have any tolerance to lose
any data" (which is quite normal) then you'll use Transaction Log backups
too. So keep the count of Transaction Log backups minimum, you'll need to
use Differential Backups as well. However, you know, it depends...
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:9ED52060-6CAA-423C-B800-D0CECF8838CE@.microsoft.com...
> Hi Folks,
> I haven't worked at multiple SQL Shops so question for those who have;
> SQL Backups, which is the perfered way using SQL Server to do the backups
> or
> to use a 3rd backup agent like Backup Execs SQL Agent to do backups?
> Right now we use SQL Server to do daily backup to .BAK file; which later
> server ops write to tape. And continously we keep running into space
> issues; so it was suggested we use Backup Execs SQL Agent to do backups
> directly to tape. But I have never used Backup Exec so I couldn't really
> say which is better. I know SQL Server has ability to write directly to
> tapes, again not something I have used.
> Please any insight. Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||Thanks Ekrem.
In your opinion which one is better, using backup to disk? Or directly to
tape; considering I can get tape drive hooked directly to SQL Server.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||It depends on the scenarios. You can see some scenarios from the following
articles.
Some of them:
http://www.smallbusinesscomputing.com/testdrive/article.php/3528326
http://www.storagesearch.com/engenio-art2.html
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:79E1C65C-5732-4957-B18B-14B765A9D558@.microsoft.com...
> Thanks Ekrem.
> In your opinion which one is better, using backup to disk? Or directly to
> tape; considering I can get tape drive hooked directly to SQL Server.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||Thanks! Those are good links, now I can backup some of what I say to
management ;-). Thanks again.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||I'm glad they helped, good luck! =)
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:59BFA06B-DCAE-4840-8CBF-2C36A946D056@.microsoft.com...
> Thanks! Those are good links, now I can backup some of what I say to
> management ;-). Thanks again.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005

Backup SQL to both TAPE AND DISK

Here's my problem. I want to perform occasional full backups on a tape but I
also want to backup to disk. The disk backup does a full backup every night
and trans log backups every half hour between 7 AM and 7 PM. The tape backup
(Veritas) just performs a full backup every day. Now when I restore, I don't
want my disk backup to "depend" on my tape backup. In other words, I don't
want to see the tape backup in the SQL backup log when I'm trying to
performs a restore from the disk backups. Is there a way to do this?
Thanks
jBA database backup doesn't break the chain of log backups so you are fine. Yo
u can apply any of the
database backups as long as you apply all subsequent log backups in sequence
. But why not skip the
Veritas SQL Server agent and let Veritas pick up the database backup files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"msnews.microsoft.com" <jbonds@.XXX.com> wrote in message
news:O2XVi3BWGHA.4960@.TK2MSFTNGP05.phx.gbl...
> Here's my problem. I want to perform occasional full backups on a tape but
I also want to backup
> to disk. The disk backup does a full backup every night and trans log back
ups every half hour
> between 7 AM and 7 PM. The tape backup (Veritas) just performs a full back
up every day. Now when I
> restore, I don't want my disk backup to "depend" on my tape backup. In oth
er words, I don't want
> to see the tape backup in the SQL backup log when I'm trying to performs a
restore from the disk
> backups. Is there a way to do this?
> Thanks
> jB
>|||msnews.microsoft.com wrote:
> Here's my problem. I want to perform occasional full backups on a tape but
I
> also want to backup to disk. The disk backup does a full backup every nigh
t
> and trans log backups every half hour between 7 AM and 7 PM. The tape back
up
> (Veritas) just performs a full backup every day. Now when I restore, I don
't
> want my disk backup to "depend" on my tape backup. In other words, I don't
> want to see the tape backup in the SQL backup log when I'm trying to
> performs a restore from the disk backups. Is there a way to do this?
> Thanks
> jB
Unless you are also doing Differential backups, nothing will "depend"
on your full backup to tape.
Why do you care what is in the backup log (I assume you mean the
backupfile table)? If you aren't using Maintenance Plans you can safely
delete from that table anyway so maybe you could just delete stuff you
don't want to see.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Backup SQL Server to another server - abnormal termination

I have just changed some SQL Server database backups to back up files
directly to another server (other than where SQL Server resides), and
I'm getting an 'abnormal termination' message. But it looks like the
backups have been completed. I'm able to restore the database from the
backup created and there appears to be no problem. Does anyone know
why the abnormal termination message is appearing? If I'm able to
restore successfully from the backup, is there something else I should
be looking for to verify that the backup really did complete?
Thanks
Here is the checklist:
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163024266.615317.129380@.f16g2000cwb.googlegr oups.com...
>I have just changed some SQL Server database backups to back up files
> directly to another server (other than where SQL Server resides), and
> I'm getting an 'abnormal termination' message. But it looks like the
> backups have been completed. I'm able to restore the database from the
> backup created and there appears to be no problem. Does anyone know
> why the abnormal termination message is appearing? If I'm able to
> restore successfully from the backup, is there something else I should
> be looking for to verify that the backup really did complete?
> Thanks
>
|||I've checked out the requirements and all have been met, yet I'm still
getting the message that the backup is failing. The backup files are
created, and I'm able to restore from them, but I'm concerned that
something still isn't happening properly and once a production database
needs to be restored I will have problems.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Here is the checklist:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> <innis1@.sbcglobal.net> wrote in message
> news:1163024266.615317.129380@.f16g2000cwb.googlegr oups.com...
|||The only other thing I have seen is if you are doing a Backup with verify,
the SQL server can ask for the file to be opened faster than the file server
can close and reopen it. Try removing the verify option (your restore tests
are the only true verification anyway) and see if that helps.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163702473.811707.94770@.h54g2000cwb.googlegro ups.com...
> I've checked out the requirements and all have been met, yet I'm still
> getting the message that the backup is failing. The backup files are
> created, and I'm able to restore from them, but I'm concerned that
> something still isn't happening properly and once a production database
> needs to be restored I will have problems.
>
> Geoff N. Hiten wrote:
>
sql

Backup SQL Server to another server - abnormal termination

I have just changed some SQL Server database backups to back up files
directly to another server (other than where SQL Server resides), and
I'm getting an 'abnormal termination' message. But it looks like the
backups have been completed. I'm able to restore the database from the
backup created and there appears to be no problem. Does anyone know
why the abnormal termination message is appearing? If I'm able to
restore successfully from the backup, is there something else I should
be looking for to verify that the backup really did complete?
ThanksHere is the checklist:
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163024266.615317.129380@.f16g2000cwb.googlegroups.com...
>I have just changed some SQL Server database backups to back up files
> directly to another server (other than where SQL Server resides), and
> I'm getting an 'abnormal termination' message. But it looks like the
> backups have been completed. I'm able to restore the database from the
> backup created and there appears to be no problem. Does anyone know
> why the abnormal termination message is appearing? If I'm able to
> restore successfully from the backup, is there something else I should
> be looking for to verify that the backup really did complete?
> Thanks
>|||I've checked out the requirements and all have been met, yet I'm still
getting the message that the backup is failing. The backup files are
created, and I'm able to restore from them, but I'm concerned that
something still isn't happening properly and once a production database
needs to be restored I will have problems.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Here is the checklist:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> <innis1@.sbcglobal.net> wrote in message
> news:1163024266.615317.129380@.f16g2000cwb.googlegroups.com...|||The only other thing I have seen is if you are doing a Backup with verify,
the SQL server can ask for the file to be opened faster than the file server
can close and reopen it. Try removing the verify option (your restore tests
are the only true verification anyway) and see if that helps.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163702473.811707.94770@.h54g2000cwb.googlegroups.com...
> I've checked out the requirements and all have been met, yet I'm still
> getting the message that the backup is failing. The backup files are
> created, and I'm able to restore from them, but I'm concerned that
> something still isn't happening properly and once a production database
> needs to be restored I will have problems.
>
> Geoff N. Hiten wrote:
>

Backup SQL Server to another server - abnormal termination

I have just changed some SQL Server database backups to back up files
directly to another server (other than where SQL Server resides), and
I'm getting an 'abnormal termination' message. But it looks like the
backups have been completed. I'm able to restore the database from the
backup created and there appears to be no problem. Does anyone know
why the abnormal termination message is appearing? If I'm able to
restore successfully from the backup, is there something else I should
be looking for to verify that the backup really did complete?
ThanksHere is the checklist:
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163024266.615317.129380@.f16g2000cwb.googlegroups.com...
>I have just changed some SQL Server database backups to back up files
> directly to another server (other than where SQL Server resides), and
> I'm getting an 'abnormal termination' message. But it looks like the
> backups have been completed. I'm able to restore the database from the
> backup created and there appears to be no problem. Does anyone know
> why the abnormal termination message is appearing? If I'm able to
> restore successfully from the backup, is there something else I should
> be looking for to verify that the backup really did complete?
> Thanks
>|||I've checked out the requirements and all have been met, yet I'm still
getting the message that the backup is failing. The backup files are
created, and I'm able to restore from them, but I'm concerned that
something still isn't happening properly and once a production database
needs to be restored I will have problems.
Geoff N. Hiten wrote:
> Here is the checklist:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> <innis1@.sbcglobal.net> wrote in message
> news:1163024266.615317.129380@.f16g2000cwb.googlegroups.com...
> >I have just changed some SQL Server database backups to back up files
> > directly to another server (other than where SQL Server resides), and
> > I'm getting an 'abnormal termination' message. But it looks like the
> > backups have been completed. I'm able to restore the database from the
> > backup created and there appears to be no problem. Does anyone know
> > why the abnormal termination message is appearing? If I'm able to
> > restore successfully from the backup, is there something else I should
> > be looking for to verify that the backup really did complete?
> >
> > Thanks
> >|||The only other thing I have seen is if you are doing a Backup with verify,
the SQL server can ask for the file to be opened faster than the file server
can close and reopen it. Try removing the verify option (your restore tests
are the only true verification anyway) and see if that helps.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<innis1@.sbcglobal.net> wrote in message
news:1163702473.811707.94770@.h54g2000cwb.googlegroups.com...
> I've checked out the requirements and all have been met, yet I'm still
> getting the message that the backup is failing. The backup files are
> created, and I'm able to restore from them, but I'm concerned that
> something still isn't happening properly and once a production database
> needs to be restored I will have problems.
>
> Geoff N. Hiten wrote:
>> Here is the checklist:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> <innis1@.sbcglobal.net> wrote in message
>> news:1163024266.615317.129380@.f16g2000cwb.googlegroups.com...
>> >I have just changed some SQL Server database backups to back up files
>> > directly to another server (other than where SQL Server resides), and
>> > I'm getting an 'abnormal termination' message. But it looks like the
>> > backups have been completed. I'm able to restore the database from the
>> > backup created and there appears to be no problem. Does anyone know
>> > why the abnormal termination message is appearing? If I'm able to
>> > restore successfully from the backup, is there something else I should
>> > be looking for to verify that the backup really did complete?
>> >
>> > Thanks
>> >
>

Backup SQL Files

Hi,

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

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

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

Thanks

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

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

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What "flavour" of SQL Server 2000 (e.g. Enterprise, Developer) will you
be installing on this machine? I only ask because certain flavours
won't let you install the server components on an XP machine...

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

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

Hope that helps a bit

Backup sizes

We use LiteSpeed backups and was wondering if there is a way in one of those
msdb backup tables that shows me the size of the compressed backups for each
of my dbs?
If so, whats the query?
All i want to know is name of db and its backup size.Take a look at this script:
http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
Thank you,
Saleem Hakani (World Wide SQL Server Community)
HTTP://WWW.SQLCOMMUNITY.COM
SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of SQL
Fun.
"Hassan" wrote:
> We use LiteSpeed backups and was wondering if there is a way in one of those
> msdb backup tables that shows me the size of the compressed backups for each
> of my dbs?
> If so, whats the query?
> All i want to know is name of db and its backup size.
>
>|||Saleem this script shows me the actual database size , but I am using
LiteSpeed backup and when i compare the output of your results to the size
on disk, they are different. Your output gives me the size assuming I do a
native backup and not SQL.
How can i get the sizes of the backups that I am using and in this case
LiteSpeed ? are there special LiteSpeed tables ?
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:3AE9390E-886F-4CAB-8E3B-F06E0BDB0801@.microsoft.com...
> Take a look at this script:
> http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
> Thank you,
> Saleem Hakani (World Wide SQL Server Community)
> HTTP://WWW.SQLCOMMUNITY.COM
> SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of
> SQL
> Fun.
>
> "Hassan" wrote:
>> We use LiteSpeed backups and was wondering if there is a way in one of
>> those
>> msdb backup tables that shows me the size of the compressed backups for
>> each
>> of my dbs?
>> If so, whats the query?
>> All i want to know is name of db and its backup size.
>>sql

backup set will expire

Can someone explain the option "backup set will expire" to me? I figured
this would be the option to set how many backups I want to keep. I set it
to "After 1 day", yet I have 4 days worth of backups in my backup directory.
Did I set the wrong option? I only want 1 backup.
Thanks, Andre
Andre,
Well... the others have expired, but they have not been deleted. Both SQL
Server 2000 and SQL Server 2005 Maintenance Plans have a function to delete
backup files older than some period, so you should check that out for
whatever version you are running.
RLF
"Andre" <nospam@.spam.com> wrote in message
news:%23PAzvvnuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Can someone explain the option "backup set will expire" to me? I figured
> this would be the option to set how many backups I want to keep. I set it
> to "After 1 day", yet I have 4 days worth of backups in my backup
> directory. Did I set the wrong option? I only want 1 backup.
> Thanks, Andre
>
|||This was very apparent in SQL2k, but I can't find it in SQL2k5. Where is
it?
|||Andre,
On the Maintenance Plan task designer (kind of like DTS / SSIS) drag the
Maintenance Cleanup Task and set it up to match your path, retention, etc.
RLF
"Andre" <nospam@.spam.com> wrote in message
news:u9DgHJpuHHA.3476@.TK2MSFTNGP02.phx.gbl...
> This was very apparent in SQL2k, but I can't find it in SQL2k5. Where is
> it?
>
|||The option you want to use has been pointed out to you by Russell. I just want to mention what the
option you mention is for:
EXPIREDATE will only prohibiting doing an INIT to overwrite the backup before the specified date. It
will not assist in keeping x number of old backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andre" <nospam@.spam.com> wrote in message news:%23PAzvvnuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Can someone explain the option "backup set will expire" to me? I figured this would be the option
> to set how many backups I want to keep. I set it to "After 1 day", yet I have 4 days worth of
> backups in my backup directory. Did I set the wrong option? I only want 1 backup.
> Thanks, Andre
>
|||Thank you both. I will like SQL2005 one of these days...right? Still
fumbling my way around, obviously.

backup set will expire

Can someone explain the option "backup set will expire" to me? I figured
this would be the option to set how many backups I want to keep. I set it
to "After 1 day", yet I have 4 days worth of backups in my backup directory.
Did I set the wrong option? I only want 1 backup.
Thanks, AndreAndre,
Well... the others have expired, but they have not been deleted. Both SQL
Server 2000 and SQL Server 2005 Maintenance Plans have a function to delete
backup files older than some period, so you should check that out for
whatever version you are running.
RLF
"Andre" <nospam@.spam.com> wrote in message
news:%23PAzvvnuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Can someone explain the option "backup set will expire" to me? I figured
> this would be the option to set how many backups I want to keep. I set it
> to "After 1 day", yet I have 4 days worth of backups in my backup
> directory. Did I set the wrong option? I only want 1 backup.
> Thanks, Andre
>|||This was very apparent in SQL2k, but I can't find it in SQL2k5. Where is
it? :)|||Andre,
On the Maintenance Plan task designer (kind of like DTS / SSIS) drag the
Maintenance Cleanup Task and set it up to match your path, retention, etc.
RLF
"Andre" <nospam@.spam.com> wrote in message
news:u9DgHJpuHHA.3476@.TK2MSFTNGP02.phx.gbl...
> This was very apparent in SQL2k, but I can't find it in SQL2k5. Where is
> it? :)
>|||The option you want to use has been pointed out to you by Russell. I just want to mention what the
option you mention is for:
EXPIREDATE will only prohibiting doing an INIT to overwrite the backup before the specified date. It
will not assist in keeping x number of old backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andre" <nospam@.spam.com> wrote in message news:%23PAzvvnuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> Can someone explain the option "backup set will expire" to me? I figured this would be the option
> to set how many backups I want to keep. I set it to "After 1 day", yet I have 4 days worth of
> backups in my backup directory. Did I set the wrong option? I only want 1 backup.
> Thanks, Andre
>|||Thank you both. I will like SQL2005 one of these days...right? :) Still
fumbling my way around, obviously.