SQL Server Performance Forum – Threads Archive
Backup Recovery Methods
I am currently using CA’s Brightstor Arcserve 9.0 with the SQL Server Agents on each SQL Server (2000). I backup the data on a private network to a server with large arrays attached to store the data (overwritten daily with new data). The arrays then get backed up by an attached tape library. This has been working great but starting having restore problems with the agents. I now want to take the agents out of the picture so now I am using SQL Server to dump the backup files and then those will be transferred to that same backup server via Arcserve. A developer suggested that I BCP the databases to that server. They say it is faster and easily restored. It sounds good but what are the problems with this versus the SQL Server Backups (besides disk space)? Any further recommendations other than other products? Thanks.Tom
This will give you only the data present in the SQL Server and not the database structure. Also BCP will generate large no. of files which may be difficult to manage. I think using SQL Server backup(in case you don’t want to use third party softwares) is the best option. You can have a backup strategy for the same like having backup once a week and differental backups everyday to reduce the time. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
As an alternative you should also test the procedure using SQL Server BACKUP/RESTORE process (as specified by Gaurav). In past I have seen couple of issues while restoring data when required, so for the BACKUP/RESTORE process which is heart for any recovery process, we use only SQL defined process not any third party tools. Check SQL Error log and vendor application logs for any information that might give information to assess the issue. Recently we started using SQLLiteSpeed on few services, which is working with no issues. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks for your input. It is very helpful. I have tried using the SQL Server Backup process again. I have been using my backup server as the destination for the .bak dumps (Using UNC in the destination of the backup). The speed is decent with 50 Gb dbs or less but it takes forever to backup a 90 Gb db. Any recommendations on settings etc?
Its expected I think for such a sheer size, try implementing DIFFERENTIAL BACKUPS in this case. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I agree with you on that. It will not do me any good if I cant get a full backup off first.
Are you using differnt arrays for storing the backup than the data file while generating backup. It is possible that the I/O Capacity has maxed out and I would recommend using performance monitor to observe the activity duting backup period. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>