Differential data backup sql2k

Hi, all
I am trying to do the data differential databackup daily and full backup
during the weekend.
Log shipping remain the same as usual.
There is anything that I should be aware of before I chnage the backup
method.
It takes too long to have full data backup every night.
Thanks,
Diff backup of the source database does not affect log shipping in any way. So, just make sure you
have thought about disaster recovery, number of backup generations and that you understand the
concept of database, diff and transaction log backups (which are pretty simple).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e0FEv70JGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> I am trying to do the data differential databackup daily and full backup during the weekend.
> Log shipping remain the same as usual.
> There is anything that I should be aware of before I chnage the backup method.
> It takes too long to have full data backup every night.
> Thanks,
>

Differential data backup sql2k

Hi, all
I am trying to do the data differential databackup daily and full backup
during the weekend.
Log shipping remain the same as usual.
There is anything that I should be aware of before I chnage the backup
method.
It takes too long to have full data backup every night.
Thanks,Diff backup of the source database does not affect log shipping in any way.
So, just make sure you
have thought about disaster recovery, number of backup generations and that
you understand the
concept of database, diff and transaction log backups (which are pretty simp
le).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e0FEv70JGHA.2012@.TK2MSFTNGP14.phx.gbl...[v
bcol=seagreen]
> Hi, all
> I am trying to do the data differential databackup daily and full backup d
uring the weekend.
> Log shipping remain the same as usual.
> There is anything that I should be aware of before I chnage the backup met
hod.
> It takes too long to have full data backup every night.
> Thanks,
>[/vbcol]

Differential data backup sql2k

Hi, all
I am trying to do the data differential databackup daily and full backup
during the weekend.
Log shipping remain the same as usual.
There is anything that I should be aware of before I chnage the backup
method.
It takes too long to have full data backup every night.
Thanks,Diff backup of the source database does not affect log shipping in any way. So, just make sure you
have thought about disaster recovery, number of backup generations and that you understand the
concept of database, diff and transaction log backups (which are pretty simple).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e0FEv70JGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> I am trying to do the data differential databackup daily and full backup during the weekend.
> Log shipping remain the same as usual.
> There is anything that I should be aware of before I chnage the backup method.
> It takes too long to have full data backup every night.
> Thanks,
>

Differential Backups Produce 55GB Files

We have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.

I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.

Has anyone experienced this issue before?
What causes it?
How do we resolve it?

Thank you in advance for your help,
Greg
if the log backups occurs every 1 hr you dont need to schedule the differential backup.......moreover check if any process is running at the time of backup......generally if you schedule the backups frequently the log files shudnt grow as high in your case........|||

I'm not worries about the size of the log files. The logs are fine. This post regards the size of the differential backup file itself (55GB). We don't really need it, true, but its much easier to restore one diff backup over six logs in my humble opinion, but this is really beside the point.

|||Was there any unusual activity over the weekend? Someone doing a bunch of bulks loads or index rebuilds for instance?|||Are you appending your differential backups to the same file, or creating a new file for each differential backup?|||Yes. Over the weekend we usually do an index rebuild, and some heavy inserts. Our database is mirrored as well, so we're not able to actually do the heavy inserts as a true bulk insert.|||Each backup creates a new file. A secondary process on another machine copies the files over to another server.|||

most probably this is coz of the activities in the weekend. When u rebiult index it take extraspace to create the fresh index . the existing index are not overwritten beacuse if system needs the existing index to rollback if necessary. Differential backup take the backup of all the extents changed from last full backup. So what i would prefer is... take full backup after the weekend maintenance instead of Differential(if you have maintenance window).

Madhu

differential backups

Hi,
Running SQL 7. We have a maintanence plan doing transaction log backups
every hour. Outside of the maintanence plan we have a job that does a
differential backup of the database. My question is, when the differential
backup occurs, is the transaction log truncated? For example if the last
transaction log backup was at 1pm, and a differential at 1:30pm, then
another transaction log backup at 2pm; will the 2pm transaction log backup
contain the transactions at 1:15pm?
Secondly, how can I modify the differential backup job below to:
a) name the filename of the diff backup file to be
<database_name>_diff_MMDDYYhhmmss.bak
b) have the script remove diff backup files older than 1 day.oops! here is the script I need to modify that I referred to below:
BACKUP DATABASE [myDB] TO DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH INIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'myDB latest diff backup', SKIP , STATS = 10,
DESCRIPTION = N'every 5 hour diff', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='myDB'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='myDB')
RESTORE VERIFYONLY FROM DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH FILE = @.i
"aaz" <aaz@.webcapacity.com> wrote in message
news:uSP7mY$SDHA.3188@.tk2msftngp13.phx.gbl...
> Hi,
> Running SQL 7. We have a maintanence plan doing transaction log backups
> every hour. Outside of the maintanence plan we have a job that does a
> differential backup of the database. My question is, when the differential
> backup occurs, is the transaction log truncated? For example if the last
> transaction log backup was at 1pm, and a differential at 1:30pm, then
> another transaction log backup at 2pm; will the 2pm transaction log backup
> contain the transactions at 1:15pm?
> Secondly, how can I modify the differential backup job below to:
> a) name the filename of the diff backup file to be
> <database_name>_diff_MMDDYYhhmmss.bak
> b) have the script remove diff backup files older than 1 day.
>|||Azz
Running a differential backup does not truncate the
transaction log, it just records all the changes to the
database sine the last full backup.
Bear in mind you should not just do differential backups,
you should do a full backup as well as part of you backup
strategy. How often depends on the size of your database
and how dynamic the data is.
If you do not do full backups eventually your differential
will be as large and take as long as a full backup, and
you will still need a full backup to use it.
Regards
John|||yeah we are also doing fulls once a day, diffs 2x, then transaction logs
every hour. I just wanted to make sure that running the differential did not
truncate the transaction logs.
can anyone help with the second 1/2 of my question?
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> Azz
> Running a differential backup does not truncate the
> transaction log, it just records all the changes to the
> database sine the last full backup.
> Bear in mind you should not just do differential backups,
> you should do a full backup as well as part of you backup
> strategy. How often depends on the size of your database
> and how dynamic the data is.
> If you do not do full backups eventually your differential
> will be as large and take as long as a full backup, and
> you will still need a full backup to use it.
> Regards
> John|||Here is a sample to start from:
-- Separate file for each day of the week --
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:\MVP\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
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 D:\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 "D:\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 Server MVP
"aaz" <aaz@.webcapacity.com> wrote in message
news:Owh8jYHTDHA.2460@.TK2MSFTNGP10.phx.gbl...
> yeah we are also doing fulls once a day, diffs 2x, then transaction logs
> every hour. I just wanted to make sure that running the differential did
not
> truncate the transaction logs.
> can anyone help with the second 1/2 of my question?
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> > Azz
> >
> > Running a differential backup does not truncate the
> > transaction log, it just records all the changes to the
> > database sine the last full backup.
> >
> > Bear in mind you should not just do differential backups,
> > you should do a full backup as well as part of you backup
> > strategy. How often depends on the size of your database
> > and how dynamic the data is.
> >
> > If you do not do full backups eventually your differential
> > will be as large and take as long as a full backup, and
> > you will still need a full backup to use it.
> >
> > Regards
> >
> > John
>

Differential backup vs Log Shipping

Hello,
I'm trying to figure out the most effective way to have a standby database
in remote location (connected by reliable broadband link).
The option which I'm looking at are log shipping and differential backups.
As far as I understand with log shipping all I have to do is to periodically
apply transacton logs to original full restore database and be good forever.
On the other hand if I use differential restores, I need to restore full
backup to standby server each time I do full backup at origin server, right?
Original database is 10 Gb so does it mean with differential backup scenario
I would have to copy 10 Gb each time I do full backup on source server?
Are there any advantages in using differential backup as opposed to log
shipping method?
On a separate note what is effect of full and transaction log backups on log
shipping process?
"ChanKaiShi" wrote:

> Hello,
> I'm trying to figure out the most effective way to have a standby database
> in remote location (connected by reliable broadband link).
> The option which I'm looking at are log shipping and differential backups.
> As far as I understand with log shipping all I have to do is to periodically
> apply transacton logs to original full restore database and be good forever.
> On the other hand if I use differential restores, I need to restore full
> backup to standby server each time I do full backup at origin server, right?
> Original database is 10 Gb so does it mean with differential backup scenario
> I would have to copy 10 Gb each time I do full backup on source server?
> Are there any advantages in using differential backup as opposed to log
> shipping method?

Differential backup vs Log Shipping

Hello,
I'm trying to figure out the most effective way to have a standby database
in remote location (connected by reliable broadband link).
The option which I'm looking at are log shipping and differential backups.
As far as I understand with log shipping all I have to do is to periodically
apply transacton logs to original full restore database and be good forever.
On the other hand if I use differential restores, I need to restore full
backup to standby server each time I do full backup at origin server, right?
Original database is 10 Gb so does it mean with differential backup scenario
I would have to copy 10 Gb each time I do full backup on source server?
Are there any advantages in using differential backup as opposed to log
shipping method?On a separate note what is effect of full and transaction log backups on log
shipping process?
"ChanKaiShi" wrote:

> Hello,
> I'm trying to figure out the most effective way to have a standby database
> in remote location (connected by reliable broadband link).
> The option which I'm looking at are log shipping and differential backups.
> As far as I understand with log shipping all I have to do is to periodical
ly
> apply transacton logs to original full restore database and be good foreve
r.
> On the other hand if I use differential restores, I need to restore full
> backup to standby server each time I do full backup at origin server, righ
t?
> Original database is 10 Gb so does it mean with differential backup scenar
io
> I would have to copy 10 Gb each time I do full backup on source server?
> Are there any advantages in using differential backup as opposed to log
> shipping method?

Differential Backup Size and Transaction Log Backup Size !

Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn

DIFFERENTIAL Backup Problem

I have a full recovery model backup scenario in place where I do full db
backups every night, incremental transaction log backups every 15 minutes and
differential backups of the database every hour. O/S is Windows 2000 Advanced
Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
This has been working flawlessly for over a year; now for some reason one of
my differential backup jobs is backing up the entire database every hour
instead of what has changed since the last full.
This happened for the first time about a week ago and after checking the
database for errors etc and finding nothing; all I could narrow it down to
was that for some
reason the job did not like the applicable record in msdb..backupset; so the
interim fix was to do a manual full backup forcing a new record to be created
and it started working fine again for a few days...
It started doing it again on the same database yesterday; same same; no
errors found; the differential just started doing full backups instead of
differentials...
Here is my script for my incremental backup;
declare @.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
cast(datepart(mi,getdate())as varchar) + '.bak'
BACKUP LOG [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'
and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY
FROM DISK = @.strFileNamePath
WITH FILE = @.i
Here is my script for my differential backup:
declare
@.strFileNamePath varchar(100)
set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
'.bak'
BACKUP DATABASE [TheDb]
TO DISK = @.strFileNamePath
WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
NAME = N'TheDb backup',
NOSKIP ,
STATS = 10,
NOFORMAT
DECLARE @.i INT
select @.i = position
from msdb..backupset
where database_name='TheDb'and type!='F'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name='TheDb')
RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
WITH FILE = @.i
The full (a db maintenance plan job):
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
-BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
As I said all these have been working flawlessly for over a year...
Thanks in advance.
I'm not sure what you are saying. Does it really produce a full backup (judging by file size)? If
you look at this backup using RESTORE HEADERONLY, is it a db backup or a diff backup? I don't see
how backup history msdb could affect the backup command you execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:5F24EC8D-A6B0-496B-B1F1-7D8B3687F059@.microsoft.com...
>I have a full recovery model backup scenario in place where I do full db
> backups every night, incremental transaction log backups every 15 minutes and
> differential backups of the database every hour. O/S is Windows 2000 Advanced
> Server, SQL 2000 sp3a, latest patches, hot fixes etc on everything…
> This has been working flawlessly for over a year; now for some reason one of
> my differential backup jobs is backing up the entire database every hour
> instead of what has changed since the last full.
> This happened for the first time about a week ago and after checking the
> database for errors etc and finding nothing; all I could narrow it down to
> was that for some
> reason the job did not like the applicable record in msdb..backupset; so the
> interim fix was to do a manual full backup forcing a new record to be created
> and it started working fine again for a few days...
> It started doing it again on the same database yesterday; same same; no
> errors found; the differential just started doing full backups instead of
> differentials...
> Here is my script for my incremental backup;
> declare @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbTransLog' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> cast(datepart(mi,getdate())as varchar) + '.bak'
> BACKUP LOG [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'
> and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY
> FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> Here is my script for my differential backup:
> declare
> @.strFileNamePath varchar(100)
> set @.strFileNamePath = '\\BackupServer\SQL Backups\TheDbDaily' +
> convert(varchar, getdate(),112) + cast(datepart(hh,getdate())as varchar) +
> '.bak'
> BACKUP DATABASE [TheDb]
> TO DISK = @.strFileNamePath
> WITH NOINIT, NOUNLOAD, DIFFERENTIAL, RETAINDAYS = 5,
> NAME = N'TheDb backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
> DECLARE @.i INT
> select @.i = position
> from msdb..backupset
> where database_name='TheDb'and type!='F'
> and backup_set_id=(select max(backup_set_id)
> from msdb..backupset
> where database_name='TheDb')
> RESTORE VERIFYONLY FROM DISK = @.strFileNamePath
> WITH FILE = @.i
> The full (a db maintenance plan job):
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 65514236-64B7-493A-841A-88F695626EB1 -Rpt "\\BackupServer\SQL Backups\TheDb
> DB Maintenance Plan14.txt" -DelTxtRpt 5DAYS -WriteHistory -VrfyBackup
> -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB "\\BackupServer\SQL
> Backups\TheDb" -DelBkUps 5DAYS -BkExt "BAK"'
> As I said all these have been working flawlessly for over a year...
> Thanks in advance.
>
|||I don't understand it either...
running RESTORED HEADERONLY tells me that it is in fact a differential
backup with a backuptype of 5 in the result set...
I am ascertaining that it is the entire database based on the physical size
of the backup, its just too big to be the differential...
Am I missing somthing obvious?
The scripts in my previous post have been working as is for over a year, now
this headache...
Any suggestions would be most welcome.
Thanks in advance.
|||and the differential backup size is almost exactly the same size as the full
backups
|||Perhaps you did some mass-modification of data? Like rebuilding indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:15B2AB34-D6B7-4E87-9828-A7ACABD731F7@.microsoft.com...
>I don't understand it either...
> running RESTORED HEADERONLY tells me that it is in fact a differential
> backup with a backuptype of 5 in the result set...
> I am ascertaining that it is the entire database based on the physical size
> of the backup, its just too big to be the differential...
> Am I missing somthing obvious?
> The scripts in my previous post have been working as is for over a year, now
> this headache...
> Any suggestions would be most welcome.
> Thanks in advance.
|||Indexes are rebuilt as needed by another job, that also has not changed in
over a year...
How would some mass-modification of data like rebuilding an index or other?
cause something like this?
I'd really like to figure out the cause and solution...
Thanks in advance.
RE:
"Tibor Karaszi" wrote:
Perhaps you did some mass-modification of data? Like rebuilding indexes...
|||A diff backup contains all pages that has been modified since the last database backup. If you
rebuild an index, then all pages that the index uses are modified (both pages for the old index and
for the new index).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"LvBohemian" <LvBohemian@.discussions.microsoft.com> wrote in message
news:7E5552A0-77C5-40B0-9262-7B01E8646B8D@.microsoft.com...
> Indexes are rebuilt as needed by another job, that also has not changed in
> over a year...
> How would some mass-modification of data like rebuilding an index or other?
> cause something like this?
> I'd really like to figure out the cause and solution...
> Thanks in advance.
> RE:
> "Tibor Karaszi" wrote:
> Perhaps you did some mass-modification of data? Like rebuilding indexes...
>

Differential Backup file fails to restore.

We are doing the following steps:-

1. Weekly Full Bckup

2.Daily Differential backup

3.hr Log back up

The following command works fine for all the Backups. it means backup file is fine.

RESTORE FILELISTONLY from DISK = 'D:\Backup\new\DB_ full.BAK'

RESTORE HEADERONLY FROM DISK = 'D:\Backup\new\DB_ ull.BAK'

RESTORE LABELONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'

RESTORE VERIFYONLY FROM DISK = 'D:\Backup\new\Test_full.BAK'

Also The full back up restoration works fine:

RESTORE DATABASE Test3 FROM DISK = 'D:\Backup\new\Test_full.BAK'

WITH MOVE 'Test2_Data' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Data.MDF',

MOVE 'Test2_Log' TO 'F:\MSSQL2K\MSSQL\data\Test2Net_Log.LDF',

NORECOVERY

GO

/* RESULT :

Processed 1032 pages for database 'Test3', file 'test2_Data' on file 1.

Processed 1 pages for database 'Test3', file 'test2_Log' on file 1.

RESTORE DATABASE successfully processed 1033 pages in 1.907 seconds (4.433 MB/sec).

*/

But While restoring the Differential file it throws error:

RESTORE DATABASE Test3 from DISK = 'D:\Backup\new\test2_Diff1.bak'

WITH NORECOVERY

GO

Msg 3136, Level 16, State 0, Line 1

Cannot apply the backup on device 'D:\Backup\new\test2_Diff1.bak' to database 'Test3'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

I checked the SQL Server Log no error msg corresponding Differential backup restore.

I don't know how to proceed further.Can any one guide me how to over come this..

Thanks !

is the differential backup appended or overwritten ? ? ?........if appended choose the exact differential backup and try to restore....also try using GUI and see........|||Differential backup is a separate file...

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
--Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_TranChar(4),--FULL or TRAN
@.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
@.db_nameVarchar(150),--Name of Database
@.StripesInt--Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPathVarchar(8000),
@.ServerNameVarchar(200),--Name of server
@.StripeNumint,
@.RuntimeDateTime,
@.CompressFileVarchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_TranChar(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Under the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> --Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_TranChar(4),--FULL or TRAN
> @.ServerAliasVarChar(5),--CLI or FIN or OM or RPT
> @.db_nameVarchar(150),--Name of Database
> @.StripesInt--Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPathVarchar(8000),
> @.ServerNameVarchar(200),--Name of server
> @.StripeNumint,
> @.RuntimeDateTime,
> @.CompressFileVarchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_TranChar(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = ''''' + @.FILE + ''''''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = ''''' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + ''''''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
''
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:

> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive' + @.ServerName +'' + @.db_name +
> ''
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114)
,':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114
),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd
+
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup did not work

Hi all,
I've been using this stored proc to do full and log backups for a while
and it's working perfectly. Now, the requirements have changed I've to
run full backup at 7:00PM and differential at 5:00AM. The full and log
backups are still working not differential. Something is wroing in my
codes on differentail backup task.
Thanks so much,
Silaphet,
Here is the error report from the job history:
Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
Below is my stored proc code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- drop proc spOM_BackUpDB
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
-- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
ALTER Proc spOM_BackUpDB_Test
@.Full_Tran Char(4), --FULL or TRAN
@.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
@.db_name Varchar(150), --Name of Database
@.Stripes Int --Number of stripes to dump it to
AS
Declare
@.cmd varchar(8000),
@.rcmd varchar (8000),
@.File Varchar(8000),
@.UNCPath Varchar(8000),
@.ServerName Varchar(200), --Name of server
@.StripeNum int,
@.Runtime DateTime,
@.CompressFile Varchar(8000)
Set @.File = ''
Set @.ServerName = @.@.servername
Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
'\'
Set @.StripeNum = 1
Set @.Runtime = GetDate()
SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak'
Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
Set @.StripeNum = @.StripeNum + 1
If @.Full_Tran = 'FULL'
Begin
set @.cmd = ' BACKUP DATABASE ' + @.db_name
End
DECLARE @.Full_Tran Char(4)
IF @.Full_Tran = 'DIFF'
BEGIN
set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
'WITH DIFFERENTIAL '
END
If @.Full_Tran = 'TRAN'
Begin
set @.cmd = ' BACKUP LOG ' + @.db_name
End
set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
While @.StripeNum <= @.Stripes
Begin
set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
@.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
Set @.StripeNum = @.StripeNum + 1
End
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
/*
Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
@.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
@.CompressFile + '"'
Select @.cmd
set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
''''
Select @.Rcmd
EXECUTE (@.Rcmd)
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOUnder the section "IF @.Full_Tran = 'DIFF'", it should be:
BEGIN
SET @.cmd = ' BACKUP DATABASE ' + @.db_name + ' WITH DIFFERENTIAL '
END
Remove the extra + sign and add a space before the word "WITH"
"Silaphet" wrote:
> Hi all,
> I've been using this stored proc to do full and log backups for a while
> and it's working perfectly. Now, the requirements have changed I've to
> run full backup at 7:00PM and differential at 5:00AM. The full and log
> backups are still working not differential. Something is wroing in my
> codes on differentail backup task.
> Thanks so much,
> Silaphet,
> Here is the error report from the job history:
> Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near the
> keyword 'TO'. [SQLSTATE 42000] (Error 156). The step failed.
>
> Below is my stored proc code:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> -- drop proc spOM_BackUpDB
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'FULL', 'CLI', 'ObjectManager', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ZION_PROD', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'socConfig', 4
> -- Exec spOM_BackUpDB 'TRAN', 'CLI', 'ObjectManager', 4
> ALTER Proc spOM_BackUpDB_Test
> @.Full_Tran Char(4), --FULL or TRAN
> @.ServerAlias VarChar(5), --CLI or FIN or OM or RPT
> @.db_name Varchar(150), --Name of Database
> @.Stripes Int --Number of stripes to dump it to
> AS
>
> Declare
> @.cmd varchar(8000),
> @.rcmd varchar (8000),
> @.File Varchar(8000),
> @.UNCPath Varchar(8000),
> @.ServerName Varchar(200), --Name of server
> @.StripeNum int,
> @.Runtime DateTime,
> @.CompressFile Varchar(8000)
>
> Set @.File = ''
> Set @.ServerName = @.@.servername
> Set @.UNCPath = '\\Houdbs0101\K_Drive\' + @.ServerName +'\' + @.db_name +
> '\'
> Set @.StripeNum = 1
> Set @.Runtime = GetDate()
> SELECT @.FILE = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak'
> Select @.CompressFile = @.uncpath + @.db_name + '_' + @.Full_Tran + '_' +
> convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '*.*'
> Set @.StripeNum = @.StripeNum + 1
> If @.Full_Tran = 'FULL'
> Begin
> set @.cmd = ' BACKUP DATABASE ' + @.db_name
> End
> DECLARE @.Full_Tran Char(4)
> IF @.Full_Tran = 'DIFF'
> BEGIN
> set @.cmd = ' BACKUP DATABASE ' + @.db_name + +
> 'WITH DIFFERENTIAL '
> END
> If @.Full_Tran = 'TRAN'
> Begin
> set @.cmd = ' BACKUP LOG ' + @.db_name
> End
> set @.cmd = @.cmd + ' TO DISK = '' + @.FILE + '''
>
> While @.StripeNum <= @.Stripes
> Begin
> set @.cmd = @.cmd + ', DISK = '' + @.uncpath + @.db_name + '_' +
> @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(5),@.Runtime,114),':','') + '_' +
> Convert(Varchar(3),@.StripeNum) + '.Bak' + '''
> Set @.StripeNum = @.StripeNum + 1
> End
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.sp_sqlexec ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> /*
> Set @.cmd = '"\\Houdbs0101\L_Drive\WinRAR\RAR.exe" a -ep "' + @.uncpath +
> @.db_name + '_' + @.Full_Tran + '_' + convert(varchar(24),@.Runtime,112) +
> replace(convert(varchar(24),@.Runtime,114),':','') + '.rar' + '" "' +
> @.CompressFile + '"'
> Select @.cmd
> set @.Rcmd = '[' + @.ServerAlias + '].master.dbo.xp_cmdshell ''' + @.cmd +
> ''''
> Select @.Rcmd
> EXECUTE (@.Rcmd)
> */
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>|||>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>
Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.
>AK
>
>.
>|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK
>--Original Message--
>I wouldn't figure the differential should ever be larger
than the full DB
>backup. Is the differential backup writing to the same
file each time? If so
>then you're probably adding to the file each time, look
up the WITH FORMAT
>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
>> I have the following scheduled for backup:
>> a) Full back up (daily) which creates a *.bak of 1.5GB
in
>> size.
>> b) Transaction log (every 2 hours) which creates a *.TRN
>> of 20 MB
>> c) Differential back up which creates a *.bak of 9GB and
>> continues to grow.
>>
>> Couple of questions:
>> 1) Is the size normal for Differential? What am I doing
>> wrong? Why is it so big?
>> 2) Is there only one file that gets created for
>> differential?
>>
>> AK
>>
>
>.
>|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK
>--Original Message--
>>--Original Message--
>>I have the following scheduled for backup:
>>a) Full back up (daily) which creates a *.bak of 1.5GB
in
>>size.
>>b) Transaction log (every 2 hours) which creates a *.TRN
>>of 20 MB
>>c) Differential back up which creates a *.bak of 9GB and
>>continues to grow.
>>
>>Couple of questions:
>>1) Is the size normal for Differential? What am I doing
>>wrong? Why is it so big?
>>2) Is there only one file that gets created for
>>differential?
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>>AK
>>
>>.
>.
>|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
> >--Original Message--
> >I wouldn't figure the differential should ever be larger
> than the full DB
> >backup. Is the differential backup writing to the same
> file each time? If so
> >then you're probably adding to the file each time, look
> up the WITH FORMAT
> >option of the BACKUP command.
> >
> >Mike Kruchten
> >
> >
> >"AK" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
> >> I have the following scheduled for backup:
> >>
> >> a) Full back up (daily) which creates a *.bak of 1.5GB
> in
> >> size.
> >>
> >> b) Transaction log (every 2 hours) which creates a *.TRN
> >> of 20 MB
> >>
> >> c) Differential back up which creates a *.bak of 9GB and
> >> continues to grow.
> >>
> >>
> >> Couple of questions:
> >>
> >> 1) Is the size normal for Differential? What am I doing
> >> wrong? Why is it so big?
> >>
> >> 2) Is there only one file that gets created for
> >> differential?
> >>
> >>
> >> AK
> >>
> >>
> >
> >
> >.
> >

Differential Backup

I have the following scheduled for backup:
a) Full back up (daily) which creates a *.bak of 1.5GB in
size.
b) Transaction log (every 2 hours) which creates a *.TRN
of 20 MB
c) Differential back up which creates a *.bak of 9GB and
continues to grow.
Couple of questions:
1) Is the size normal for Differential? What am I doing
wrong? Why is it so big?
2) Is there only one file that gets created for
differential?
AKI wouldn't figure the differential should ever be larger than the full DB
backup. Is the differential backup writing to the same file each time? If so
then you're probably adding to the file each time, look up the WITH FORMAT
option of the BACKUP command.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...

quote:

---

> I have the following scheduled for backup:
> a) Full back up (daily) which creates a *.bak of 1.5GB in
> size.
> b) Transaction log (every 2 hours) which creates a *.TRN
> of 20 MB
> c) Differential back up which creates a *.bak of 9GB and
> continues to grow.
>
> Couple of questions:
> 1) Is the size normal for Differential? What am I doing
> wrong? Why is it so big?
> 2) Is there only one file that gets created for
> differential?
>
> AK
>

---

|||

quote:

---

>--Original Message--
>I have the following scheduled for backup:
>a) Full back up (daily) which creates a *.bak of 1.5GB in
>size.
>b) Transaction log (every 2 hours) which creates a *.TRN
>of 20 MB
>c) Differential back up which creates a *.bak of 9GB and
>continues to grow.
>
>Couple of questions:
>1) Is the size normal for Differential? What am I doing
>wrong? Why is it so big?
>2) Is there only one file that gets created for
>differential?
>

---

Perhaps you are Apending the Differential file @. time you
run it. Try over writing the Differential @. time you run
it.

quote:

---

>AK
>
>.
>

---

|||Mike:
Thank you for the reply!
It is backing up to same file every 4 hours.
I am not sure where you want me to check this statement? I
would appreciate some assistance on which tool to use?
The TRASNCT-SQL statement under the job is as follows
BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
\BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
Differential', NOSKIP , STATS = 10, NOFORMAT
Thank you very much!
AK

quote:

---

>--Original Message--
>I wouldn't figure the differential should ever be larger

---

than the full DB

quote:

---

>backup. Is the differential backup writing to the same

---

file each time? If so

quote:

---

>then you're probably adding to the file each time, look

---

up the WITH FORMAT

quote:

---

>option of the BACKUP command.
>Mike Kruchten
>
>"AK" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:01bc01c3dc6d$25e70930$a401280a@.phx.gbl...
in[QUOTE]
>
>.
>

---

|||How would I NOT append and rather overwrite?
Where would I set up this option?
I am running SQL 7.0
Thank you!
AK

quote:

---

>--Original Message--
>
in[QUOTE]
>Perhaps you are Apending the Differential file @. time you
>run it. Try over writing the Differential @. time you run
>it.
>.
>

---

|||You need to change the NOINIT/NOFORMAT statements, whats happening is the
backup is appended to the file each time. If you want to overwrite the
previous differential backup with the new one each time, drop the NOINIT and
NOFORMAT options and add FORMAT instead.
Mike Kruchten
"AK" <anonymous@.discussions.microsoft.com> wrote in message
news:09c201c3debc$01e8ae70$a401280a@.phx.gbl...[QUOTE]
> Mike:
> Thank you for the reply!
> It is backing up to same file every 4 hours.
> I am not sure where you want me to check this statement? I
> would appreciate some assistance on which tool to use?
> The TRASNCT-SQL statement under the job is as follows
> BACKUP DATABASE [Labor32SQL] TO DISK = N'D:\MSSQL7
> \BACKUP\Labor32SQL\Labor32SQL Differential.BAK' WITH
> NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N'Labor32SQL
> Differential', NOSKIP , STATS = 10, NOFORMAT
>
> Thank you very much!
> AK
>
> than the full DB
> file each time? If so
> up the WITH FORMAT
> message
> in

Differential and Transaction Log BU Maintenance Plan SP2

I had a maintenance plan which does a full backup weekly, differential backup daily, and transaction log every hour. After installing SP2, the maintenance plan designer will not allow me to set up my differential. I select the database to backup in the designer, then when I go back, it didn't take and it tells me I need to specify which database. I found the following in the "Whats New" section of SP2.

The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.

So, have I been wrong all this time for doing Full, Differential, and Transaction log backups(even though that plan was recommended in a Sql Server 2005 book). Is it now an either or? (you either do differential or transaction log, not both). Because transaction logs take longer to restore, and you "HAVE" to do a transaction log backup or the log will grow to rediculous size, meaning you can only do differential on "Simple Mode" databases? Thanks,

Jason

Transaction log backup should control the virtual size of transaction log and that should give you more time for point-in-time recovery than the differential backups, in this case the SP2 readme is right on the subject.

Also you might try to keep the transaction log backup schedule frequently to take care of such slowness of restoring the tlog.

|||Alright, I'll take your word for it. No more Full, Differential, and Transaction Log backups. Just Full and Transaction Log. Thanks.