Solved

Backup using SQL Server Express 2005

Posted on 2009-05-08
12
232 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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