Availability of SQL server when backup runs

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  July 30, 2004 at 9:29 am

  #109271

  I need help how to prove that SQL server can do backups when it is fully accessible for the users. I know this is true, because I’m a DBA for the last 7 years, but the people in my previous company do not know that and they are terrified that SQL server will not accessible for e very long time daily.

  Current situation:

  I left my job 3 months ago. The new DBA insists that for doing full nightly backup he needs to put SQL server in a single user mode, all the connections have to be closed, other way he gets errors. In this situation the SQL server is inaccessible between 10 pm and 5am every day – Oh, my God, so pity for the banks’ clients…

  The problem, I guess, is that he mixes DB maintenance plans procedures with the backup procedures, which do not need to be done every day for all databases at once.

  The previous backup plan was as follow:

  Every night full backup on all databases as they are very small – most between 300KB and 1G, transaction log backups every 20 min in the activity time between 5 am and 8 pm. 3 times/nights a week – backup master DB as there were often structural changes. Weekly maintenance and optimization plan for both users and system DBs on Sunday early in the morning. Additional checking for fragmentation, index corruption, etc. as needed. All databases were accessible 24/7, I never had a problem, several big restorations from a backup have been done, recovering data within minutes with almost no data lost. Most of the DBs are set up to allow filegroup/datafile backups, but the size right now do not require that.

  My current backup plans differs a lot from this simple scenario as some of my Dbs are huge – over 500 GB.

  I feel uncomfortable to make this posting and to waist everybody’s time for something so simple and well documented, but as I’m not there any longer, my colleagues - developers really need your help to have their databases available again.

  Any opinion, article, advise will be greatly appreciated. If you could share your experience with them to let them know how the maintenance and backups are done in some other places, that’ll be a great help.

  Thanks a lot,

  MJ

• Jason Hall

  Hall of Fame

  Points: 3906

  More actions

  July 30, 2004 at 11:03 am

  #517078

  Just perform a simple test.  Create an application that fires off a set of transactions 30 seconds after the backup starts.  You can even create several jobs that fire off at different times that will perform several inserts onto a table.  After the backup completes, verify that the backup succeeded and that the inserts did propegate into the table.  You could even then prove that the transactions were logged by restoring the full backup followed by the tlog backup.  I've had several questions posed to me along these same lines and users that are stuck in their ways find it hard to believe that a database is fully functional during a backup, but I've always been able to convince them.  The easiest way is to prove by example and physically show them results.  Maybe even run a profiler trace on the database while a backup is going on and prove that you can open up new connections.

   

  Let me know what you think.

• hoo-t

  SSChampion

  Points: 12713

  More actions

  July 30, 2004 at 11:08 am

  #517080

  From Books Online (Backup/Restore Architecture)

• Backups that can be performed while the database is in use, allowing backups to be made of systems that must run continuously.

  The backup processing and internal data structures of SQL Server 2000 are structured so that backups maximize their rate of data transfer with minimal effect on transaction throughput.

  Steve