SQL Server Performance

Stored Procedure for Closing Database/Tables

Discussion in 'Getting Started' started by lenhilldba, Jun 19, 2007.

  1. lenhilldba New Member

    Hi,

    First Time at this. Can someone please tell
    me how to setup and activate a Stored Procedure
    to Close all Tables within a Database each night
    @ 3:00am. Using SQL 2000.

    Thank You
  2. Adriaan New Member

    What do you mean by "closing" a table? That's a new term for me.
  3. lenhilldba New Member

    Adriann,

    I have a couple of tables TestLen.mdf and TestLen.ldf that
    give a 'Sharing Violation' whenever we carry out an ovenight
    backup. I'd like to 'Close' both these tables prior to the
    backup, hence the reason for the Stored Procedure.

  4. Adriaan New Member

    Use the built-in tools of SQL Server for backing up the database, and let your backup software backup the backup.

    Other than that, most professional backup software should have an "agent" facility that can interface with SQL Server.
  5. satya Moderator

    .MDF & .LDF are not tables, they are data and log files for a database. As suggested you have to use native BACKUP statement in any case. Do not attempt to backup those files directly, as you will get default error.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. lenhilldba New Member

    Satya,

    Thanks for that. How do I ensure that these type of files are 'Closed' prior to any Backup
    Routine?

    rgds,

    lenhilldba
  7. satya Moderator

    Why do you need to close these files?

    On the other hand for the BACKUP process, SQL Server performs online backup, that means you need to close any tables or objects. As documented on the books online:
    quote:
    The scope of a backup of data (a data backup) can be a whole database, a partial database, or a set of files or filegroups. For each of these, SQL Server supports full and differential backups:

    Full backup

    A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.


    Differential backup

    A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base. Typically, differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup. Therefore, using differential backups can speed up the process of making frequent backups to decrease the risk of data loss. Usually, a differential base is used by several successive differential backups. At restore time, the full backup is restored first, followed by the most recent differential backup.

    Over time, as a database is updated, the amount of data that is included in differential backups increases. This makes the backup slower to create and to restore. Eventually, another full backup will have to be created to provide a new differential base for another series of differential backups.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. Madhivanan Moderator

    When you do backup make sure that QA and EM are not pointed to that database
    I think thats what you meant to say "Close objects"

    Madhivanan

    Failing to plan is Planning to fail
  9. satya Moderator

    I don't see any problem in performing BACKUP while using same database.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. Madhivanan Moderator

    Well. The problem will happen at the time of restoring [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. lenhilldba New Member

    Everyone,

    Here is my problem from the Backup Log :-

    20070412 221956 2790 W3404 Unable to open file. (FILE=M:ACCESSDBARK_DATA.MDF, EC=SHARING VIOLATION)
    20070412 221956 2790 W3404 Unable to open file. (FILE=M:ACCESSDBARK_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230641 2790 W3404 Unable to open file. (FILE=M:HOMEDELIVERYCODEHOMEDELIVERY_DATA.MDF, EC=SHARING VIOLATION)
    20070412 230641 2790 W3404 Unable to open file. (FILE=M:HOMEDELIVERYCODEHOMEDELIVERY_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230812 2790 W3404 Unable to open file. (FILE=M:MARKETSERVMARKETSERV.MDF, EC=SHARING VIOLATION)
    20070412 230812 2790 W3404 Unable to open file. (FILE=M:MARKETSERVMARKETSERV_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230853 2790 W3404 Unable to open file. (FILE=M:pRODUCTIONSQLPRODUCTION_DATA.MDF, EC=SHARING VIOLATION)
    20070412 230853 2790 W3404 Unable to open file. (FILE=M:pRODUCTIONSQLPRODUCTION_LOG.LDF, EC=SHARING VIOLATION)
  12. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Obviously, you said during backup, hence my reply there.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  13. mrbonney2002 New Member

    lenhilldba,

    You first need to perform a backup of your SQL Server databases. The SQL Server backup will create a .bak file for each database. Backup the .bak files created by SQL Server not the .mdf or .ldf files.

    As suggested, look at the BOL (Books online) that came with SQL Server for configuring your backup.

    Thanks,
    mrbonney2002
  14. FrankKalis Moderator

    quote:Originally posted by lenhilldba

    Satya,

    Thanks for that. How do I ensure that these type of files are 'Closed' prior to any Backup
    Routine?

    rgds,

    lenhilldba
    Even when nobody is connected to SQL Server, these database files still cannot be copied by the OS. You would need to stop the MSSQLSERVER service to "close" these files before you can back them up at the OS level. However, this is NOT the recommended or advisable way. As has been said, use the built-in functionality or use a specialized third party SQL Server backup tool.

    See if these links help:
    http://support.microsoft.com/kb/903643
    http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,540-6-100-225-1,00.htm

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  15. satya Moderator

    Do not use that backup tool to back the .MDF & .LDF files or can we know what is the reason in using this tool?


    quote:Originally posted by lenhilldba

    Everyone,

    Here is my problem from the Backup Log :-

    20070412 221956 2790 W3404 Unable to open file. (FILE=M:ACCESSDBARK_DATA.MDF, EC=SHARING VIOLATION)
    20070412 221956 2790 W3404 Unable to open file. (FILE=M:ACCESSDBARK_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230641 2790 W3404 Unable to open file. (FILE=M:HOMEDELIVERYCODEHOMEDELIVERY_DATA.MDF, EC=SHARING VIOLATION)
    20070412 230641 2790 W3404 Unable to open file. (FILE=M:HOMEDELIVERYCODEHOMEDELIVERY_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230812 2790 W3404 Unable to open file. (FILE=M:MARKETSERVMARKETSERV.MDF, EC=SHARING VIOLATION)
    20070412 230812 2790 W3404 Unable to open file. (FILE=M:MARKETSERVMARKETSERV_LOG.LDF, EC=SHARING VIOLATION)
    20070412 230853 2790 W3404 Unable to open file. (FILE=M:pRODUCTIONSQLPRODUCTION_DATA.MDF, EC=SHARING VIOLATION)
    20070412 230853 2790 W3404 Unable to open file. (FILE=M:pRODUCTIONSQLPRODUCTION_LOG.LDF, EC=SHARING VIOLATION)

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  16. Adriaan New Member

    To summarize ---

    While SQL Server is active, the MDF and LDF files are in use. Your backup software can only backup these files after you detach your database or shut down the SQL Server service. But it is really not necessary to bring the system down - check the following two options:

    (1)
    Your backup software may have a so-called "agent" for SQL Server, which works together with the SQL Server software to backup databases, while SQL Server is active. The agent can be part of the backup software package, or may be an add-on that you have to buy separately.

    (2)
    You can setup a maintenance plan in SQL Server to create a backup of your database. This will create a separate file. You can let your backup software backup this separate file, while SQL Server is active.

Share This Page