Data file backups and ArcServe

We are attempting to backup our SQL Server data files (MDF
and LDF) onto tape using Brightstor ARCserve. (I have my
normal daily backups - this is for offsite redundancy) The
Arcserve backups of the data files are failing
consistantly. I suspect that arcserve is not able to get
an image of the data files because they are open and
active - transactions run on this server 24/7. Does anyone
have experience with this? Is there a workaround besides
stopping the SQL service? I'm satisfied with taping the
daily backups, but my se wants the data itself.
RLR
You would need to purchase the Open File Agent in order to
backup the files while the SQL Server is running.
-Sue
On Thu, 22 Apr 2004 07:18:17 -0700, "rlruss"
<rlrsysdev@.mindspring.com> wrote:

>We are attempting to backup our SQL Server data files (MDF
>and LDF) onto tape using Brightstor ARCserve. (I have my
>normal daily backups - this is for offsite redundancy) The
>Arcserve backups of the data files are failing
>consistantly. I suspect that arcserve is not able to get
>an image of the data files because they are open and
>active - transactions run on this server 24/7. Does anyone
>have experience with this? Is there a workaround besides
>stopping the SQL service? I'm satisfied with taping the
>daily backups, but my se wants the data itself.
>RLR
|||According to my se, we have Open agent installed - it works for everything except the db files. I created a non-sql file with the mdf extension, and it copied it fine. We're going with taping the dailies until this gets figured out - se isn't thrilled, bu
t I can live with it.
so it goes..
sql

Data corruption in ldf when debugging WinForm app

I'm trying to build my 1st WinForm app which uses SqlExpress and when I click Debug and test my app everything is fine (though occasionally I do get time out errors - but that's another question I guess), however I can't then debug the app again because I get the following error:

"The log scan number (34:410:82) passed to log scan in database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. \r\nAn error occurred during recovery, preventing the database '3D1D0C232D2C55B0EF6C455470CD3D4C_S\\VISUAL STUDIO 2005\\PROJECTS\\MYFIRSTAPP\\MYFIRSTAPP\\BIN\\DEBUG\\OTJDB.MDF' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.\r\nCannot open user default database. Login failed.\r\nLogin failed for user 'SQurreL\\Simon'."

I can't delete the ldf or mdb files in the debug\bin folder because they are in use, so I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again... which is beginning to get right on my nerves now

Can anyone help with my sanity please?

Hi ganseki,

I think the error message has claimed very clearly: " .....that the log file (.ldf) does not match the data file (.mdf)." If mdf file and the corresponding ldf file cannot match, we cannot open that database again--that's designed by microsoft. So, we need to "If this error occurred during replication, re-create the publication. Otherwise, restore from backup..".

I have to stop the SQLExpress service, kill the sqlserv.exe process, delete the ldf and mdb files, then restart the SQLExpress service and try again

I think what you did is right. So, does this error still occure after you did that? Also i'm curious about what you did to that database thru your application, that you get that error message.

Hope my suggestion helps

|||

Thanks for the reply.

Any idea why that is happening each time I debug the app? Or is it normal for WinForms development to have to jump through those hoops in order to test the changes.

It takes longer to stop the service, kill the process, delete the files and restart the service than to make a change to a column width in a datagridview, which if I then find is too narrow or too wide means I have to go through that whole process again just to check that changing the width from 150 to 100 isn't enough? This involves no change to the database, all that would have happened is a select to get the data for the grid, but it happens even without any database interaction - i.e. just opening the app (which starts with a blank page).

After debugging I click the close button on the form, the app closes and Visual Studio quits debug mode. I make changes (which can be as trivial as my illustration above) and then have to go through that routine. And that happens each and every time I want to debug.

It is driving meinsane
Is there something I should be doing differently to stop this from happening?

Data base growth control

Please anybody can explain how to reduce MDF LDF files size
or tuneup the MS SQL Server

Quote:

Originally Posted by Ashutosh Keskar

Please anybody can explain how to reduce MDF LDF files size
or tuneup the MS SQL Server

I'm not absolutely sure what you're after but you can change the size and growth properties of both files in Enterprise Manager: right click on the database -> Properties -> Data files and Transaction Log tab.|||Hi Ashutosh Keskar and welcome to TSDN MSSQL Forum,

You can also take a look at the maintenance plan - Enterprise manager -> Server -> Management -> Database Maintenance and see if you are running a job to reorg the data and index pages( may be part of your backup), there is also an option to free unused space.

I cannot guarantee it will help (there is a small chance it could make performance worse) but it is worth a look.

Regards Purple

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

data and log files won't copy

I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.

Hi,

you have to detach your database form your sql server 2000, and then attach them to the sql server 2005.

...

Detaching a database requires exclusive access to the database

USE master;
ALTER DATABASE dbname
SET SINGLE_USER;
GO

exec sp_detach_db @.dbname='dbname'

...

plz read BOL for more information: Detaching and Attaching Databases

|||

If you are restoring from an actual backup file (which usually has a .bak extension), you would not need to copy the data and log files. The restore process creates new ones from the backup file.

There are two easy ways to go from 2000 on one DB Server to 2005 on another DB Server. One is to backup the SQL Server 2000 database to a .bak file, then copy the .bak file to the new server and use SQL Server 2005 to do the restore (on the new server). This converts the database to 2005 format.

The second way is to detach the database in SQL Server 2000 and then copy the data and log files to the new server and then use SQL Server 2005 to attach them. This also converts the database to 2005 format. Either way, once you have restored or attached a SQL Server 2000 database in SQL Server 2005, the database cannot go back to SQL Server 2000 format!

Finally, whichever method you use, you should change the compatibility level to 90 and run sp_UpdateStats on the upgraded database.

|||Thank you for all your help. This worked out fine.|||

Hi,

First step :Take database offline or stop the sql server 2000 service.

After that you copy the .mdf and .ldf files.

withRegards

S.kuraliniyan

SystemEngineer