Log dosyası olmayan MDF dosyasını ATTACH işlemi ( SQL 2008 )
<blockquote> <h1><span style="font-size:36px"><span style="font-family:inherit"><span style="color:inherit">Attaching SQL Server database without log file</span></span></span></h1> <div class="metadata">Tuesday, July 21, 2009 <div class="tags"><a class="tag label label-default" href="https://weblogs.asp.net/gunnarpeipman/Tags/Databases" rel="tag" style="box-sizing:border-box; background:#999999; color:#ffffff; text-decoration:none; display:inline; padding:0.2em 0.6em 0.3em; font-size:10.5px; font-weight:bold; line-height:1; text-align:center; white-space:nowrap; vertical-align:baseline; border-radius:0.25em">Databases</a> <a class="tag label label-default" href="https://weblogs.asp.net/gunnarpeipman/Tags/SQL%20Server" rel="tag" style="box-sizing:border-box; background:#999999; color:#ffffff; text-decoration:none; display:inline; padding:0.2em 0.6em 0.3em; font-size:10.5px; font-weight:bold; line-height:1; text-align:center; white-space:nowrap; vertical-align:baseline; border-radius:0.25em">SQL Server</a></div> </div> <p style="text-align:start"><span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">I needed to attach SQL Server 2008 database to server. There was no log file, just mdf. I don’t know why but it is not very easy to get this database online. After some digging in internet I found solution.</span></span></span></span></p> <p style="text-align:start"><span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">I suggest you to read and try out example by Paul S. Randal <a href="http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx" style="box-sizing:border-box; background:transparent; color:#428bca; text-decoration:none">TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database</a>. I also suggest you to bookmark this posting, you never know…. I found simplest solution from stackoverflow: <a href="http://stackoverflow.com/questions/773059/how-to-recover-database-from-mdf-in-sql-server-2005" style="box-sizing:border-box; background:transparent; color:#428bca; text-decoration:none">How to recover database from MDF in SQL Server 2005?</a> It works also for SQL Server 2008.</span></span></span></span></p> <ol> <li>Create database with same name as MDF file you have.</li> <li>Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.</li> <li>Start SQL Server. Database will be now in suspect state because log file is not correct.</li> <li>Run the following script:<br /> <br /> <span style="color:#0000ff">USE</span> [master]<br /> <span style="color:#0000ff">GO<br /> ALTER DATABASE</span> [MyDatabase] <span style="color:#0000ff">SET EMERGENCY<br /> GO<br /> ALTER DATABASE</span> [MyDatabase] <span style="color:#0000ff">SET SINGLE_USER<br /> GO<br /> DBCC</span> CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)<br /> <span style="color:#0000ff">GO<br /> ALTER DATABASE</span> [MyDatabase] <span style="color:#0000ff">SET MULTI_USER<br /> GO<br /> ALTER DATABASE</span> [MyDatabase] <span style="color:#0000ff">SET ONLINE<br /> GO</span></li> </ol> <p style="text-align:start"><span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">When I ran this script I got the following output.</span></span></span></span></p> <p style="text-align:start"> </p> <p style="text-align:start"> </p> <hr /> <p><span style="color:#ff0000">Msg 5173, Level 16, State 1, Line 1<br /> One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.</span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Log file 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS2008\MSSQL\DATA\MyDatabase_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Warning: The log for database 'MyDatabase' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">DBCC results for 'MyDatabase'.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9675, State 1: Message Types analyzed: 14.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9667, State 1: Services analyzed: 3.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9668, State 1: Service Queues analyzed: 3.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.</span></span></span></span><br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.</span></span></span></span><br /> <br /> <span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">… Long list of GBCC messages …</span></span></span></span></p> <p style="text-align:start"> </p> <p style="text-align:start"><span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.<br /> DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span></span></span></span></p> <hr /> <p style="text-align:start"> </p> <p style="text-align:start"> </p> <p style="text-align:start"><span style="font-size:14px"><span style="color:#333333"><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif"><span style="background-color:#ffffff">You can see the error message in the output but that was no problem after all. My database is now online and works as expected.</span></span></span></span></p> </blockquote>
0 Yorum