Solved

Backup using SQL Server Express 2005

Posted on 2009-05-08
12
234 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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