Solved

Backup using SQL Server Express 2005

Posted on 2009-05-08
12
235 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:rmk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 24335485
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
 

Author Comment

by:rmk
ID: 24335558
Don't I have to worry about an ever increasing log file size?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 250 total points
ID: 24335577
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 24335583
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24335607
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24335614
>> 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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24335624
>> It never hurts to shrink the log though.<<

anthony, you beat me to it.  :)
0
 

Author Comment

by:rmk
ID: 24335678
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24335683
>>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24335708
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24335850
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
 

Author Closing Comment

by:rmk
ID: 31579403
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question