tag:blogger.com,1999:blog-58294453920747121522024-03-05T15:02:18.815-08:00A true friend for SQL ServerMark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-5829445392074712152.post-27317880574260662802013-03-27T21:16:00.000-07:002013-03-27T21:16:23.416-07:00How to Restore SQL server Database with Corrupt Media Backup?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">Back</span><span lang="en-US">up
is a thing that can be used if necessary. In term of SQL server,
Backup is a copied database that can be used to restore the database
after a disaster occurs. The most popular SQL server database backups
are full, differential, transaction log, file, partial, copy-only &
database backup. SQL server database backups are essential to avoid
the data loss from database. SQL server database administrator can
restore the database from backups with the help of T-SQL command or
SQL server management studio (SSMS). In respect to the SQL database,
database backups are also prone to corruption and generates error
message in the event of restoring the database.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Why Restore Operation
Fails?</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
SQL server generates
restore error messages when backup media is corrupt. These error
messages either reported by the operating system or database
checksum.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>What to do </b></span><span lang="en-US"><b>now?</b></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
SQL server database
administrators have three options to overcome on above problems.</div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Cure the error
message & restart the restore operation.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Continue with the
error message.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Close the restore
operation & choose any other recovery mechanism.</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>1. Cure the error
message & restart the restore operation</b></span><span lang="en-US">:
Database administrator can be cured the error messages by following
ways.</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
If the error occurred
on the tape drive then clean or replace the tape drive.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
If error occurred due
to disk device then resolve the device error & replace the
damaged file.</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>2. Continue with
the Error Messages</b></span><span lang="en-US">: DBA can specify
continue_after_error option in restore statement to restore the
database. It will allow the restore operation with past error message
and roll forward occurs. Now DBA can apply transaction log backups to
bring the database online. Below is the restore syntax with
continue_after_error option.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #b3b3b3;">RESTORE
DATABASE database_name FROM backup_device WITH CONTINUE_AFTER_ERROR,
[ NORECOVERY ]</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
After the specifying
continue_after_error option, roll forward encounters the error
message then recovery cannot be completed and database is left
offline.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>How to Repair Offline
Database?</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
DBA can <a href="http://www.mssqldatabaserecovery.com/recover-corrupted-mssql-server-database.php" target="_blank">repair SQL database</a> with the help of DBCC CHECKDB. DBCC CHECKDB has three repair
options to repair the database, repair_rebuild, repair_fast &
repair_allow_data_loss. There is no any data loss in repair_rebuild &
repair_fast options but minimum amount of data may be lost in case of
repair_allow_data_loss option.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Note</b></span><span lang="en-US">:
To learn about minimum repair level needed, first run DBCC CHECKDB
with any repair option. It will suggest you the repair level and
re-run the DBCC CHECKDB with suggested repair level to repair the
database. </span>
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><br /></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>3.</b></span><span lang="en-US">
</span><span lang="en-US"><b>Close the restore operation & choose
any other recovery mechanism: </b></span><span lang="en-US">It is the
easiest method to resolve the above problem. If you have other
recovery disaster plan then it is the right time to use it.</span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-80118068517004872452013-02-25T22:06:00.001-08:002013-02-25T22:06:43.335-08:00Help Me! I am unable to open my SQL server database<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div lang="en-US" style="margin-bottom: 0cm;">
At times, when SQL server
database administrator tries to open the database, he gets below
error message on the screen.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
“<i><b>Database cannot
be opened due to inaccessible files or insufficient memory or disk
space. See the SQL Server error log for details</b></i>”</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1aKK-VGLJ5-BM-gRFiN8kkCNUD6H_th11ouuu_Yl79S6rDnvZH00hqBz-sXmfIi04YZm7di-S4USE_Ft5JjCzNClU1oNbTn3XoteKfFaXMw6AQjx7HEebsobPuhKIDHEd6Abf3dVG6oG9/s1600/database.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1aKK-VGLJ5-BM-gRFiN8kkCNUD6H_th11ouuu_Yl79S6rDnvZH00hqBz-sXmfIi04YZm7di-S4USE_Ft5JjCzNClU1oNbTn3XoteKfFaXMw6AQjx7HEebsobPuhKIDHEd6Abf3dVG6oG9/s1600/database.jpg" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Cause</b>: As the
result of above error message, database administrator is unable to
open the database. There are various reasons for the above error
message like less disk space, permission unavailability and many
more.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
It is one of the most
generated error message during mounting the SQL sever database.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>WorkAround</b>:
Database administrator can resolve above error message by following
steps:</div>
<ol type="a">
<li><div lang="en-US" style="margin-bottom: 0cm;">
Database
administrator needs to add more hard drive space and it can be done
by following methods:</div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Remove the
unnecessary files from the hard drive</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Add new hard drive.</div>
</li>
</ol>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Check the Autogrow
feature of the database, if it is ON then disable it.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Have you enough
permission to open the database? Check for the permission setting.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Check for .mdf &
.ldf files. If these files are mark as read only then database
administrator is unable to open the file.</div>
</li>
</ol>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-45034350964993725442013-02-25T00:42:00.000-08:002013-02-25T00:42:30.354-08:00Recommendations for Changing the SQL server Recovery Models <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">A database
administrator can change the recovery model of their database at any
time as per the requirement. If you have planned to change the
recovery model from the simple recovery model to the full recovery
model during the bulk operation then be aware that logging of bulk
operations will be change from minimal logging to full logging. Here
I will tell you some recommendations that are very necessary before
or after changing to Simple recovery model. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Recommendations
for changing from Simple to Full recovery model: </b></span><span lang="en-US">If
you are planning to change the recovery model for your database from
the simple recovery model to the full recovery model then take care
of the followings:</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNBK31tR13oSjeY3pv0zy3ooEXOfmGnFp1deqmqf8g2yVKtJW99EBzTNNh9Y6538plosr_wvIEQslodI6ySFmw841_y9NE3y7-UNGpBZuTiYoo3vVyqYid69oqprr08Y2Yzmi4yMDZ1qhH/s1600/simple+to+full.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNBK31tR13oSjeY3pv0zy3ooEXOfmGnFp1deqmqf8g2yVKtJW99EBzTNNh9Y6538plosr_wvIEQslodI6ySFmw841_y9NE3y7-UNGpBZuTiYoo3vVyqYid69oqprr08Y2Yzmi4yMDZ1qhH/s640/simple+to+full.png" width="640" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Take the full or
differential database backup of your database immediately after
changing the recovery model from the simple to the full recovery
model. The log chain will not start until the full or differential
database backup.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Take regular backup
of your transaction log and make a proper plan for backing up the
log backup according to your database need.</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
Note: If you don't backup
the transaction log on the regular interval then it can expand until
it runs out of your disk.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Recommendations
for changing from Full to Simple recovery model: </b></span><span lang="en-US">If
you are planning to change the recovery model for your database from
the full recovery model to the simple recovery model then please
attention for the following:</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPPoY3L5UrJTgE3EfSReaM50rgBJufff6SpE7AH5RVBUljwz4nPJlDi5j27_J1BKIyH5pWWuw7wo_zKGPWfdA4G_jSzAWcgKOicl9LWmit03AbJcvYCBYjxenvM-mXZxngGpGrQYondozM/s1600/full+to+simple.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPPoY3L5UrJTgE3EfSReaM50rgBJufff6SpE7AH5RVBUljwz4nPJlDi5j27_J1BKIyH5pWWuw7wo_zKGPWfdA4G_jSzAWcgKOicl9LWmit03AbJcvYCBYjxenvM-mXZxngGpGrQYondozM/s640/full+to+simple.png" width="640" /></a></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"></span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Changing the recovery
model from full to simple will break the log backup chain so make a
backup of your log backup before changing the recovery model. Log
backup will allow you to recover the database up to that point.
</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
After changing the
recovery model, you will need to take the data backup on regular
interval to avoid any data loss.</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to change the
recovery model</b></span><span lang="en-US">: After understanding the
impact of backup and restore strategy that will happen after the
changing the recovery model to simple, now you can change the
recovery model of your database with the help of SSMS. Perform below
steps to change the recovery model for your database:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Connect to the SQL
server instance and expand the server tree.</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Now expand the
database tree, select user database, or system database that suits
your requirement.</span></div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Press right
clicks</span><span lang="en-US"> on the database and go-to the
database properties.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now go-to the select
a page pane and click on the options.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now go-to the
recovery model section and see the current recovery model of your
database.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
To change the
recovery model, click on the arrow next to the recovery model
options and select your desired recovery model: simple, bulk-logged
or full.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL4ZEqfPVs4_gfi-FdTxGaBZSNIAr_DUn0f5cZLWdoJEwIzpaJxA-h4mCVM0G5mREtBfxER64X4Uiwum3ooph_Dpxx-h2BBQklF7hGX1p-uRNMg4jyyyl7gWIkT94ByRA69o_ClWvVro_w/s1600/recovery+model.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="317" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL4ZEqfPVs4_gfi-FdTxGaBZSNIAr_DUn0f5cZLWdoJEwIzpaJxA-h4mCVM0G5mREtBfxER64X4Uiwum3ooph_Dpxx-h2BBQklF7hGX1p-uRNMg4jyyyl7gWIkT94ByRA69o_ClWvVro_w/s320/recovery+model.gif" width="320" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
</li>
</ul>
<br />
<br />
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-15474462301611348812013-02-13T18:49:00.001-08:002013-02-13T18:49:37.918-08:00Choose the Best Method for SQL server Database Moving<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
At times, it may require to moving SQL
server data or log files from one to other location on the same SQL
server. There are two methods that help you in performing this task;
<b>Attach/Detach & ALTER Database</b>. Both methods perform the
same task but there are numerous reasons why prefer Alter Database
over Attach/detach method.
</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<b>Attach/Detach Method</b>: This
method is used to move SQL server data or log files from one location
to another location as mentioned above. Now have a look on the syntax
of both the operations.</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">EXEC
sp_detach_db 'your-database-name</span></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
After the successful detach of the
database from SQL server instance, you can move the data & log
file to a new location then after attach the files to new SQL server
instance by below syntax.</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">EXEC
sp_attach_db 'your-database-name</span></div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">'C:\Program
Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\your-database-name_Data.mdf',</span></div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">'C:\Program
Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\your-database-name_Log.LDF'</span></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
Microsoft’s online book topic
<b>sp_attach_db command</b> has mentioned the below warning message:</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
“<i>This feature will be removed in a
future version of Microsoft SQL Server. Avoid using this feature in
new development work, and plan to modify applications that currently
use this feature. We recommend that you use CREATE DATABASE
database_name FOR ATTACH instead. For more information, see CREATE
DATABASE (Transact-SQL).”</i></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<b>Alter Database Method</b>: You
cannot use this method without identifying the filenames associated
with the database. So first identify the filenames by below query:</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">Select
name, physical name from sys.master_files where database_id =
('your-database-name')</span></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
Now you will be able to use Alter
database with Modify command for moving SQL server database files
from one SQL server instance to another.</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">Aleter
Database your-database-name Modify File (Name =
your-database-name_Data, Filename =
'D:\SQLData\your-database-name_Data.mdf');</span></div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">Aleter
Database your-database-name Modify File (Name =
your-database-name_Log, Filename =
'D:\SQLData\your-database-name_Log.ldf');</span></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
Now set your database as offline to
complete the moving process by below syntax.</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">Alter
Database your-database-name Set Offline</span></div>
<div style="margin-bottom: 0cm;">
Move the files to a new location &
bring your database online by below syntax.</div>
<div style="margin-bottom: 0cm;">
<span style="background: #cccccc;">Alter
database your-database-name Set online </span>
</div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<table cellpadding="4" cellspacing="0" style="width: 643px;">
<colgroup><col width="312"></col>
<col width="312"></col>
</colgroup><tbody>
<tr valign="TOP">
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: none; border-top: 1px solid #000000; padding-bottom: 0.1cm; padding-left: 0.1cm; padding-right: 0cm; padding-top: 0.1cm;" width="312">
<div align="CENTER">
<b>Attach/Detach</b></div>
</td>
<td style="border: 1px solid #000000; padding: 0.1cm;" width="312">
<div align="CENTER">
<b>ALTER DATABASE</b></div>
</td>
</tr>
<tr valign="TOP">
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: none; border-top: none; padding-bottom: 0.1cm; padding-left: 0.1cm; padding-right: 0cm; padding-top: 0cm;" width="312">
It will disable the service broker on the database.<br />
</td>
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: none; padding-bottom: 0.1cm; padding-left: 0.1cm; padding-right: 0.1cm; padding-top: 0cm;" width="312">
It will not disable the service broker in the database.<br />
</td>
</tr>
<tr valign="TOP">
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: none; border-top: none; padding-bottom: 0.1cm; padding-left: 0.1cm; padding-right: 0cm; padding-top: 0cm;" width="312">
It will disable the Trustworthy setting of the database.<br />
</td>
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: none; padding-bottom: 0.1cm; padding-left: 0.1cm; padding-right: 0.1cm; padding-top: 0cm;" width="312">
It will not disable the Trustworthy setting for the database,
if enableed.<br />
</td>
</tr>
</tbody></table>
<div style="margin-bottom: 0cm;">
<br />
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-72926391297549260432013-02-07T02:41:00.000-08:002014-08-06T02:11:41.931-07:00Need to attach an mdf file without transaction log file in SQL server<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0cm;">
<span lang="en-US">If someone has
deleted your transaction log file </span><span lang="en-US">intentionally
and you have only .mdf file of your database then you can still
attach your mdf file with the database. No matter your database was
cleanly shutdown or not. In this article I will thoroughly guide you
methods & steps which are required to <a href="http://www.stellarinfo.com/sql-recovery.htm" rel="nofollow" target="_blank"><b>recover your database</b></a> when
you have only .mdf file of the database. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Database was
cleanly shutdown</b></span><span lang="en-US">: If your SQL server
database was cleanly shutdown then you can attach your database with
the help of SQL server management studio (SSMS). </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Let’s</span><span lang="en-US">
discuss all the steps in details:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<ul style="text-align: left;">
<li>Connect to the SQL
server instance using SSMS</li>
<li>From the Object
Explorer, right click the Databases node and a drop-down menu will
be appeared.</li>
<li>Now click on the
Attach tab.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsLN1xRLldUF6WV3tvAoRS_hhgzlNqCERq3hdvsi2qJ4zpgn6yXTwqMz_lElBGCvH-bKRWPscjDC-5jSE3ME1i0gv5izg4XLHiRV6zl2aXFV-b0iiNfRjjPrM69MKKvS2ZQ43RTAze0oMr/s1600/attach+database.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsLN1xRLldUF6WV3tvAoRS_hhgzlNqCERq3hdvsi2qJ4zpgn6yXTwqMz_lElBGCvH-bKRWPscjDC-5jSE3ME1i0gv5izg4XLHiRV6zl2aXFV-b0iiNfRjjPrM69MKKvS2ZQ43RTAze0oMr/s1600/attach+database.png" /></a></div>
<div>
<br /></div>
<ul style="text-align: left;">
<li>Click on the Add
button from appeared dialog box.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmTcTRa-EhHki7kYJMdYoSVrUMGyfEf5DW0XZgcwuctWyZqwZr-r_Y1Tshah6tgVfhtB0Ict6bLqgjH6590tXE3Wickw1DwhLt_KzgcHvz3JJh1Ol5pwu0iE4Ts2wwJGyL1Grz1uflPHBL/s1600/attach+dialog+box.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmTcTRa-EhHki7kYJMdYoSVrUMGyfEf5DW0XZgcwuctWyZqwZr-r_Y1Tshah6tgVfhtB0Ict6bLqgjH6590tXE3Wickw1DwhLt_KzgcHvz3JJh1Ol5pwu0iE4Ts2wwJGyL1Grz1uflPHBL/s320/attach+dialog+box.png" height="287" width="320" /></a></div>
<div>
<br /></div>
<ul style="text-align: left;">
<li>Now a Locate Database
Files dialog box will be appeared.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8vQb0xn770J2nzUQXxXT68cQOEagOxodRN45cR4eQxAVCNK9UcJjay7GTGLJSX9V1Juqp58SZRuVatADMyXfqKdIPQEBbS3xhGTGC6W2AFxvM0aTvOmx2lKxRVyKaIZUXuKkwkhN_gFru/s1600/locate+database+file.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8vQb0xn770J2nzUQXxXT68cQOEagOxodRN45cR4eQxAVCNK9UcJjay7GTGLJSX9V1Juqp58SZRuVatADMyXfqKdIPQEBbS3xhGTGC6W2AFxvM0aTvOmx2lKxRVyKaIZUXuKkwkhN_gFru/s320/locate+database+file.PNG" height="210" width="320" /></a></div>
<div>
<br /></div>
<ul style="text-align: left;">
<li>Click on the
browse button to select your database MDF file after choosing .mdf
file click on the OK button.</li>
<li>Now you will see your
mdf file is selected but SQL server is unable to select your
transaction log file because transaction log file was deleted. To
attach .mdf file without transaction log file; select the log file
and click on the remove button to remove it. Now click on the OK
button to attach the database file. SQL server will create a
transaction log file for you while attaching the database.</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Database was not
cleanly shutdown</b></span><span lang="en-US">: Try below steps if
your database was not cleanly shutdown.</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Create a same size
database</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now shutdown the SQL
server</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Change the old mdf
file with new one</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Start the SQL server,
your database may go in suspect mode</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now change your
database status from suspect mode to emergency mode by ALTER
Database command</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Run DBCC CHECKDB with
REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Note</b></span><span lang="en-US">:
You may loss some amount of data but your database will be available
again.</span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com2tag:blogger.com,1999:blog-5829445392074712152.post-47327669001157540522013-01-23T02:27:00.001-08:002013-02-07T22:06:07.620-08:00Importance of DBCC CHECKDB Command<div dir="ltr" style="text-align: left;" trbidi="on">
<style type="text/css">p { margin-bottom: 0.21cm; }</style>
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">Quite often, it</span><span lang="en-US">
has been seen that some of the DBAs do not run DBCC CHECKDB command
on his/her database. I don't understand why DBAs not perform this on
their database regularly. It is very simple to run, and it saves you
from big trouble later on. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>What is DBCC
CHECKDB Command?</b></span><span lang="en-US"><b> </b></span>
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">This command checks
logical & physical integrity of all the objects in the desired
database. It includes all three command checks (DBCC CHECKALLOC, DBCC
CHECKTABLE & DBCC CHECLCATALOG) in itself. If DBA has </span><span lang="en-US">run
DBCC CHECKDB command on the database then he/she should not three
command on the database as DBCC CHECKDB includes all these commands.
If corruption has occurred in the database due to some reason, DBCC
CHECKDB command will check it and report you about which object have
corrupted. It has three optional arguments that help you to fix the
reported corruption, and the arguments are</span></div>
<ol>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Repair_Rebuild</b></span><span lang="en-US">:
When you use this argument with DBCC CHECKDB, there is no
possibility of data loss. It will try to repair & rebuild
corrupt database without data loss.</span></div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Repair_Fast</b></span><span lang="en-US">:
It does not perform any repair operation on the specified database
but maintains the syntax for backward compatibility.</span></div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Repair_Allow_Data_Loss</b></span><span lang="en-US">:
As name suggests, it will try to repair corrupt database with
minimum data loss. </span>
</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Note:</b></span><span lang="en-US">
Before running above repair arguments, make sure your database is in
single-user mode. If database is in multi-user mode then ALTER it,
and set it in single-user mode.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>How do you use DBCC
CHECKDB?</b></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">You can use it on
your database</span><span lang="en-US"> by performing T-SQL</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: none repeat scroll 0% 0% rgb(204, 204, 204);">DBCC
CHECKDB AdventureWorks </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: none repeat scroll 0% 0% rgb(204, 204, 204);">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Replace AdventureWorks
with the name of your database.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>What is the best time
interval to Run DBCC CHECKDB?</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
It depends upon your
database importance, use, and so many other factors also. Running
DBCC CHECKDB on the database regularly will not avoid the corruption
to happen but it will help in determining the corruption as soon as
it happens.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to c</b></span><span lang="en-US"><b>heck
last DBCC CHECKDB?</b></span><span lang="en-US"> </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
If you want to know, when
had the last DBCC CHECKDB command performed on the database then run
bellow command</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: none repeat scroll 0% 0% rgb(204, 204, 204);">DBCC
DBINFO AdventureWorks WITH TABLERESULTS </span>
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-42576052084565866832012-12-20T20:28:00.000-08:002013-02-07T22:06:27.318-08:00SQL Statements Overview – DDL & DML<div dir="ltr" style="text-align: left;" trbidi="on">
SQL statements are
categorized into two categories: DDL statements & DML statements.
DDL is abbreviation for data definition language while DML is
abbreviation for data manipulation language. Data definition language
statement is used to define the data structure of database, in
addition to this; it can modify the structure of the table or other
objects like view, stored procedure and many more. Data manipulation
language statement is used to retrieve data or work with data in SQL
server. Both of these categories contain several statements than I
have present in this article.<br />
<br />
<div style="margin-bottom: 0cm;">
<span style="font-size: medium;"><span lang="en-US"><b>Data
Definition Language (DDL)</b></span></span><span lang="en-US">:</span><span lang="en-US"><b>
</b></span><span lang="en-US">You can use this statement for create,
drop, truncate, and alter data structure in an instance of SQL server
database. Some of the popular DDL statements are described below.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Create Statement:</b></span><span lang="en-US">
This statement is used for creating a new entity; like table, view,
database, index, and many more. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">CREATE
TABLE <table name> ( <attribute name 1> <data type 1>,
...<attribute name n> <data type n>);</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Alter Statement</b></span><span lang="en-US">:
This statement is used for modifying the definition of a an entity;
like table, view, database, index, and many more. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">ALTER
TABLE <table name> ADD CONSTRAINT <constraint name>
PRIMARY KEY (<attribute list>);</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Drop Statement</b></span><span lang="en-US">:
You can use this statement to remove an existing entity; like table,
view, database, index, and many more. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">DROP
TABLE <table name>;</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Truncate
Statement:</b></span><span lang="en-US"> This statement removes all
the rows from the table. It is similar to the DELETE statement with
'Where' clause.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">TRUNCATE
TABLE <table name>;</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Note: In all above
examples; I have taken table as an entity.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: medium;"><span lang="en-US"><b>Data
Manipulation Language (DML)</b></span></span><span lang="en-US">:
Basically this statement is used to work with data in the SQL server
database. Some of the popular DML statements are as follows:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Insert Statement</b></span><span lang="en-US">:
As the name suggests; it is used to insert one or more rows in the
table.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">INSERT
INTO <table name> VALUES (<value 1>, ... <value n>);
</span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Update statement</b></span><span lang="en-US">:
You can use this statement to change the data from in a table.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">UPDATE
<table name> SET <attribute> = <expression> WHERE
<condition>;</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Delete Statement</b></span><span lang="en-US">:
As I have discussed above; it is similar to the truncate statement
but with 'Where' clause. It removes one or more rows from the table.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #b3b3b3;">DELETE
FROM <table name> WHERE <condition>;</span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-5829445392074712152.post-60710984316832566662012-12-12T02:35:00.000-08:002013-02-07T22:06:25.314-08:00Oh My God! SQL Server Backup is Corrupt too<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US">It
is a dark secret that once in a while every database administrator
face SQL server backup corruption issue. Some of them talk on this
topic publicly & some does not but </span></span><span style="font-size: small;"><span lang="en-US">it
is true. I have seen in forums & newsletters where; people talk
freely not on SQL data file corruption only but SQL backup file
corruption too. </span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="font-size: small;">Here
is a statement given by <b>Paul Randlal</b>, who is the CEO of
SQLskills.com.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
“<span style="color: #0070c0;"><span style="font-size: small;"><span lang="en-US"><b>You
don't have a backup until you have done a restore</b></span></span></span><span style="font-size: small;"><span lang="en-US">”
</span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US"><b>Meaning</b></span></span><span style="font-size: small;"><span lang="en-US">:
The meaning of statement is that if you have SQL server database
backup that is corrupt due to any reason, you can’t restore it over
original database but you can check the validity of the backup that
is going to help you in disaster recovery.</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US"><b>Check
integrity of database before backup</b></span></span><span style="font-size: small;"><span lang="en-US">:
It is a very simple & important talk of SQL server DBA. You can
do this by running </span></span><span style="font-size: small;"><span lang="en-US"><b>DBCC
consistency checks</b></span></span><span style="font-size: small;"><span lang="en-US">
before taking a full backup of your database otherwise you may be
backing up a corrupt database.</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US"><b>Check
the Integrity of Backup itself</b></span></span><span style="font-size: small;"><span lang="en-US">:
You can do this by using '</span></span><span style="font-size: small;"><span lang="en-US"><b>WITH
CHECKSUM</b></span></span><span style="font-size: small;"><span lang="en-US">'
option along with backup command. Backup command with Checksum option
checks the checksum for whole backup steam and save it into the
backup. It ensures you that your database backup is not corrupt.</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="font-size: small;">So
there are a lot of things that you can do to minimize the change of
backup corruption. Now the main point comes in picture, if you don't
have followed any of the above suggestions before making a backup of
your database and backup is corrupt.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US"><b>How
to Restore Database?</b></span></span><span style="font-size: small;"><span lang="en-US">
Many people are got frustrated or tensed when they face this
situation but I will suggest you keep your mind cool. You can do one
thing, allow restore to ignore error. There is an option in restore
that is known as </span></span><span style="font-size: small;"><span lang="en-US"><b>continue-after-error</b></span></span><span style="font-size: small;"><span lang="en-US">.
It enables you to restore the backup even if corruption is found in
the backup stream. After this; your database may go inconsistent
state. You can use </span></span><span style="font-size: small;"><span lang="en-US"><b><a href="http://msdn.microsoft.com/en-us/library/ms176064.aspx" target="_blank">DBCCCheckDB command</a></b></span></span><span style="font-size: small;"><span lang="en-US">
to repair SQL database. DBCC CheckDB offers three repair options:
repair_fast, repair_rebuild, and repair_allow_data_loss. However, if
you are still not able to restore your database then it is the best
time to use any 3</span></span><sup><span style="font-size: small;"><span lang="en-US">rd</span></span></sup><span style="font-size: small;"><span lang="en-US">
party backup repair program. </span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="font-size: small;"><span lang="en-US"><b>Summary</b></span></span><span style="font-size: small;"><span lang="en-US">:
Make sure you have validated the database before backup & backup
after you have finished with backup. Also, try to make more than one
backup if first backup got corrupted then you will have another
option for restore & recovery. </span></span>
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0