[dba-SQLServer] Database backups

John,

Below is a backup stored procedure that I use on all databases.
It will give you a rotating 7 day backup  If you need it more often,
4 or 5 time a day, then you can add a time stamp for when it is run
at the end of the name of the file.

The WITH INIT will overwrite the file each time.

Changing the recovery mode to SIMPLE and then back to FULL
will allow for the log to be truncated.  This part, you may only want
to run at the midnight backup, and use one that does not change the
recovery model during the day if you are backing up multiple times
during the day.


CREATE PROCEDURE [dbo].[usp_BackupDatabase_DOW]
     /*
            Purpose: to back up the database and automatically 
truncate the log file
            Author: Robert L. Stewart
            Paramaters: None
     */
AS
BEGIN
     DECLARE
             @DOW AS TINYINT
           , @FileName AS VARCHAR( 250 )
           , @FileExtension AS VARCHAR( 5 )
           , @FilePath AS VARCHAR( 100 )
           , @DOW_String VARCHAR( 15 )

     SET @DOW = DATEPART( dw , current_timestamp )

     /* allows you to set a path other than the local server */

     SET @FilePath = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\'

     SET @FileExtension = '.bak'

     IF @DOW = 1
         BEGIN
             SET @DOW_String = 'Sunday'
         END
     ELSE
         BEGIN
             IF @DOW = 2
                 BEGIN
                     SET @DOW_String = 'Monday'
                 END
             ELSE
                 BEGIN
                     IF @DOW = 3
                         BEGIN
                             SET @DOW_String = 'Tuesday'
                         END
                     ELSE
                         BEGIN
                             IF @DOW = 4
                                 BEGIN
                                     SET @DOW_String = 'Wednesday'
                                 END
                             ELSE
                                 BEGIN
                                     IF @DOW = 5
                                         BEGIN
                                             SET @DOW_String = 'Thursday'
                                         END
                                     ELSE
                                         BEGIN
                                             IF @DOW = 6
                                                 BEGIN
                                                     SET @DOW_String = 'Friday'
                                                 END
                                             ELSE
                                                 BEGIN
                                                     IF @DOW = 7
                                                         BEGIN
                                                             SET 
@DOW_String = 'Saturday'
                                                         END
                                                 END
                                         END
                                 END
                         END
                 END
         END

     SET @DOW_String = 'Daily'

    /* this line may have to be chaged to a select into a variable
    and then checked*/

     IF DATABASEPROPERTYEX( 'USL_DW' , 'Recovery' ) != 'SIMPLE'
         BEGIN

             /* set the recovery model to simple*/

             ALTER DATABASE USL_DW SET RECOVERY SIMPLE;
         END

     SET @FileName = @FilePath + 'USL_DW_' + @DOW_String + @FileExtension

     BACKUP DATABASE USL_DW TO DISK = @FileName WITH INIT;

     SET @FileName = @FilePath + 'USL_DW_Log_' + @DOW_String + @FileExtension

     BACKUP LOG USL_DW_Log TO @FileName WITH INIT

     BEGIN
         DBCC SHRINKFILE( USL_DW_Log , 1 )
     END

     ALTER DATABASE USL_DW SET RECOVERY FULL;

END


>Date: Sat, 25 Feb 2012 11:46:34 -0500
>From: jwcolby <jwcolby at colbyconsulting.com>
>To: Sqlserver-Dba <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] Database backups
>Message-ID: <4F49106A.90005 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>How does an incremental database backup work?  I kinda have a 
>picture in my head, only the changed
>stuff is backed up, to the same file (maybe?) and so forth.  Not 
>even sure if that is true.
>
>What about restores from?  Is the entire thing restored?  Only 
>changes from a specific date / time?
>
>My client will never have more than my expertise (which is scary) so 
>I need to get a handle on this.
>
>ATM they are backing up the entire thing every night using "windows 
>backup", but I doubt that they
>are testing restores.  I have found some scripts that seem to do a 
>good job (though how would I
>know?) here:
>
>http://ola.hallengren.com/downloads.html
>
>I have built them up in a _DISMaster database where I keep such 
>things.  I actually ran them and got
>a full backup of every user database (that is what I specified to 
>the SP) in the default backup
>location in a directory structure that this script builds if necessary.
>
>It looks like this thing could be the basis for a backup strategy, 
>but I need to know more about
>restores specifically.
>
>The client is a call center for insurance claims.  The entire 
>company is about 50-60 people with
>about 25 people in the database all day.
>
>The data was going into Access MDB backends but we are moving 
>towards SQL Server backends.  They
>work all day adding / modifying data.  I would like to be able to do 
>a "point in time" kind of
>restore in case of disaster.  AFAICT that means that I have to do a 
>backup every N minutes / hours
>or something like that in order to ensure that we can get back to a 
>point in time N minutes / hours ago.
>
>Am I close?  As I have said many times I am not a SQL Server admin 
>so I need to learn enough about
>this specific subject to handle this aspect of the business.
>
>Any advice or concise focused readings you can point me to would be 
>very much appreciated.
>
>--
>John W. Colby
>Colby Consulting

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com