August 10, 2009 at 2:19 pm
Hello,
I have a big database that needs to be backed up. The combination of the .mdf and the .ldf should be around 55 GB or so. The three broken up parts of the .bak file adds up to 50 GB.
I have mentioned the reason we have to do this is in this post:
http://www.sqlservercentral.com/Forums/Topic767562-1550-1.aspx
Since this is what I have to do, it presents a problem.
The problem
When I try to do an incremental backup, it is asking for a full backup. I don't have a full backup, because the full backup was backed up into three .bak files.
The Question
Is there a way using TSQL to associate the incremental to the three .bak files?
Do I need to assemble the three .bak files on server b and then associate the incremental from server a?
I've included our backup script again.
-- Backup
DECLARE @BakDate as datetime
DECLARE @Bak1 as nvarchar(250)
DECLARE @Bak2 as nvarchar(250)
DECLARE @Bak3 as nvarchar(250)
SET @BakDate=getdate()
SET @bak1 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_1_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak2 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_2_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak3 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_3_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
BACKUP DATABASE [KRONOS] to
DISK = @bak1,
DISK = @bak2,
DISK = @bak3
WITH INIT, NOUNLOAD, Name='KRONOS Backup', Noskip, noformat
go
Thanks. This is a time and attendeance system and I only have the backup from Friday night. If I have to do a restore, at this point, I would lose all day since Friday night.
Tony
Things will work out. Get back up, change some parameters and recode.
August 10, 2009 at 2:25 pm
WebTechie38 (8/10/2009)
Hello,I have a big database that needs to be backed up. The combination of the .mdf and the .ldf should be around 55 GB or so. The three broken up parts of the .bak file adds up to 50 GB.
I have mentioned the reason we have to do this is in this post:
http://www.sqlservercentral.com/Forums/Topic767562-1550-1.aspx
Since this is what I have to do, it presents a problem.
The problem
When I try to do an incremental backup, it is asking for a full backup. I don't have a full backup, because the full backup was backed up into three .bak files.
The Question
Is there a way using TSQL to associate the incremental to the three .bak files?
Do I need to assemble the three .bak files on server b and then associate the incremental from server a?
I've included our backup script again.
-- Backup
DECLARE @BakDate as datetime
DECLARE @Bak1 as nvarchar(250)
DECLARE @Bak2 as nvarchar(250)
DECLARE @Bak3 as nvarchar(250)
SET @BakDate=getdate()
SET @bak1 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_1_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak2 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_2_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
SET @bak3 = '\\MYSERVER (KRONOS Backups)\-- BAK files\KRONOS_3_'+LTRIM(STR(YEAR(@BakDate)))+RIGHT('0'+LTRIM(STR(MONTH(@BakDate))),2)+RIGHT('0'+LTRIM(STR(Day(@BakDate))),2)+'1230.BAK'
BACKUP DATABASE [KRONOS] to
DISK = @bak1,
DISK = @bak2,
DISK = @bak3
WITH INIT, NOUNLOAD, Name='KRONOS Backup', Noskip, noformat
go
Thanks. This is a time and attendeance system and I only have the backup from Friday night. If I have to do a restore, at this point, I would lose all day since Friday night.
Tony
First, SQL Server doesn't have an incremental backup. It has FULL, DIFFERENTIAL, and TRANSACTION LOG backups. If you are getting an error message that you don't have a FULL BACKUP, its saying just that. It isn't a result of your striping the backup to multiple files.
So, my first question, what are you attempting to accomplish; a DIFFERENTIAL backup or TRANSACTION LOG backup?
Also, could you post the full text of the error message you are receiving, that would help.
August 10, 2009 at 2:43 pm
Are you trying to restore a differential backup? If so, you need to restore the full WITH NORECOVERY and then restore the differential.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2009 at 2:48 pm
Wow,
Now that is embarrassing. I said incremental?! This problem is truly driving me crazy. Sorry about that. My manager said, "Find out how to associate the incremental to the full backup."
I had that stuck in my head. I meant differential.
I will get the error message and post it.
Thanks.
Tony.
Things will work out. Get back up, change some parameters and recode.
August 10, 2009 at 2:54 pm
There's no associating of differentials to fulls, except when doing a restore.
Do you have anything else backing up this database? Any system backup tool that has a sql component. I've seen this before when two different tools were backing up the SQL database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 7:41 am
Finally was able to get the error message and post it:
Executing the query "BACKUP LOG [Kronos] TO DISK = N'\\\\myserver" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Okay, here’s the background, and what the schedule was in SQL2000:
FULL backup via a maintenance plan at 12:30
REINDEX at 2:45am. A manager had written a reindexing job (a while back in SQL2000. In this job, he switched the mode from FULL to SIMPLE, REINDEXED, then back to FULL.
Incremental transaction log backups started up again at 8:00 that morning as usual.
Never had an incremental fail, no error messages, but then again, never had to do a point in time restore using a full from 12:30 and an incremental from say 8:00 (or after).
Now that we are on SQL2008 I started to get the above error (the schedule was exactly the same – NOTHING was changed – well, except that I recreated the exact same plans, jobs, and schedule times as this was not an in-place but a side-by-side upgrade).
Question#1:
Why did this work in SQL2000 (or did it?!?!?) and now not in SQL2008.
Question#2:
Is this new plan of action is solid? DIFFERENTIAL (midnight), REINDEX (12:30am), FULL (4:00am), INCREMENTALS (every hour starting at 8:00AM – 11:00PM)?
Tony
Things will work out. Get back up, change some parameters and recode.
August 13, 2009 at 7:54 am
I would suggest change the reindex to switch to bulk-logged instead of simple then back to full afterwards. The log will still grow, be prepared for that, but the log chain will stay intact and you won't need the full straight after the reindex, just backup the log.
In SQL 2000, log backups failed silently if the log chain was broken. I don't recall if backup files were created or not, but if they were, they'd be useless for restore.
p.s. Log backups aren't technically incremental backups. Better to call them log backups, otherwise it'll confuse people
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2009 at 8:57 am
Thank you for your advice!:-D
Things will work out. Get back up, change some parameters and recode.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply