Is it possible to access data in a raw .mdf file from another database

Hello
We have a database which has crashed and only have the raw mdf file (and
associated ldf file). Can we import this data into a new database?
Gary
"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
Yes.
Make a copy and then use sp_attach_db to attach it to the server.

> Gary
>
|||Gary
It could be done , but I have seen many times it did not work\
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
> Gary
>
|||Greg
If the database was not properly detached (sp_detach) , it may not work .
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OJsKwLyMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Gary" <gary@.ggg.com> wrote in message
> news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Yes.
> Make a copy and then use sp_attach_db to attach it to the server.
>
>

Is it possible to access data in a raw .mdf file from another database

Hello
We have a database which has crashed and only have the raw mdf file (and
associated ldf file). Can we import this data into a new database?
Gary"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
Yes.
Make a copy and then use sp_attach_db to attach it to the server.
> Gary
>|||Gary
It could be done , but I have seen many times it did not work\
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
> Gary
>|||Greg
If the database was not properly detached (sp_detach) , it may not work .
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OJsKwLyMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Gary" <gary@.ggg.com> wrote in message
> news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Hello
>> We have a database which has crashed and only have the raw mdf file (and
>> associated ldf file). Can we import this data into a new database?
> Yes.
> Make a copy and then use sp_attach_db to attach it to the server.
>
>> Gary
>

Is it possible to access data in a raw .mdf file from another database

Hello
We have a database which has crashed and only have the raw mdf file (and
associated ldf file). Can we import this data into a new database?
Gary"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
Yes.
Make a copy and then use sp_attach_db to attach it to the server.

> Gary
>|||Gary
It could be done , but I have seen many times it did not work\
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Gary" <gary@.ggg.com> wrote in message
news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Hello
> We have a database which has crashed and only have the raw mdf file (and
> associated ldf file). Can we import this data into a new database?
> Gary
>|||Greg
If the database was not properly detached (sp_detach) , it may not work .
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OJsKwLyMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Gary" <gary@.ggg.com> wrote in message
> news:u9kCr8xMHHA.4848@.TK2MSFTNGP04.phx.gbl...
> Yes.
> Make a copy and then use sp_attach_db to attach it to the server.
>
>

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 copy of database and log file enough for backup?

Hello,

i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.

Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?

I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.

This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..

Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?

Is this enough to have a valid backup? Backup would be an attach of the .mdf file.

Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?

Regards,

Markus

And in my opinion attach a database should be enough. ít is the users, the owners, wish to get the data stored in the database.

In the past, as i tried to use RESTORE stuff, i get an error message. From the sql server system point of view this was ok because something of the restore file did not match the STRICT criteria for restore. But i lost the data.

Therefore a attach should do it, to fullfill the wishes of the owner. To show him the data of that .mdf file. Even it this .mdf file does not meet the critierias of the current version. SQL Server should inform the owner of that, and ask if it is allowed to try to converte the file to the current format. If OK, it should do everything to save as much data as possible.

Sorry, if it sounds a bit curious, but i would like a way to do the obvious things without force the owner to take any learning effort.

Read in a blog:
http://www.sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/Default.aspx
This schould not be the case..

Markus

|||

I personaly would use the back up and restore options, this is what they are designed for. You can run these from the Management studio (Express Version) or from a raw query. If you need to schedule it you can either use the normal scheduler that is in windows or use a custom one. For one of my clients I created a windows service that copied the function on the unix cron system but on a windows machine.

The only time that I have used the attach and detach functions is when I need to move a database quickly. I have seen some people use it to install the database when the program is installed, but for this I prefer to code a solution that creates the database from scripts. Doing it this way I know that the structure and data is clean at the time of install.

|||

Thank you Glenn.

But the question was, is copy enough? And under what conditions?

In my opinion, if Sql Server Express should be a common datastore, it should be easy to backup.

Without knowing Sql Server Books online, without knowing what "scripts" are. This is stuff for a few freaks, who likes things like that. But most of the people don't like to read such stuff. Most people hate this stuff.

What is if someone use a Sql Server database (any older version) and want to sell his computer. He copies the .mdf and .ldf file to a cd. He buys a new computer. Installs a new download of Sql Server. Tries to attach the copied files. This should be the only thing he should know. And Sql Server should be the best it can do and not show an error message.

Or what is if someone send's the .mdf and .ldf file via email to another person. Who knows which version of Sql Server he is running`?

What i mean, if Sql Server want to be a datastore of everyone it should meet the needs of everyone. Don*t kow and don't need to know what BOL is or what scripts are. Perform the needs of the owners autmatically and explain him in a few simple sentences.

I think today the normal person is a bit confused.

Best regards,

Markus

|||

When you do use the attach and detach system you do not have to copy the ldf file as this is only the transaction log file, In that should only be open transactions... If you are copying the file to a new location you will need to make sure that al transactions are commited to the database. This is why I prefer to use the backup option as this makes sure that at the time of the backup all of the data is stored. If you do use the attach and detach method there are chances of loosing data.

|||

Hello Glenn,

you wrote:
> The only time that I have used the attach and detach functions is when I need to
> move a database quickly. I have seen some people use it to install the database
> when the program is installed, but for this I prefer to code a solution that creates
> the database from scripts. Doing it this way I know that the structure and data is
> clean at the time of install.

This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

> When you do use the attach and detach system you do not have to copy the ldf
> file as this is only the transaction log file, In that should only be open transactions...

If i use attach and have only the .mdf file, is the .ldf file then new created?

> If you are copying the file to a new location you will need to make sure that al
> transactions are commited to the database.

Is this the case when i use Detach? When does a detach fail?

> This is why I prefer to use the backup option as this makes sure that at the time of
> the backup all of the data is stored. If you do use the attach and detach method
> there are chances of loosing data.

Are there limitations of restore and backup? When will a restore fail? What is of different version ofs sql server, differences betwen the system where the database was backed up and where it is to be restored? Is there allways compatibility or what must the user care for, that the restore will run?

Regards,
Markus

|||

>This is what i want to do when my program is installed. Install SQL Server Express with a named instance. Copy the database files and attach them. Because there ist allready data in the database files. What do you think is the risk of that way? Have you ever heard that his fails?

Well there are certain things that are not stored in the database itself. Logins, for example.

I have had problems doing exactly what you described in SQL 2000, especially when my original database had any users other than dbo.I've had problems with restore as well.

If you are shipping initial data with your product I would recommend that you do it all in code.INFORMATION_SCHEMA is your friend.I've done things using batch scripts and the command line tools.These work but are not flexible enough and don't provide sufficient error detection.

|||

>Well there are certain things that are not stored in the
>database itself. Logins, for example.
>I have had problems doing exactly what you described
>in SQL 2000, especially when my original database had
>any users other than dbo. I've had problems with
>restore as well.

But when you install a seperate named instance for your application? Do you see this problems in this case too?

|||

Yes you do see the same problems, a Named Instance is just like a completly new server install...

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