Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Restore DB from mdf and ldf

    Hello,


    i have an mdf and ldf file, is there any way to restore the DB from these two files? ( sp_detach_db is not used)


    Pl discuss
    Cheers....

    baburajv

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Use sp_attach_db system stored procedure.

    OR

    Open Enterprise Manager -> Databases -> Right Mouse Key -> All Tasks -> Attach Database.

    If you have Backup file than use Restore Backup from query analyzer.

    For more information explore Books OnLine from query analyzer...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db


    --
    Cheers....

    baburajv

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by baburajv
    i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db
    Can you describe your problem in detail instead of ambiguous information..?
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by baburajv
    i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db
    --
    why is that so?? Help us...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Aug 2003
    Posts
    29

    Use restore?

    Restore specific files or filegroups:

    RESTORE DATABASE {database_name | @database_name_var}
    <file_or_filegroup> [,...n]
    [FROM <backup_device> [,...n]]
    [WITH
    [DBO_ONLY]
    [[,] FILE = file_number]
    [[,] MEDIANAME = {media_name | @media_name_variable}]
    [[,] NORECOVERY]
    [[,] {NOUNLOAD | UNLOAD}]
    [[,] REPLACE]
    [[,] RESTART]
    [[,] STATS [= percentage]]

    For more info try reading this...

    http://doc.ddart.net/mssql/sql70/ra-rz_9.htm
    Joo Marto

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jpmartop
    Restore specific files or filegroups:

    RESTORE DATABASE {database_name | @database_name_var}
    <file_or_filegroup> [,...n]
    [FROM <backup_device> [,...n]]
    [WITH
    [DBO_ONLY]
    [[,] FILE = file_number]
    [[,] MEDIANAME = {media_name | @media_name_variable}]
    [[,] NORECOVERY]
    [[,] {NOUNLOAD | UNLOAD}]
    [[,] REPLACE]
    [[,] RESTART]
    [[,] STATS [= percentage]]

    For more info try reading this...

    http://doc.ddart.net/mssql/sql70/ra-rz_9.htm

    yeah that works with backup files. for mdf's and ldfs, you use sp_attach_db, but this guy will not giving us the error he is getting so he is getting ignored.
    If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Hello ppl,

    this is not a real life scenario, i was thinking whether i can copy the mdf and ldf file from a DB server and copy it to another machine, and try to restore the Db, from these two files ( i am not taking a backup or detaching the original DB). my question is, whether i can copy the mdf and ldf files to another system, and restore the DB there.

    most of the info in wed says that i have to detach the db and attach it in the destination. i just want to know whether any other mechanism is there to restore the db from copied mdf and ldf files

    im using MS SQL 2k
    Cheers....

    baburajv

  9. #9
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    was thinking whether i can copy the mdf and ldf file from a DB server and copy it to another machine, and try to restore the Db, from these two files ( i am not taking a backup or detaching the original DB). my question is, whether i can copy the mdf and ldf files to another system, and restore the DB there.
    you can attach .mdf / .ldf files using EXEC SP_ATTACH_DB

    most of the info in wed says that i have to detach the db and attach it in the destination. i just want to know whether any other mechanism is there to restore the db from copied mdf and ldf files.
    You need to use SP_DETACH_DB, if database is on-line (i.e. in use). If any user is using database than SQL Server will gives you an error for same.

    It's quite not necessary that first you have to use sp_detach_db then sp_attach_db stored procedures (As you have written in your previous post), unless your database is online.

    Instead of detach then attach database use backup - restore. Backup - Restore will not hurt database availability.

    Remember in SQL every command is made for some special purpose. Refer Book OnLine from query analyzer.

    And the most important thing : Be Explanatory, specially when you are asking someones help...
    Last edited by rajeshpatel; 11-08-06 at 01:29.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you copy the datafiles while the server is running, you will likely get junk, unless the database is very small. Even then, you are not guaranteed anything. If you shut down SQL Server entirely, then you can get a consistent set of files. This is usually called a "cold backup", and is extremely rare in the SQL Server world.

  11. #11
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by MCrowley
    If you copy the datafiles while the server is running, you will likely get junk, unless the database is very small. Even then, you are not guaranteed anything.
    We can not copy database while database is 'ONLINE' on the server, if you try SQL Server will gives you 'database being in use' error for same.

    If you shut down SQL Server entirely, then you can get a consistent set of files.
    For copying single database we don't need to shutdown entire SQL Server, just put the database offline & copy your database.

    Code:
    -- Make sure no any user is using database, else this will not work.
    -- Run this command from query analyzer.  
    
    -- this will put the database offline.
    
    use Master
    GO
    sp_dboption 'pubs', 'offline', 'True'
    
    -- Bring database online.
    
    sp_dboption 'pubs', 'offline', 'False'
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    While I have not actually tried it, I think xcopy will give you a file regardless of the file being in use. This tends to lead some administrators to think they have a backup solution, when they really do not. Some backup packages may also be able to back up open files, which also leads to this mistaken impression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •