Solved

Backup using SQL Server Express 2005

Posted on 2009-05-08
12
229 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
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
>> It never hurts to shrink the log though.<<

anthony, you beat me to it.  :)
0
 

Author Comment

by:rmk
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now