Is it 100% safe to stop SQL Server to copy mdf/ldf files for a replicated database?

I have mainly 2 questions.
1- Other than detaching a db through SQL or backing up a db, is it 100% safe
to
stop SQL Server service and then copy the .mdf/.ndf/.ldf files? Is there any
risk
or possibility of anything going wrong this way when choosing the copy
option?
2- If the database is being replicated, what are my options to make a
backup?
I can't detach the db because SQL demands the replication to be dropped
first.
Can I stop SQL Server service and then copy the .mdf/.ndf/.ldf files?
Thank youHi
Moving the database files will leave entries in sysdatabases that reference
the old files/database. If you don't drop replication before detaching it,
then it may not work when it's attached and you will have to clean up an
inconsistent system, so it is probably better to drop first.
John
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:37521BFB-16B4-498C-BEEC-F027AC55741B@.microsoft.com...
>I have mainly 2 questions.
> 1- Other than detaching a db through SQL or backing up a db, is it 100%
> safe to
> stop SQL Server service and then copy the .mdf/.ndf/.ldf files? Is there
> any risk
> or possibility of anything going wrong this way when choosing the copy
> option?
> 2- If the database is being replicated, what are my options to make a
> backup?
> I can't detach the db because SQL demands the replication to be dropped
> first.
> Can I stop SQL Server service and then copy the .mdf/.ndf/.ldf files?
> Thank you
>|||> Moving the database files will leave entries in sysdatabases that
> reference the old files/database. If you don't drop replication before
> detaching it, then it may not work when it's attached and you will have to
> clean up an inconsistent system, so it is probably better to drop first.
Thanks John, however I forgot to point out that I am not really moving
the files. I am making backup copies of the mdf/ldf files simply because
in case of restore, copying mdf/ldf and re-attaching them is much faster
than doing a restore. So physical file locations are not being changed in
this case.|||While there may be some cleverness in what you're trying to do, I'd look at
it in terms of support - PSS will give you no help if anything goes wrong
for this type of process, and at a time when you'd probably most need it -
disaster recovery. For this to be supported you'd have to look at
implementing a recognised backup strategy eg
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
Rgds,
Paul Ibison
(www.replicationanswers.com)|||> While there may be some cleverness in what you're trying to do, I'd look
> at it in terms of support - PSS will give you no help if anything goes
> wrong for this type of process, and at a time when you'd probably most
> need it - disaster recovery. For this to be supported you'd have to look
> at implementing a recognised backup strategy eg
> http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
Thanks for the info regarding PSS support. I will also read the link
about the backup strategies.|||On Mar 26, 7:58=A0am, "serge" <ser...@.nospam.ehmail.com> wrote:
> > While there may be some cleverness in what you're trying to do, I'd look=
> > at it in terms of support - PSS will give you no help if anything goes
> > wrong for this type of process, and at a time when you'd probably most
> > need it - disaster recovery. For this to be supported you'd have to look=
> > at implementing a recognised backup strategy eg
> >http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
> Thanks for the info regarding PSS support. I will also read the link
> about the backup strategies.
try this...
CopySharp is a GUI tool for copying open/inprocess/lock files. It is
inspired by robocopy and vshadow.
CopySharp V1.0 requires .Net Framework 3.5 and VC++ 2005 Runtime.
CopySharp V1.0 requires Microsoft=AE Windows=AE Server 2003, Microsoft=AE
Windows=AE XP.
For Example:
1. Try to backup/copy your .pst file(s), while your outlook is open.
2. Try to backup/copy your .mdf/.ldf (SQL Server) files, while your
SQL Server is running.
Locate it at: http://www.amitchaudhary.com/|||<<CopySharp is a GUI tool for copying open/inprocess/lock files. >>
How do you make sure that several files are from the same point in time? A database consists of
several database files, and they of course need to be from the same point in time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Amit" <amit.ary@.gmail.com> wrote in message
news:2036cba8-92d5-4a90-8108-2898fc1637b5@.u12g2000prd.googlegroups.com...
On Mar 26, 7:58 am, "serge" <ser...@.nospam.ehmail.com> wrote:
> > While there may be some cleverness in what you're trying to do, I'd look
> > at it in terms of support - PSS will give you no help if anything goes
> > wrong for this type of process, and at a time when you'd probably most
> > need it - disaster recovery. For this to be supported you'd have to look
> > at implementing a recognised backup strategy eg
> >http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
> Thanks for the info regarding PSS support. I will also read the link
> about the backup strategies.
try this...
CopySharp is a GUI tool for copying open/inprocess/lock files. It is
inspired by robocopy and vshadow.
CopySharp V1.0 requires .Net Framework 3.5 and VC++ 2005 Runtime.
CopySharp V1.0 requires Microsoft® Windows® Server 2003, Microsoft®
Windows® XP.
For Example:
1. Try to backup/copy your .pst file(s), while your outlook is open.
2. Try to backup/copy your .mdf/.ldf (SQL Server) files, while your
SQL Server is running.
Locate it at: http://www.amitchaudhary.com/

Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish
|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!
|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
--
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"
> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.
> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
--
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish