Backup using SQL Server Express 2005

I am upgrading the backend of an Access 2003 mdb to SQL Server Express 2005 on a 6 user peer to peer network. In the past the user backed up the mdb every night by using compact and repair. I want to provide a simple and similar process for backing up the SQL Server database. I have created the following stored procedure which the user will invoke by clicking a Backup button in the Access front end. Since I really don't understand SQL Server backups, is this a good approach and if not, what should I be doing?

/****** Object:  StoredProcedure [dbo].[procBackupAndShrinkLog]    Script Date: 05/08/2009 07:43:40 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procBackupAndShrinkLog]
      @FQBackupName VARCHAR(1000) OUTPUT
AS
--
BEGIN
      DECLARE @BackupName VARCHAR(1000)
      DECLARE @DbName VARCHAR(500)
      DECLARE @DbPath VARCHAR(500)
      DECLARE @ErrMsg VARCHAR(1000)
      DECLARE @ErrNbr INT
      DECLARE @LogFileName VARCHAR(500)
      DECLARE @RC INT
      --
      EXECUTE @RC = master.dbo.xp_instance_regread
            'HKEY_LOCAL_MACHINE',
            'SOFTWARE\Microsoft\MSSQLServer\Setup',
            'SQLDataRoot',
            @param = @DbPath OUTPUT
      IF @RC <> 0 BEGIN
            SET @ErrMsg = 'master.dbo.xp_instance_regread failed with RC=' + CAST(@RC AS VARCHAR)
            RAISERROR (@ErrMsg,16,1)
            RETURN @RC
      END
      --
      SET @DbName = DB_NAME()
      SET @BackupName = @DbPath + '\Backup\'
                              + @DbName
                              + CONVERT(CHAR(8), GETDATE(), 112)
                              + STUFF(CONVERT(VARCHAR(5), GETDATE(), 8), 3, 1, '')
                              + '.BAK'
      SET @LogFileName = @DbName + '_Log'
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' backing up database to ' + @BackupName
      BACKUP DATABASE @DbName
            TO DISK = @BackupName
            WITH DESCRIPTION = 'Backup before log truncate'
      SET @ErrNbr = @@ERROR
      IF @ErrNbr <> 0 BEGIN
            RAISERROR (@ErrNbr,16,1)
            RETURN @ErrNbr
      END
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' shrinking log file'
      DBCC SHRINKFILE(@LogFileName, 1)
      SET @ErrNbr = @@ERROR
      IF @ErrNbr <> 0 BEGIN
            RAISERROR (@ErrNbr,16,1)
            RETURN @ErrNbr
      END
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' backing up log file'
      BACKUP LOG @DbName
            WITH TRUNCATE_ONLY
      SET @ErrNbr = @@ERROR
      IF @ErrNbr <> 0 BEGIN
            RAISERROR (@ErrNbr,16,1)
            RETURN @ErrNbr
      END
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' shrinking log file after truncate_only'
      DBCC SHRINKFILE(@LogFileName, 1)
      SET @ErrNbr = @@ERROR
      IF @ErrNbr <> 0 BEGIN
            RAISERROR (@ErrNbr,16,1)
            RETURN @ErrNbr
      END
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' done'
      SET @FQBackupName = @BackupName
      RETURN 0
END
rmkAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
you really only need this...scrap everything else.

      SET @DbName = DB_NAME()
      SET @BackupName = @DbPath + '\Backup\'
                              + @DbName
                              + CONVERT(CHAR(8), GETDATE(), 112)
                              + STUFF(CONVERT(VARCHAR(5), GETDATE(), 8), 3, 1, '')
                              + '.BAK'
      SET @LogFileName = @DbName + '_Log'
      --
      PRINT CONVERT(VARCHAR,GETDATE(),109) + ' backing up database to ' + @BackupName
      BACKUP DATABASE @DbName
            TO DISK = @BackupName
            WITH DESCRIPTION = 'Backup before log truncate'
      SET @ErrNbr = @@ERROR
      IF @ErrNbr <> 0 BEGIN
            RAISERROR (@ErrNbr,16,1)
            RETURN @ErrNbr
      END
0
 
rmkAuthor Commented:
Don't I have to worry about an ever increasing log file size?
0
 
chapmandewConnect With a Mentor Commented:
if you're in a full recovery mode, yes...you'll have to do log backups.  If you're not going to do log backups, then the full recovery mode doesn't help you and you can switch your recovery mode to simple.  

here's an article I wrote on it:  blogs.techrepublic.com.com/datacenter/?p=448
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
Depends how oftern records are inserted.  Note that SQL express has a 4GB data limit on dbs.  It never hurts to shrink the log though.
0
 
Anthony PerkinsConnect With a Mentor Commented:
And don't forget to change the description to just simply:
WITH DESCRIPTION = 'Backup'

:)

On a more serious note, if your original intent was to keep the transaction log size manageable, as you are unable to back it up, then simply change the Recovery Model from Full to Simple.  You will lose the point in time restore capabilities, but by doing a BACKUP LOG ... WITH TRUNCATE_ONLY you had already crippled that functionality.
0
 
Anthony PerkinsCommented:
>> It never hurts to shrink the log though.<<
Please don't give that impression.  Either set the Recovery Model to Simple or be prepared to backup the Transaction Log.  There are no other alternatives.
0
 
chapmandewCommented:
>> It never hurts to shrink the log though.<<

anthony, you beat me to it.  :)
0
 
rmkAuthor Commented:
It's my understanding the SQL Sever 2005 Express sets the recovery mode to simple by default. In simple recovery mode, if I do a full baclup each night, am I correct that the log file will be cleared (not shrunk) and the space will be reusable? Is the SQL Server 2005 Express limit of 4 GB limit for the combined size of the data file and log file or just the data file?
0
 
Anthony PerkinsCommented:
>>you beat me to it. <<
That was just one out of 10.

<Rant>
Why in God's name does MS ship SQL Server Express set to Full Recovery Model?  

On a lesser note. Do they not know that most users using Express come from MS Access and are going to assume that SHRINKDATABASE and SHRINKFILE is the same as "Compact and Repair Database"?
</Rant>
0
 
Anthony PerkinsConnect With a Mentor Commented:
>>It's my understanding the SQL Sever 2005 Express sets the recovery mode to simple by default.<<
Not unless it has changed in the last few months.

>>In simple recovery mode, if I do a full baclup each night, am I correct that the log file will be cleared (not shrunk) and the space will be reusable?<<
Correct.

>>Is the SQL Server 2005 Express limit of 4 GB limit for the combined size of the data file and log file or just the data file?<<
Not sure.
0
 
Anthony PerkinsCommented:
I just looked it up.
http://msdn.microsoft.com/en-us/library/ms345154.aspx
<quote>
The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and the distributed Transaction Coordinator is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Programmers intending to use SQL Server Express are recommended to keep these changes in mind when designing their applications.
</quote>
0
 
rmkAuthor Commented:
Just as a side note, I've got the backup working and the application working except for one problem. Now my transactions in my Access data access class are failing now that the tables are linked to SQL Server Express 2005, but they all work when the tables are linked to an Access 2003 mdb. ARGH - more problems to sort out !
0
All Courses

From novice to tech pro — start learning today.