Resources for IT Professionals >
Página Inicial dos Fóruns
>
SQL Server Disaster Recovery and Availability
>
SQL Server 2008 Backup?
SQL Server 2008 Backup?
- Hello:
I have database which is size of 12GB & this database is enabled for FILESTREAM. filestream size is is 500GB+ & when i do full backup it takes more than 24hrs+.
For database restore testing, when i restore the database it takes more than 23 days which is not acceptable.
Any help to expedite backups & restore will be highly appreciated. SQL partition is not in our solution & don't want to use as that will lead us to re-design of Database & we don't want to redesign at this moment.
Thanks
Todas as Respostas
- Have you tried excluding the FILESTREAM data from your backup? How long does that take?
Still, 500GB shouldn't take that long to backup and restore with decent hardware. What's the underlying I/O subsystem for the FILESTREAM data?
Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database. - Are you doing the backup locally or over a network? Just curious.
I agree with Paul that today, 500GB shouldn't take you that long. It may take a few hours, but not more than a day.
Allan Hirt Blog: http://www.sqlha.com/blog Author: Pro SQL Server 2008 Failover Clustering (out now!) - Hello:
Thanks for the reply.
My database is on SAN (RAID-10). FILESTREAM is on separate volume on SAN (RAID-10) & this volume is strictly dedicated for FILESTREAM files. I tried compressed backups & backups without compression, no difference.
backup is done locally with compression.
To test backups from restoring testing point. as i mentioned, it takes more than 23 days just to restore which is not acceptable.
let me know if i can provide any further information.
Thanks- Editadoaaditya2000 terça-feira, 18 de agosto de 2009 19:25
- How are you calculating 23 days? Laying down 500GB on a RAID-10 array is a matter of hours, not days. I suspect you may have millions of FILESTREAM files and you haven't disabled 8.3 name generation for new files (see my FILESTREAM whitepaper at http://msdn.microsoft.com/en-us/library/cc949109.aspx)
How long does the backup/restore take if you exclude the FILESTREAM data? How many FILESTREAM files are there?
Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database. - Hello:
Thanks for the reply.
This FILESTREAM folder has close to 8 millions files which combines to size of 500GB+.
I have gone thru your white paper on FILESTREAM along with BOL before configuring FILESTREAM on SQL Server 2008 Database.
I have disable both the feature as per whiete paper.
fsutil behavior set disable8dot3 1
fsutil behavior set disablelastaccess 1
Excluding filestream files backups, it takes less than 4 min to backup database of size 12GB.
Thanks I am having the same problem. Did you ever get the restore time to an acceptable level
thanks- Hello:
Basically i started doing filegroup backups & now it is taking 58 minutes. Restore took 5 hrs & 19 mins.
Also make sure that NTFS Cluster size is 4KB (Which is by default).
Above metric is in reference to FILESTREAM feature that is used in one of my table.
I hope if this helps.
Thanks Hi,
We also are facing the same problem and we tried filegroup backups and restore as per your suggestion (Piecemeal restore). But we are facing some problems with it.
We are using Simple recovery model and some of the filegroups are not coming online, their status is Defunct. All the filegroups are with Read/Write access.
Did you face any problems related to filegroups with read/write access and recovery model, if yeas then how did you solve it?
Thanks
Prashant Ghadge.
