backup strategies for hosted enviroment ?

Im about to start a project that will be hosted by a third party web host. What is a common way to backup your database and have the backup saved ? The data may end up being several 100 MB of user settings, text etc (blog type stuff). If the DB gets to be several 100MB, then does making a backup and ftping it offsite sound reasonable ? Does ftp bandwidth usually count against your overall bandwidth usage ?

For FTP bandwidth going against your sites bandwidth... Ask your hoster - they'll tell you for sure.

For backing up, most hosts back everything up for you automatically (most of them do it daily).

What I've done in the past is every week or so I just take my DB offfline and copy it down to my computer using FTP. I then burn it. (not with fire)...

|||

Agree.

Make a backup and then ftp it to another place is the comman way. As to "make a back up every day or every week" , i would suggest you using sql server jobs. You can create a job and then schedule it run automatically every a certain time interval. You can refer to the following article to see how to create a backup job and make it run regularly:

To schedule a database backup operation by using SQL Server Management Studio in SQL Server 2005, follow these steps:

1.Start SQL Server Management Studio.2.In theConnect to Server dialog box, click the appropriate values in theServer type list, in theServer name list, and in theAuthentication list.3.ClickConnect.4.In Object Explorer, expandDatabases.5.Right-click the database that you want to back up, clickTasks, and then clickBack Up.6.In theBack Up Database - DatabaseName dialog box, type the name of the backup set in theName box, and then clickAdd underDestination.7.In theSelect Backup Destination dialog box, type a path and a file name in theDestinations on disk box, and then clickOK.8.In theScript list, clickScript Action to Job.9.In theNew Job dialog box, clickSteps underSelect a page, and then clickEdit if you want to change the job parameters.

Note In theJob Step Properties - 1 dialog box, you can see the backup command.10.UnderSelect a page, clickSchedules, and then clickNew.11.In theNew Job Schedule dialog box, type the job name in theName box, specify the job schedule, and then clickOK.

Note If you want to configure alerts or notifications, you can clickAlerts orNotifications underSelect a page.12.ClickOK two times.

You receive the following message:

The backup of database 'DatabaseName' completed successfully.

Hope my suggestion helps

Backup Slow Throughput

Hi Team,

I have a SQL 2000 instance with 46 Databases (all databases put together will be 15 GB in size). I am running a SQL Backup using a third party software.

My full backup of the SQL instance which backs up 15 GB of data finishes within 30-45 minutes. But, my differential backup of the same instance which backs up only 150 -250 MB of data takes 12 hours to backup.

I found a knowledge article from MS Support site which says, differntial backup would take more time than full in few scenarios.

http://support.microsoft.com/default.aspx?scid=kb;en-us;196658

But the above document is for SQL 7.0. Will it be the same for SQL 2000 and 2005 too? If yes, can you please tell if I can increase the speed of differential backup in my environment. Should I modify any SQL Parameters?

Please let me know your thoughts on this..

Thanks
Santhosh

This really doesn't make sense.

The codepath for differential backups is the same as for fulls. The only difference is that in differential backups, we optionally skip writing some pages to the backup stream. The test to see if we should skip a page is very fast. Generally, worst case for differential backups is that they take the same time as a full backup.

Is there perhaps some other backup activity going on at the same time which could be causing lock contention?

If not, you can try starting SQL with traceflags 3605 and 3004 and sending the log to us (kevin.farlee@.microsoft.com). Those flags will cause more verbose logging about the backup process.

|||

"starting SQL with traceflags 3605 and 3004" - Can you be more specific on how to start SQL with trace flags.

I have not done this before. Also , it would be great if you could say what are we going to achieve by starting SQL with traceflags 3605 and 3004 .

Thank you,

Santhosh

|||

You can enable the trace flags with the command:

DBCC TRACEON(3004, 3605, -1)

What this does is to cause SQL to log much more detailed information about the backup process in the SQL log.

This information will help us to determine what is happening.

After collecting this information, you can use the command

DBCC TRACEOFF(3004, 3605, -1)

to disable the trace flags and go back to normal logging.