February 12, 2012 at 2:19 am
Hello All,
can you please help me to solve this issue
I have a 400gb of data to take backup
the back up of data should be split as below in different drives
100 gb in x drive
100 gb in y drive
100 gb in z drive
100 gb in d drive
Thanks In Advance
February 12, 2012 at 2:29 am
Do you have a database of 400 GB? In general, its FULL backup won’t cross 100 GB size.
Can you please explain how your database files are distributed on I/O subsystem For Example Primary file group on C: drive with XX size and Log on D: drive with YY size?
February 12, 2012 at 3:29 am
Dev (2/12/2012)
Do you have a database of 400 GB? In general, its FULL backup won’t cross 100 GB size.
A database of 400 GB will generally have a backup size under 100GB? Really?
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 12, 2012 at 3:33 am
Why are you so surprised?
February 12, 2012 at 3:43 am
Dev (2/12/2012)
Why are you so surprised?
Tell me exactly how a 400 GB database will generally have a full backup of under 100GB (and note that backup compression wasn't in SQL 2005)?
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 12, 2012 at 3:43 am
Hello All,
400gb i had taken it as example
i want to know how to split the database backup into multiple disks and how to restore them
February 12, 2012 at 3:55 am
GilaMonster (2/12/2012)
Dev (2/12/2012)
Why are you so surprised?Tell me exactly how a 400 GB database will generally have a full backup of under 100GB (and note that backup compression wasn't in SQL 2005)?
How? I guess I don't know and you know it better but I can produce few figures... Just took a backup of 5 GB database (test).
backup size with Compression: 13 MB
backup size without Compression: 850 MB
February 12, 2012 at 3:55 am
mssql.dba (2/12/2012)
i want to know how to split the database backup into multiple disks and how to restore them
If you specify multiple destinations for your backup, the data will be striped evenly across them all. Be careful, you need all of the files to restore, lose one and the backup is useless.
So if you want to stripe a backup over two files, it would be
BACKUP DATABASE <database name> TO DISK = <destination 1>, DISK = <destination 2>;
Restore is much the same way, specify multiple sources for the restore.
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 12, 2012 at 4:03 am
Dev (2/12/2012)
GilaMonster (2/12/2012)
Dev (2/12/2012)
Why are you so surprised?Tell me exactly how a 400 GB database will generally have a full backup of under 100GB (and note that backup compression wasn't in SQL 2005)?
How? I guess I don't know and you know it better but I can produce few figures... Just took a backup of 5 GB database (test).
backup size with Compression: 13 MB
backup size without Compression: 850 MB
Compression didn't exist in SQL 2005 hence irrelevant in this thread.
Those figures mean that your 5GB database is mostly empty space. It's mostly either log file or free extents within a data file. That's nice, but it's not something that's always or even generally true. A 400GB database that stores only 100 GB of data is a waste of space.
If I have a 400GB database with a 350GB data file and a 50GB log file and the data file is 80% full, is its backup going to be under 100 GB (without compression, SQL 2005 did not have compression)?
Let me give you another set of figures.
Testing database, data file 3 GB in size. Uncompressed full backup 2.55GB.
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 12, 2012 at 4:22 am
Does it mean 1 TB database will have ~1 TB backups (FULL, without compression)? Let me add 2 DIFF backups (100 GB each) and few LOG backups (up-to 300 GB in a week) as well. As a best practice we must have backup preserved equivalent to 2 FULL backups (at least). So it’s approximately 3TB backups.
If it's so, then duplicating database is better than taking backups.
February 12, 2012 at 4:32 am
Thanks you very much for the answer
February 12, 2012 at 7:14 am
Dev (2/12/2012)
Does it mean 1 TB database will have ~1 TB backups (FULL, without compression)? Let me add 2 DIFF backups (100 GB each) and few LOG backups (up-to 300 GB in a week) as well. As a best practice we must have backup preserved equivalent to 2 FULL backups (at least). So it’s approximately 3TB backups.If it's so, then duplicating database is better than taking backups.
But wait, you're making a HUGE assumption that a database size and the amount of data within it are either always the same or always wildly different. It completely depends on how people have configured their systems. I've seen it where a 100gb database consists of 20gb of data and I've seen it where a 100gb database consists of about 100gb of data. It's that configuration that matters. You can't just dismiss the concept that when someone says that they have a 400gb database that there might be as much as 400gb of data within it.
So, your 1TB database may have 1TB of data within, in which case, yes, your backup is 1TB in size, but if it has less data, then the backup is less. What's the issue here?
Just for clarity, what do you mean by "duplicating database is beter than taking backups"?
"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 12, 2012 at 2:09 pm
Dev (2/12/2012)
Does it mean 1 TB database will have ~1 TB backups (FULL, without compression)?
If there's 1 TB of data in there, yes. How else would it work? 1 TB of data's not smaller when backed up than normally.
If it's so, then duplicating database is better than taking backups.
Huh?
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 6:53 am
So, your 1TB database may have 1TB of data within, in which case, yes, your backup is 1TB in size, but if it has less data, then the backup is less. What's the issue here?
No issues at all. I was just trying to setup a formula for backup size estimation. It’s always good to know in advance that how much time & space a backup will take.
Just for clarity, what do you mean by "duplicating database is beter than taking backups"?
Hot / Warm standby servers.
February 13, 2012 at 6:56 am
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.
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
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply