Backing up .mdf and .ldf files

I want to copy and paste .mdf and .ldf from our SQL server.
Am I correct in saying that the mdf and ldf files are locked into th SQL
databases and require the files to be detached via Enterprise Manager (for
SQL2000)?
In which case, how do I copy the .mdf and .ldf files to another HDD?
Is there a Utility I can use or another way?
Please help.
skcLookup sp_detach_db ,sp_attach_db in the BOL.
You can stop your services and copy/paste the files. I prefer
BACKUP/RESTORE operations
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:D6D5728B-1FEC-44AD-80DA-D93293D8CA4B@.microsoft.com...
>I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc|||Skc wrote:
> I want to copy and paste .mdf and .ldf from our SQL server.
> Am I correct in saying that the mdf and ldf files are locked into th SQL
> databases and require the files to be detached via Enterprise Manager (for
> SQL2000)?
> In which case, how do I copy the .mdf and .ldf files to another HDD?
> Is there a Utility I can use or another way?
> Please help.
> skc
Hi
Why do you want to copy the files rather than using Backup/Restore? If
you want to copy the database files, you'll have to detach them first
using sp_detach_db. You can now copy them as regular files and you can
then use sp_attach_Db to create the database when you've copied the
files to the new server.
Please keep in mind that your database will be unavailable when you run
sp_detach_Db and then until you attach it again with sp_attach_db.
You should also keep in mind, that after you've run the sp_detach_db you
haven't got a working database - not even the original. By this I mean
that if something goes wrong during the detach and the database file get
corrupted, you have lost your database. I know that it's not very likely
that it fails and if it eventually does, you might be able to recover it
in some way, but it's a potential risk.
If you go the backup route, you'll still have your original database
even though your backup doesn't work, so you can always create a new backup.
HTH
Regards
Steen