SQL Server Performance Forum – Threads Archive

Stored Procedure for Closing Database/Tables

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

What do you mean by "closing" a table? That’s a new term for me.

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.

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.

.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.

Satya, Thanks for that. How do I ensure that these type of files are ‘Closed’ prior to any Backup
Routine? rgds, lenhilldba

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.

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

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.

Well. The problem will happen at the time of restoring [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

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

RODUCTIONSQLPRODUCTION_DATA.MDF, EC=SHARING VIOLATION)
20070412 230853 2790 W3404 Unable to open file. (FILE=M

RODUCTIONSQLPRODUCTION_LOG.LDF, EC=SHARING VIOLATION)

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Obviously, you said during backup, hence my reply there. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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

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

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

RODUCTIONSQLPRODUCTION_DATA.MDF, EC=SHARING VIOLATION)
20070412 230853 2790 W3404 Unable to open file. (FILE=M

RODUCTIONSQLPRODUCTION_LOG.LDF, EC=SHARING VIOLATION)

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.

]]>