February 13, 2012 at 7:05 am
Those are HA/DR, not backups. They're great for HA/DR, but pretty useless when you need to get last night's data.
I have another question now. Does it mean HA/DR will not give me last night’s data if I want?
February 13, 2012 at 7:06 am
GilaMonster (2/13/2012)
Dev (2/13/2012)
Just for clarity, what do you mean by "duplicating database is beter than taking backups"?Hot / Warm standby servers.
Those are HA/DR, not backups. They're great for HA/DR, but pretty useless when you need to get last night's data.[/quote]
Agreed. Excellent mechanism for keeping immediately available copies. As you say, much faster than backups. But as Gail points out, useless for any kind of historical retrieval.
Even if you have a good HA system, I would still perform backup operations. They're a fundamental part of disaster recovery, even if they're not the most efficient mechanism on the planet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2012 at 7:23 am
Dev (2/13/2012)
Those are HA/DR, not backups. They're great for HA/DR, but pretty useless when you need to get last night's data.
I have another question now. Does it mean HA/DR will not give me last night’s data if I want?
HA/DR = high availability/disaster recovery. Clustering, database mirroring, log shipping, SAN replication, RAID arrays, database replication, etc
If you have a hot standby server (and hot standby server usually means exact of the production DB), how would you get the data as it was last night from it?
The high availability solutions are there to give you redundancy at the hardware levels and in some cases a second (or third, etc) exact copy of the database for if the primary site fails. Emphasis on exact copy.
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
February 13, 2012 at 7:30 am
Even the new AlwaysOn capabilities with SQL Server 2012 that include asynchronous updates are still just mirrors of the main database, not historical beyond the delay of the asynchronous updates. So no, you can't look at previous day's or even previous hour's data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2012 at 7:41 am
That’s why I said hot (exact) / warm (with some lag) standby server. Server that is lagging (intentionally) behind can help me in getting last night’s data.
I am certainly not against backups. I also understand without backups we can’t design a good HA / DR plan. I was just curious to know what could be the best solution if VLDB database needs to be recovered till last night and should we really need to manage 3 TB of backups for 1 TB of data. Honestly, it sounds very much odd to me.
Sorry for dragging the thread especially when OP’s question is already answered.
February 13, 2012 at 7:53 am
Dev (2/13/2012)
That’s why I said hot (exact) / warm (with some lag) standby server. Server that is lagging (intentionally) behind can help me in getting last night’s data.
And the user that wants the rows she accidentally deleted 10 minutes ago? The junior DBA who dropped a semi-important table yesterday and didn't realise until today that it was on production not development? The auditor who wants to see a particular table as it was last week Friday at 2pm, 3pm and 4pm (for some fraud investigation)
The only HA/DR technique you can use for lagging data intentionally is log shipping, and lagging the restores means when you need it for DR, you've got more work to do when you need the server for DR, and when the server is down is not the time you want to be frantically restoring log backups to get the secondary up to date
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
February 14, 2012 at 6:02 am
Hi Dev, Gail is correct,also backup size depends on usage size mdf/ldf.can you tell me the usage size for 5 GB DB ?
February 14, 2012 at 6:06 am
Correct
February 14, 2012 at 10:14 am
If this is what you mean, then try this.....
BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT
It's from this post:
To restore, try this....
RESTORE DATABASE MyNwind
FILE = 'MyNwind_data_1',
FILE = 'MyNwind_data_2',
FILEGROUP = 'new_customers'
FROM MyNwind_1
WITH NORECOVERY
-- Restore the log backup.
RESTORE LOG MyNwind
FROM MyNwindLog1
It's from the SQL Server Books Online:
http://msdn.microsoft.com/en-us/library/aa238405(v=sql.80).aspx
I have tried this and there are some things to be aware of. First, if any of the files get "lost", the backup is invalid. Also, there is a Windows parameter that if set, will speed up the backups incredibly. I can't remember the parameter but you can look at MidnightDBA's web-site.
February 14, 2012 at 10:15 am
If this is what you mean, then try this.....
BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT
It's from this post:
To restore, try this....
RESTORE DATABASE MyNwind
FILE = 'MyNwind_data_1',
FILE = 'MyNwind_data_2',
FILEGROUP = 'new_customers'
FROM MyNwind_1
WITH NORECOVERY
-- Restore the log backup.
RESTORE LOG MyNwind
FROM MyNwindLog1
It's from the SQL Server Books Online:
http://msdn.microsoft.com/en-us/library/aa238405(v=sql.80).aspx
I have tried this and there are some things to be aware of. First, if any of the files get "lost", the backup is invalid. Also, there is a Windows parameter that if set, will speed up the backups incredibly. I can't remember the parameter but you can look at MidnightDBA's web-site.
February 15, 2012 at 5:43 pm
How it will take the size of single file ?suppose i have 400 GB DB..if i spilt the backup to 4 drives,it will take each drive 100 GB ? or we need to mention the size ?.please clarify.
February 16, 2012 at 1:49 am
The backup will be striped equally across all files.
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
February 16, 2012 at 7:09 am
Thankq Soo much for clarification ...your hair style is Good:)
February 16, 2012 at 7:13 am
If you're referring to the avatar, that's a picture from one of the StarWars movies.
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
February 16, 2012 at 7:21 am
I have one issue please ..pls help on this..
Issue:- After upgrade to MSSQL 2008 SP3 from 2008 SP2..MSSQL service agent is not coming up automatically after server reboot(now we are starting manually). please help me to solve this issue.
Thanks in advance!!
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply