October 15, 2001 at 7:33 am
Our production database( SQL Server 7.0 with SP3)is around 100 GB . Due to some difficulties we are not in a position to take a database back up regularily. However at the EOD, there is a scheduled OS level back up.
Can I restore the SQL Server database from the OS level back up. ( The OS level back up provides the original .mdf and .ldf files).
Please also note that the production database is built on multiple filegroups.
October 15, 2001 at 7:53 am
Depends on what you're using to backup. If its strictly a file backup that doesnt understand how SQL works, no it won't work. Im curious, why can't you backup?
Andy
October 18, 2001 at 12:53 pm
The files used for SQL are the .mdf/.ldf/.ndf files. These are open and locked while the mssql service is running.
For OS level backups: two choices:
If you get backup software to handle open files, you can use these (most likely). the consistancy of the files is suspect though because you will physically backup one file before another. You are taking chances here. This is a great reason why you should either backup using SQL tools.
2nd choice, if you can shut down the services, do that. Most backup tools allow a pre and post job. Use a "net stop" "net start" to stop and start the services.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 19, 2001 at 2:51 am
quote:
Depends on what you're using to backup. If its strictly a file backup that doesnt understand how SQL works, no it won't work. Im curious, why can't you backup?Andy
Andy,
Thanks for the response. There are couple of reasons for not being able to take up regular database backups.
1.Lack of down time in the narrow week-end window. We hardly have 32 hours during the week-end, we need to carry out Re-indexing tasks and other maintenance tasks along with the database backup.
2.Shortage of diskspace. As the database is size is ever growing, we always need free space, atleast 1.5 times the size of the production database
Could you please name some tools which allows us to take a consistent database backup while the database is up and running ?
Thanks,
Sharma
October 19, 2001 at 5:18 am
The backup native to SQL Server works just fine, no reason not to use it. You can backup to disk (my preference) or directly to tape if space is tight.
My friend, you simply cannot afford NOT to backup regularly. At the very least keep a running series of log backups. It might take a while to restore all of them, but at least you could.
SQL2K has dbcc indexdefrag, allows online realtime defrag. You may not want to run it during peak use, but you can run it live. That would free up time to run a backup. In SQL 7, consider doing a "smarter" reindex to save time. Which tables could be done less frequently?
How long does the backup take to run (for a full backup)? What kind of disk array are you using?
Andy
October 22, 2001 at 12:46 am
Andy/Steves,
Following are the details of the Database backup and reindexing activity.
Database Backup Details:
Database Size: 60GB
Duration : 7 Hours
Frequency : Once in a week
Reindexing Details: Because of the large sizes of the tables, we can afford to have only few tables reindexes a week. Following are the details of one of the largest tables of the database:
Table Name: XYZ
No.Of Indexes: 2 - One Cluster and one Non-Cluster ( No composite keys)
Rows# : 32918662
Size : 18729360 KB
Index Size: 51760 KB
%of DB size: 30%
It takes 12 hours to reindex this table.
Could you throw some tips to improvize on reindexing and backup ?
Regards,
Sharma
October 22, 2001 at 12:46 am
Andy/Steves,
Following are the details of the Database backup and reindexing activity.
Database Backup Details:
Database Size: 60GB
Duration : 7 Hours
Frequency : Once in a week
Reindexing Details: Because of the large sizes of the tables, we can afford to have only few tables reindexes a week. Following are the details of one of the largest tables of the database:
Table Name: XYZ
No.Of Indexes: 2 - One Cluster and one Non-Cluster ( No composite keys)
Rows# : 32918662
Size : 18729360 KB
Index Size: 51760 KB
%of DB size: 30%
It takes 12 hours to reindex this table.
Could you throw some tips to improvize on reindexing and backup ?
Regards,
Sharma
October 22, 2001 at 5:11 am
How many rows are added/updated each week? 7 hours seems awfully long for 60g, what kind of disk array are you using? While Im asking, how many/what speed CPU, how much memory?
Andy
October 22, 2001 at 10:41 am
Also, what is the IO structure and the Index structure. You many have too many indexes. If you rebuild the clustered index, then you will be moving all the data around, so the IO system will be stressed.
I would look at upgrading to 2000. The reindex tools and backup engine were substantially enhanced in 2000. I know there is a cost, but it might be less than spending $$ on hardware.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 5, 2001 at 3:21 pm
VERITAS Backup Exec sells an add-on product called "RAIDirector" which allows you to use multiple drives as a tape RAID array. You could try this to improve your througput.
I've never used this, but I've had no problems with the "standard" SQL Server backup using Backup Exec. I do a daily full backup of a 55GB database and I've been able to write the whole thing to tape, using Backup Exec with the "SQL Server Agent," in around 4-5 hours using a single Seagate DDS4 changer (this is with the tape machine directly attached to the database server). The database is on-line throughout the whole period, and at least an hour of this process is running a (physical only) DBCC on the database.
As an alternative, if you're able to upgrade to SQL Server 2000, you could set up an offline database server and use log-shipping to keep it up to date. You could then back that thing up whenever it's convenient.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply