tag:blogger.com,1999:blog-1879983222058846962024-03-14T01:02:36.884-07:00SQL Server Recovery BlogMore About SQL server recovery Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.comBlogger101125tag:blogger.com,1999:blog-187998322205884696.post-11408178413076524392014-05-07T02:55:00.000-07:002016-01-29T03:06:15.467-08:00SQL Server Transaction Log Corruption & Recovery<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">In the field o</span><span lang="en-US">f</span><span lang="en-US">
database, a transaction log is defined as a record of actions.
</span><span lang="en-US">Transaction log</span><span lang="en-US">
records all the actions performed </span><span lang="en-US">i</span><span lang="en-US">n
the database. </span><span lang="en-US">It </span><span lang="en-US">is
an important component of any database because it helps in recovery
of database during disaster strike to the database. In SQL server,
Every database has a transaction log that list all the changes done
in the </span><span lang="en-US">SQL server </span><span lang="en-US">database.
</span>
</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">For smooth
functioning of your SQL server database, you need to monitor and
backup transaction log of your database periodically. Frequency of
taking log backups depends upon your business requirement. Specially
how much time you can afford your business down. It might be 30
minutes to daily. </span><span lang="en-US">But I would suggest that
a</span><span lang="en-US"> frequent transaction log backup minimize
the chance of data loss. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Sometime back I
faced transaction log backup issue. Backup job was failing
</span><span lang="en-US">continuously and through below error
message:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">201</span><span lang="en-US">4-04-28
14:22:15.48 Backup Error: 3041, Severity: 16, State: 1.<br />2014-04-28
14:22:15.48 Backup BACKUP failed to complete the command BACKUP
DATABASE willium. Check error log for detailed messages.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Above error message
clearly indicates that backup was failing on the willium database. I
checked errorlog for detailed information and got below error
message:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">201</span><span lang="en-US">4-04-28
14:22:15.48 </span><em><span lang="en-US"><span style="font-style: normal;">Backup
detected log corruption in database willium</span></span></em><span lang="en-US">.
Context is FirstSector.LogFile: 34
‘K:\Mount12\willium_Log\willium_Log.ldf’ </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
VLF
SeqNo:x18e44eVLFBase:x374000000LogBlockOffset:x3777c4200SectorStatus:2
LogBlock.StartLsn.SeqNo: x3020001LogBlock.StartLsn</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
As above error messag
indicates log file of willium database had been corrupted.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Solution:</b> I had performed
below steps to remove corruption from transaction log file of willium
database.</div>
<span lang="en-US"></span><br />
<ol style="text-align: left;"><span lang="en-US">
<li><span lang="en-US">First of all, I
</span><span style="color: navy;"><span lang="zxx"><u><a href="http://sql-server-recovery.blogspot.in/2012/02/two-ways-to-change-recovery-model.html">changed
the recovery model</a></u></span></span><span lang="en-US"> of
willium database from Full to Simple. You can do this by Alter
command. </span><span lang="en-US"><b>Alter database <willium>
set Recovery Simple.</willium></b></span></li>
<li><span lang="en-US">Then
after </span><span style="color: navy;"><span lang="zxx"><u><a href="http://sql-server-recovery.blogspot.in/2011/11/shrink-transaction-log-file-of-sql.html">shrinked</a></u></span></span><span lang="en-US">
the willium database log file.</span></li>
<li>Take
a full backup of database and it was accomplished without any error
message.</li>
<li>And
finally changed the recovery model of willium database from Simple
to Full.</li>
<li>Again
take full backup of database and it was successfully accomplished. </li>
</span></ol>
<span lang="en-US">
</span></div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com2tag:blogger.com,1999:blog-187998322205884696.post-91661922133756402832014-03-25T01:57:00.000-07:002014-03-25T01:57:16.600-07:00Resolving SQL server Error Message 5123: Cannot attach DB<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">To move a SQL
database from one location to another location; first you need to
detach the database from server and then re-attach it on another
server. SQL server offers two methods for detaching a database: using
SQL server management studio (SSMS) or using Transact SQL (T-SQL).
And the detached database can be moved to another server and attached
over new server.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Sometimes you may got
following error message while attaching a detached database on
another server
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<i><b>SQL Server -Error
5123: Cannot attach DB.</b></i></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Cause</b></span><span lang="en-US">:
It is a very common error message that has been seen by most of the
database administrator during attaching a database. Above error
message generally occurs when attaching a database on the different
location. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Resolution</b></span><span lang="en-US">:
Please check any of the following solutions that can fix your
problem:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Verify the Name
of database</b></span><span lang="en-US">: It might be possible that
you have misspelled the name of database or trying to attach the
database from different location. Please verify the name and path of
database and try again.</span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVKPb6lNsvTpR5VguubFrB23-YlmbMXnu6uozvyhmxkvZKis7pU_tAs7zSNQXfY-kaQfXLTJ4qUZPoC7rqMifPhu1SVTVBn-zMViGOrKk_jcIBTtuU-HAaUarvq8hZNJzIYfnGqLn6R3w/s1600/verify+the+name.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVKPb6lNsvTpR5VguubFrB23-YlmbMXnu6uozvyhmxkvZKis7pU_tAs7zSNQXfY-kaQfXLTJ4qUZPoC7rqMifPhu1SVTVBn-zMViGOrKk_jcIBTtuU-HAaUarvq8hZNJzIYfnGqLn6R3w/s1600/verify+the+name.jpg" height="79" width="320" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Rename the
database file</b></span><span lang="en-US">: Just rename the database
file name and try to reattach the database from newly named database.</span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgczrwdIoUOd7s1Y2fzkZgFw76cvvl3YCBOqx-VPn4RjfZ_V7lc9vw_wQxVUZgb3BJVGWuhLzAT96nwYHDNwIl33YoawHY9BCHmjqNyvwR_FwwVLYC0xZlB5VYtPFjFc9rtcGM_Jx0MLcc/s1600/rename+the+database.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgczrwdIoUOd7s1Y2fzkZgFw76cvvl3YCBOqx-VPn4RjfZ_V7lc9vw_wQxVUZgb3BJVGWuhLzAT96nwYHDNwIl33YoawHY9BCHmjqNyvwR_FwwVLYC0xZlB5VYtPFjFc9rtcGM_Jx0MLcc/s1600/rename+the+database.png" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Disable the
Anti-virus Software</b></span><span lang="en-US">: If you have
installed any anti-virus software on your PC then disable it and
re-try to attach your database. It has been seen that sometimes
anti-virus software locks the database.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Check the Backup
Software</b></span><span lang="en-US">: Check the Backup software. It
also locks the database.</span></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlfI2nRCgEv4VTOEZqc5caKr7W09iGzLlisy-OA-pd1kr0U3y08iNNvqZE9ZTj7WBABOVcheKmu6WOOarj6AyF4oX0YecxFctCr9uBG4E1qMxhzXOgT2rFjMtl3ofThUZPBrZaqfjQtk4/s1600/check+backup+software.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlfI2nRCgEv4VTOEZqc5caKr7W09iGzLlisy-OA-pd1kr0U3y08iNNvqZE9ZTj7WBABOVcheKmu6WOOarj6AyF4oX0YecxFctCr9uBG4E1qMxhzXOgT2rFjMtl3ofThUZPBrZaqfjQtk4/s1600/check+backup+software.gif" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Allow NTFS
permission security</b></span><span lang="en-US">: It might be
possible that you have not given NTFS security permission to
everyone. So first all the permission for every one and try again.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Restart the SQL
server services</b></span><span lang="en-US">: First close all the
services of SQL server and start the SQL server again. Most of the
experts DBAs recommend restarting the SQL services after your defined
working hours.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuYGsreG5kcFsHYCFphgx5aH5_gKPlwradO61LPN2GImT72iJJ2BysjucpD6N3dwc-QkwkxCc66vC3-0ki7sI8Z7Q1HI0AqFNikKErUqDiEwwgCWb14iiWCHnSrwegLVc4z72Va7MjKJc/s1600/restart.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuYGsreG5kcFsHYCFphgx5aH5_gKPlwradO61LPN2GImT72iJJ2BysjucpD6N3dwc-QkwkxCc66vC3-0ki7sI8Z7Q1HI0AqFNikKErUqDiEwwgCWb14iiWCHnSrwegLVc4z72Va7MjKJc/s1600/restart.jpg" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Use SP_Attach_DB
Command</b></span><span lang="en-US">: It is another method to attach
a database. If you had tried GUI method to attach the database then
try sp_attach_db command. Open a new query and use sp_attach_db
command. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Change the
Location of database file</b></span><span lang="en-US">: Move the
database to a new location (sub directory, root directory etc.) and
try to re-attach the database from there.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Each of the above
mentioned possible solutions can do the trick. Please try
re-attaching the DB after each possible solution.</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-91669518297625659002013-03-27T21:28:00.000-07:002013-11-17T19:00:01.364-08:00Recover Lost Data using Database snapshot in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">Database</span><span lang="en-US">
snapshot was firstly introduced in SQL server 2005 enterprise
edition. It is a static view of SQL server database (source database)
and read-only in nature. It resides on the same instance as its
source database. When a source database is updated then its snapshots
database are also updated. Database administrator can create database
snapshot for all source database irrespective of their recovery
model. It can be used for reporting purpose and quick <a href="http://www.mssqldatabaserecovery.com/" rel="nofollow" target="_blank">recovery of SQL</a> data. Here, you will see how to create and use database snapshot
features.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>How to Create a Source
Database?</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Database administrator can
create a source database by executing following T-SQL command:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">Use master</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">CREATE DATABASE
Name-Source-Database</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO
</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;"># Create a table in the
source database</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">Create Table-Name in
Name-Source-Database</span></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">
Replace the Name-Source-Database & Table-Name with the name of
your source database & table name.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to Create </b></span><span lang="en-US"><b>a
Database Snapshot?</b></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
After the creating of
source database, now database administrator can create the database
snapshot for its source database by executing following T-SQL
command:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">Use master</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">CREATE DATABASE
Name-Source-Database_Snapshot</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">ON</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">(</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">NAME =
'Name-Source-Database',</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">FILENAME = 'D:Program
FilesMicrosoft SQL
ServerMSSQL10.SQL2008MSSQLDATAName-Source-Database_Snapshot.SS')</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">AS</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">SNAPSHOT OF
Name-Source-Database</span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US" style="background-color: #cccccc;">GO </span></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">:
Database administrator can't create database snapshot with the help
of SQL server management studio (SSMS).</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>How to Recover?</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
If database administrator
has lost data from the source database due to any reasons then
deleted data can be recovered by two methods: Full Backup &
Database snapshot. Full backup is out of scope of this article so
here you will see how to recover data by database snapshot.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Recovery with Database
Snapshot</b></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Database snapshot's Insert
into...Select options helps in recovery of deleted record from the
table.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Database administrator can
restore source database with the help of database snapshot. To
restore the database, execute the following T-SQL command:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">USE master</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">RESTORE DATABASE
Name-Source-Database</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">FROM DATABASE_SNAPSHOT
='Name-Source-Database_Snapshot'</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO
</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;"><br />
</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">Now use Insert
into...Select option to recover the lost data.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">INSERT INTO
Name-Source-Database.dbo.record
</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">SELECT * FROM
Name-Source-Database_Snapshot.dbo.record</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Advantages</b></span><span lang="en-US"><b>
of Database Snapshot:</b></span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
It helps in reporting
purpose.</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">It safeguards
the database against user & administrator error.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
It is used for
maintaining the historical data.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
It helps in creating
multiple database snapshots of a given source database.</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Flexibility to
create database snapshot for all source database irrespective of
their sizes. </span>
</div>
</li>
</ul>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-52711962462664862252013-03-18T01:44:00.000-07:002013-11-17T19:05:05.158-08:00How to Create a SQL server database with .MDF file only?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
Are you new in SQL server and trying to
create a new database for test or study purpose? Then you can do this
with the help of mdf file only. MDF file is the primary database file
of the SQL server. It stores all the user data & files. MS SQL
server offers sample databases for the test purpose which available
at <span style="color: navy;"><span lang="zxx"><u><a href="http://msftdbprodsamples.codeplex.com/" rel="nofollow">MicrosoftSQL Server Product Samples &Database</a></u></span></span>
website.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
For your referrence I have downloaded
the <span style="color: navy;"><span lang="zxx"><u><a href="http://msftdbprodsamples.codeplex.com/releases/view/59211" rel="nofollow">AdventureWorks2008R2database</a></u></span></span> from the codeplex website. This
database has mdf file only.
</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Attach the database</b></div>
<div style="margin-bottom: 0cm;">
You can attach the
mdf file without ldf file by two methods:</div>
<ol>
<li><div style="margin-bottom: 0cm;">
Through SQL
command</div>
</li>
<li><div style="margin-bottom: 0cm;">
Through SQL
server management studio</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
I
will show you both the methods; firstly <b>through SQL
command</b>.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
Run below SQL
command to attach the mdf file:</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="color: blue;">CREATE
DATABASE</span>
YourDatabaseName <span style="color: blue;">ON</span></div>
<div style="margin-bottom: 0cm;">
( FILENAME =
N'C:\DATA\YourDatabaseName.mdf')</div>
<div style="margin-bottom: 0cm;">
<span style="color: blue;">FOR</span>
ATTACH_REBUILD LOG;</div>
<div style="margin-bottom: 0cm;">
GO</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
Above command will
attempt to attach the database file and create an empty log file
while attaching the database.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Note</b>:
Create a backup of your database to minimize the unavailability time
of the database. As you are new in the SQL server so it may possible
that you will perform a wrong command that makes your database
unavailable.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Attach through SQL Server Management
Studio</b>: Perform below steps to
attach a mdf file without ldf file using SQL server management
studio.</div>
<ol>
<li><div style="margin-bottom: 0cm;">
Connect to
the SQL server instance using SSMS.</div>
</li>
<li><div style="margin-bottom: 0cm;">
From the
Object Explorer, right click the Databases node and a drop-down menu
will be appeared.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Now click on
the Attach tab.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Click on the
Add button from appeared dialog box.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Now a Locate
Database Files dialog box will be appeared.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Click on the
browse button to select your database MDF file after choosing .mdf
file click on the OK button.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Now you will
see your mdf file is selected but SQL server is unable to select
your transaction log file because transaction log file is missing.
</div>
</li>
<li><div style="margin-bottom: 0cm;">
To attach
.mdf file without transaction log file; select the log file and
click on the remove button to remove it.
</div>
</li>
<li><div style="margin-bottom: 0cm;">
Now click on
the OK button to attach the database file.
</div>
</li>
<li><div style="margin-bottom: 0cm;">
SQL server
will create a transaction log file for you while attaching the
database.</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<b>Suggestion</b>:
Make a habit to run DBCC CHECKDB command on your database. It will
check and report the error message. If it reports any error message
then re-run it with proper repair clause to repair the database.</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-4466915609688198662013-02-07T02:32:00.000-08:002013-11-15T03:03:53.528-08:00Reason & Resolution steps for SQL server error 233<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div lang="en-US" style="margin-bottom: 0cm;">
Are you getting below
error message while connecting to the Microsoft SQL server?</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/AVvXsEh3KLa5Gipp22m6_yPRek6j2r0HrkjB3z6tZwnKzllHYw1Z2symad5z9UuVn8XWp_4TaZjJ1w5lM-RjqjB9Wv2o20ETZSvjMNL8FM9K-4K8Tk-SR9oMOqqSkaZONZ1biErPLZV7y2YnxtY/s1600/2.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="148" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3KLa5Gipp22m6_yPRek6j2r0HrkjB3z6tZwnKzllHYw1Z2symad5z9UuVn8XWp_4TaZjJ1w5lM-RjqjB9Wv2o20ETZSvjMNL8FM9K-4K8Tk-SR9oMOqqSkaZONZ1biErPLZV7y2YnxtY/s640/2.jpg" width="640" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">If '</span><span lang="en-US"><b>Yes</b></span><span lang="en-US">'
then don't worry!! You have reached at the right place.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
After getting any error
message; first of all check the error log for error message details
recommended by experts. When you check error log; you may got given
below details:</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/AVvXsEi6F9Re9wc86VGFMATUTh5bBBF7eu65uxnbYxQrHzubUD8MhZ10qtB__M6TABMANdn91QTO88iwO3n_X-sW9lrzNyvSiMV39GCOY4pb77FuDJLRkZ0ji4WVqoIixVpKyqobmkehIlV1AQ4/s1600/2.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6F9Re9wc86VGFMATUTh5bBBF7eu65uxnbYxQrHzubUD8MhZ10qtB__M6TABMANdn91QTO88iwO3n_X-sW9lrzNyvSiMV39GCOY4pb77FuDJLRkZ0ji4WVqoIixVpKyqobmkehIlV1AQ4/s640/2.jpg" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Cause</b></span><span lang="en-US">:
As per the above detail is explaining the maximum number of user
connection is 1 and that is already reached the maximum value so you
can't login to the MS SQL server. If you are system administrator of
the database then you can increase the maximum value by using stored
procedure (sp_configure) or connect with your system administrator if
you can't increase the maximum value.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to Increase
Maximum Value</b></span><span lang="en-US">: Follow below steps to
increase the maximum value using sp_configure stored procedure:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Open the SQL server
management studio (SSMS).</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now open a new query
editor.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Write down below
query in the query editor.</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">sp_configure
'show advanced options', 1;</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">Go</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">reconfigure</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">Go</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">sp_configure
'user connections', 0</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">Go</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">reconfigure</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background: #808080;">Go</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now press the execute
button to execute the query.</div>
</li>
</ul>
<br />
<br /></div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-955798959764500982013-01-27T18:53:00.003-08:002013-11-17T19:11:25.880-08:00Is your SQL server database backup OK? Check it now<div dir="ltr" style="text-align: left;" trbidi="on">
<span lang="en-US">A data can be any
thing like employee attendance record, student marks detail,
passenger list, customer list, booking history, and so on. Normally
data backup is taken by database administrator but if you are working
for any organization then it’s your responsibility to maintain the
backup of your data which is a very crucial task for protecting data.
Sometimes making a <a href="http://sql-server-recovery.blogspot.in/2013/01/unable-to-restore-sql-database-backup.html" rel="nofollow" target="_blank">backup</a> of your database is not enough for
protecting data because backup can get corrupt too. So checking the
integrity of your database backup is also essential. The same concept
is applied in SQL server. SQL server backup helps in recovery of
corrupt or deleted database.</span><br />
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>How to Check your
Backup?</b></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">If you have made
backup of your SQL server database and did not checked </span><span lang="en-US">its
integrity yet then I will recommend you to check it now by below
syntax:</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(192, 192, 192);">RESTORE
VERIFYONLY FROM DISK = '<location backup="" database="" of="" your="">'</location></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
To illustrate it, I have
made a backup of my database. I have made another copy of my backup &
corrupted it intentionally.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Name of my perfect backup
is perfectdatabasebackup.bak.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Name of corrupt database
backup is corruptdatabasebackup.bak</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
First I will run RESTORE
VERIFYONLY syntax on the perfect database backup and the syntax is
given below:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);">RESTORE
VERIFYONLY FROM DISK = 'C:\Database</span></span><span lang="en-US"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);"> Backup\perfectdatabasebackup.bak'</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Output</b></span><span lang="en-US">:
</span><span lang="en-US">The backup set on file 1 is valid</span><span lang="en-US">.</span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">As the output is
indicating that my backup is </span><span lang="en-US"><b>OK</b></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Now I will run RESTORE
VERIFYONLY syntax on the corrupt database backup and the syntax is
given below:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);">RESTORE
VERIFYONLY FROM DISK = </span></span><span style="background-color: silver;">'C:\Database</span><span lang="en-US"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);"> Backup\corruptdatabasebackup.bak'</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Output</b></span><span lang="en-US">:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;">Msg
3242, Level 16, State 2, Line 1</span></div>
<div style="margin-bottom: 0cm;">
<span style="color: red;"><span lang="en-US">The
file on device 'C:\Database </span></span><span style="color: red;"><span lang="en-US">Backups\corruptdatabasebackup.bak' is not a valid Microsoft Tape
Format backup set.</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;">Msg
3013, Level 16, State 1, Line 1</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;">VERIFY
DATABASE is terminating abnormally.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
As the output is
indicating that backup is not valid.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
If you are trying to
restore your database from backup and unable to do that then use
RESTORE VERIFYONLY command on the database backup to verify it.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Conclusion</b></span><span lang="en-US">:
It is recommended to run </span><span lang="en-US"><b>RESTORE VERIFY</b></span><span lang="en-US">
command on SQL database backup after every successful backup. </span>
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-17996453194873256692013-01-15T01:33:00.001-08:002013-11-17T19:12:10.129-08:00Unable to Restore SQL Database Backup: error 3205<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">When
you try to restore a </span><span lang="en-US">SQL
server database backup created on SQL server 2005 instance to a
different SQL server instance using SQL server management studio
(SSMS), you may get following error message on your computer screen:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
“<span lang="en-US">An
exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Additional
information:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Too
many backup devices specified for backup or restore; only 64 are
allowed.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
RESTORE
HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error:
3205)”</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/AVvXsEghMIN2f_4FlgFNYOniIwxMLj_lGNtaxHHw8bJlHpcYpVYSQXCa6T9rcPEM6ft1Hmxdak34NBRsPW2Tl4PTxz1i-eheQQSdTp7zzlJ5PmfmFLFGf1VSQf_AGzKbVCInF84xZiD2PmaFnWQ/s1600/error+3205.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="116" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghMIN2f_4FlgFNYOniIwxMLj_lGNtaxHHw8bJlHpcYpVYSQXCa6T9rcPEM6ft1Hmxdak34NBRsPW2Tl4PTxz1i-eheQQSdTp7zzlJ5PmfmFLFGf1VSQf_AGzKbVCInF84xZiD2PmaFnWQ/s400/error+3205.png" width="400" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Cause</b></span><span lang="en-US">:
Above error message is appearing because you are trying to restore a
SQL server backup created using SQL server 2005 into SQL Server 2000
instance. </span>
</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">:
Microsoft suggests that backup created using higher instance of SQL
server can't be restored into the earlier instance of SQL server.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
For
simple understanding:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Restore
a backup from 2005 ---> 2000 - </span><span style="color: red;"><span lang="en-US">Not
allowed</span></span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Restore
a backup from 2008 ---> 2005, 2000 - </span><span style="color: red;"><span lang="en-US">Not
allowed</span></span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Restore
a backup from 2000 ---> 2005 - </span><span style="color: green;"><span lang="en-US">Allowed</span></span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Restore
a backup from 2000, 2005 ---> 2008 - </span><span style="color: green;"><span lang="en-US">Allowed</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Solution</b></span><span lang="en-US">:
To solve this problem, follow below steps:</span></div>
<ol>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">First
of all run '</span><span lang="en-US"><b>SELECT @@version</b></span><span lang="en-US">'
command on the target SQL server instance. </span>
</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Verify
the </span><span style="color: navy;"><span lang="zxx"><u><a href="http://sql-server-recovery.blogspot.in/2011/04/how-to-find-version-level-and-edition.html" rel="nofollow">version of target SQL server</a></u></span></span><span lang="en-US">
instance.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Upgrade
the target SQL server.</div>
</li>
</ol>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-48968200218614414022012-12-27T02:27:00.000-08:002013-11-17T19:12:32.686-08:00What is the Recovery Model for Newly Created Database?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div lang="en-US" style="margin-bottom: 0cm;">
SQL server offers a
facility to all database administrator for managing transaction log
that is known as <b>R</b><b>ecovery model</b>. It will decide that
you are going to manage your transaction log or not. There are three
types of recovery model in SQL server: <b>S</b><b>imple, </b><b>F</b><b>ull,
& </b><b>B</b><b>ulk</b><b>-L</b><b>ogged</b>. If you have chosen
simple recovery model for your database then you will be unable to
make transaction log backup but you can make backup of transaction
log in full & bulk-logged recovery model.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Recovery Model for
Newly Created Database</b>: Default recovery model of SQL server
database is same like as recovery model of Model database. Model
Database recovery model depends upon the edition of SQL server. If
your model database is in 'Full' recovery model then your newly
created database will also in 'Full' recovery model. To verify this
lets create a database.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Simple create statement
for creating a database:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">CREATE
DATABASE [New Test Database]</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">GO</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
# 'New Test Database' is
the name of database.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Now run a query to select
recovery model for 'model' & 'new test database'.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">SELECT
Name AS 'Databasename', recovery_model_desc </span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">FROM
sys.databases </span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">WHERE
name = 'Model' OR Name = 'New Test Database' </span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;">GO
</span></span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: red;"><span style="background: #cccccc;"><br /></span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Output:</b></div>
<table cellpadding="4" cellspacing="0" style="width: 643px;">
<colgroup><col width="205"></col>
<col width="206"></col>
<col width="206"></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="205"><div align="CENTER" lang="en-US">
<br /></div>
</td>
<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="206"><div align="CENTER" lang="en-US">
Databasename</div>
</td>
<td style="border: 1px solid #000000; padding: 0.1cm;" width="206"><div align="CENTER" lang="en-US">
recovery_model_desc</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="205"><div align="CENTER" lang="en-US">
1</div>
</td>
<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="206"><div align="CENTER" lang="en-US">
Model</div>
</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="206"><div align="CENTER" lang="en-US">
Full</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="205"><div align="CENTER" lang="en-US">
2</div>
</td>
<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="206"><div align="CENTER" lang="en-US">
New Test Database</div>
</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="206"><div align="CENTER" lang="en-US">
Full</div>
</td>
</tr>
</tbody></table>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
As you can see that both
the databases have same recovery model i.e 'Full'.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">If you would like to
change the recovery model for your database then you can change it
with the help of </span><span style="color: navy;"><span lang="zxx"><u><a href="http://sql-server-recovery.blogspot.in/2012/02/two-ways-to-change-recovery-model.html" rel="nofollow"><span lang="en-US">SQL Server Management Studio (SSMS) or Transact SQL (T-SQL)</span></a></u></span></span><span lang="en-US">.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Is Your SQL Server
Database Really in Full Recovery Model</b>? It has been noticed that your
SQL server database is not in full recovery model as conformed by
'Select recovery model' query. I am not telling anything wrong you
guys! You can verify this at you end also as I have done.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
As discussed in the above
paragraph of article, you can make backup of transaction log if your
database is in full recovery model & also confirmed by query that
my newly created database (NEW Test Database) is in full recovery
mode that means I am able to take transaction log backup right! But
when I try to take transaction log backup got below error message:</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: #cccccc;">BACKUP
LOG New Test Database TO DISK =
'C:\DATA\Backup\New_Test_Database.BAK'</span></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Got error message on my
screen:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #c5000b;">Msg
4214, Level 16, State 1, Line 1</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #c5000b;">Backup
log cannot be performed because there is no current database backup.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #c5000b;">Msg
3013, Level 16, State 1, Line 1</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #c5000b;">Backup
log is terminating abnormally.</span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-24375409935160970242012-12-20T20:21:00.004-08:002013-11-17T19:13:05.415-08:00Getting Started with SQL Server Transaction Log<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div lang="en-US" style="margin-bottom: 0cm;">
SQL server transaction log
records of all the changes made on the database while the actual
records are placed in a separate file. This file is known as
transaction log file and the file extension for transaction log file
is .ldf. Transaction log holds enough record to undo all the changes
made on the database. It is the most critical component in the SQL
server that can recover database if there is a system failure.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Note</b>: Never delete or move
transaction log unless you know the effect of this.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
As we have seen above
transaction log file uses a transaction log file (.ldf) to records
all the changes made on the database and the size of this file grows
as per every change on the database. SQL server offers two designed
to counterbalance the size of transaction log: transaction log
truncation & log file shrinking.
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Transaction Log
Truncation</b></span><span lang="en-US">: SQL server automatically
removes the entries from the transaction log file. The frequency of
record removal depends upon the recovery model used in the database.
If you have chosen full or bulk-logged recovery model for your
database then SQL server truncates the transaction log file when you
perform backup. In case of simple recovery model, sql server
truncates the transaction log file at every transaction checkpoint.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Note</b>: It is recommended to
use full recovery model for production database.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Transaction Log
File Shrinking</b></span><span lang="en-US">: Transaction log
truncation removes the entries only from the log file; it does not
reduce the size of transaction log file. If you know there is a
unused space in your transaction log file then you can shrink your
log file and this process is know as transaction log file shrinking.
Run below T-SQL command to shrink a transaction log file:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">DBCC SHRINKFILE
(<transaction-log-file-name>, <your-desired-file-size>)</your-desired-file-size></transaction-log-file-name></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Sometime, you do not
finish with above command. Try following code to shrink the
transaction log file.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">USE DatabaseName</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">DBCC
SHRINKFILE(<transactionlogname>, 1)</transactionlogname></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">BACKUP LOG <databasename>
WITH TRUNCATE_ONLY</databasename></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">DBCC
SHRINKFILE(<transactionlogname>, 1)</transactionlogname></span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="background-color: #cccccc;">GO
</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<b>Note</b>: You can't shrink a
transaction log file when your database is offline.</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Backup
Transaction Log</b></span><span lang="en-US">: If you are using
simple recovery model for your database then you can't backup the
transaction log, if you still want to backup then you have to change
the recovery model for your database first. Read my article “</span><span style="color: navy;"><span lang="zxx"><u><a class="western" href="http://sql-server-recovery.blogspot.in/2012/02/two-ways-to-change-recovery-model.html" rel="nofollow">Twoways to Change the Recovery Model</a></u></span></span><span lang="en-US">”.
After changing the recovery model for your database, you can perform
backup by following T-SQL command:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
BACKUP LOG
Your-Database-Name</div>
<div lang="en-US" style="margin-bottom: 0cm;">
TO Your-desired-location;</div>
<div lang="en-US" style="margin-bottom: 0cm;">
GO</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-7173067759684423462012-11-27T00:16:00.001-08:002013-11-17T19:10:03.235-08:00Top 5 Disaster Recovery Solutions in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: 0cm;">
<span lang="en-US">Hey Guys!! How are
you? I hope that you all are doing well. As we all know disaster can
stri</span><span lang="en-US">ke on the database in any form &
any time. So it is better to well prepared to get rid from it. There
are so many disaster recovery solutions available in SQL server, here
we will go through top 5 of them.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Disaster</b></span><span lang="en-US">:
In term of SQL server, disaster is defined as an event that makes
data unavailability or stop the normal functioning of database known
as disaster. An event can appear in any form like hardware failure,
virus attack, power failure, natural disaster, human errors, system
hang, and many more. Some of the examples are given below:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Online railway
reservation halt: Unable to show seats availability.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Data is unavailable:
Think about big data retrieval companies like Google, Yahoo, and
MSN.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Order can not be
placed: Think about online shopping sites like Amazon, where user
wants to place an order but unable to do.</div>
</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzBfj6av7tHPQw3Xd-H6y_GLZBMkDaCLqzJ0DJMX3vU-GzqcmzlkTKM9uF3qTh66uu0enAl_BSxnFE27HOoXy9KkMfR6ll0pbnqd3XPhZxSqyin2ZHUW7lh0R_fOGn-WLQ0gySLTwXkak/s1600/disaster.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzBfj6av7tHPQw3Xd-H6y_GLZBMkDaCLqzJ0DJMX3vU-GzqcmzlkTKM9uF3qTh66uu0enAl_BSxnFE27HOoXy9KkMfR6ll0pbnqd3XPhZxSqyin2ZHUW7lh0R_fOGn-WLQ0gySLTwXkak/s1600/disaster.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b><br /></b></span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Disaster Recovery
Solution</b></span><span lang="en-US">: It is a process to overcome
from data loss or business down after any disaster. Top 5 disaster
recovery solutions in SQL server 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>Top 1: </b></span><span lang="en-US"><b>High
Availability & Disaster Recovery Solution</b></span><span lang="en-US">:
It is shortly known as HADR. This disaster recovery solution
introduced in newly SQL server version 2012 & much expensive in
comparison with other disaster recovery solutions. HADR offers high
availability along with data recovery service if disaster has made
business down. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Top 2: </b></span><span lang="en-US"><b>SQL
Server Clustering Solution</b></span><span lang="en-US">: SQL server
clustering is one the best disaster recovery solution in the SQL
server. In this technology; there are two or more physical servers
that handle high availability and minimize the business downtime
almost zero. When a physical server suddenly fails to perform the
operation then all the tasks & responsibilities of failed server
automatically take over by another physical server. It was introduced
with Windows NT Server 4.0 enterprise edition.</span></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVQHXmIMeqDsUoUrqOpodoZure9wA-b62BWfZ5J0cS6CwbQngRweaHRIVqV1PA0PMNZc8RJLv8IE61cE08TGGSuEbmLq0KPKIIURwkJ77mSWKqiE4gfAafHdZ5HhduKGHziMqR5WcuLos/s1600/clustering.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVQHXmIMeqDsUoUrqOpodoZure9wA-b62BWfZ5J0cS6CwbQngRweaHRIVqV1PA0PMNZc8RJLv8IE61cE08TGGSuEbmLq0KPKIIURwkJ77mSWKqiE4gfAafHdZ5HhduKGHziMqR5WcuLos/s1600/clustering.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Top 3: </b></span><span lang="en-US"><b>SQL
Server Database Mirroring Solution:</b></span><span lang="en-US"> It
is the fastest disaster recovery solution to bring database online in
comparison of all other solutions. In addition to clustering solution
that provides data protection at SQL server instance level, it
provides data protection at database level. In </span><span style="color: navy;"><span lang="zxx"><u><a class="western" href="http://technet.microsoft.com/en-us/library/cc917680.aspx" rel="nofollow">database mirroring</a></u></span></span><span lang="en-US"> transaction log
records are sent to the mirrored transaction log as soon as log
buffer is written to the disk on the principle server. There is only
one mirrored server for each principle server.</span></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/AVvXsEizf7CiUc-nsM1hgYCHAlRUZfl21ppMiyr0VPnxOcK2gvbpFxIbZsKlUP1Cm_gR4Y1957IH9-WI9X1CE5dFhvYR7EVvLtDh7FO36preyds0TEOSs_qbDV6huMeiIeDPvzhRMx8nN9bBaCw/s1600/mirroring.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizf7CiUc-nsM1hgYCHAlRUZfl21ppMiyr0VPnxOcK2gvbpFxIbZsKlUP1Cm_gR4Y1957IH9-WI9X1CE5dFhvYR7EVvLtDh7FO36preyds0TEOSs_qbDV6huMeiIeDPvzhRMx8nN9bBaCw/s1600/mirroring.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Top 4: </b></span><span lang="en-US"><b>SQL
Server Log Shipping Solution</b></span><span lang="en-US">: It works
on the database & server levels so you can configure more than
one database in this. Unlike database mirroring that supports full
recovery model only, it supports full as well as bulk logged recovery
models. It was also introduced in SQL server 2005 & does not
support automatic failover. </span><span style="color: navy;"><span lang="zxx"><u><a class="western" href="http://msdn.microsoft.com/en-us/library/ms187103.aspx" rel="nofollow">Log shipping</a></u></span></span><span lang="en-US"> failover time
duration is larger than database mirroring failover time duration
that can vary from minutes to hours (sometimes).</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiphGgoAnH-30ApK9bJDEtoVcEA9HCYMs9Bc8CrftnHB8jjZ9XwpAJZnffP53oGLiT2tiRoE8HlI9Jlkw1oatp2QmB7O0XlzOJrBRCu7NlpbNQiAKnyuFvsRgxgxgBcf2WSLp72QDHJww4/s1600/log+shipping.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiphGgoAnH-30ApK9bJDEtoVcEA9HCYMs9Bc8CrftnHB8jjZ9XwpAJZnffP53oGLiT2tiRoE8HlI9Jlkw1oatp2QmB7O0XlzOJrBRCu7NlpbNQiAKnyuFvsRgxgxgBcf2WSLp72QDHJww4/s1600/log+shipping.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Top 5: </b></span><span lang="en-US"><b>3</b></span><sup><span lang="en-US"><b>rd</b></span></sup><span lang="en-US"><b>
Party SQL Server Recovery Program</b></span><span lang="en-US">:
There are so many 3</span><sup><span lang="en-US">rd</span></sup><span lang="en-US">
party SQL server recovery programs available online like </span><span style="color: navy;"><span lang="zxx"><u>StellarPhoenix SQL recovery</u></span></span><span lang="en-US">. Most
of the recovery programs offer free demo version to download that
shows the preview of your corrupt database. If you are satisfied with
the demo then go forward to register your full copy from their
official websites.</span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKIRhrVbjWrkRKX5OvRTL9impEwPfm-y65nnNDdamjeQF3ZS7qaSzKgCZeAFJPV_tW0hFcEnP2zZ6tZW5O_I6JjeYlRPVN-DqnsP8HNlYWapOpfZASKgyLRlkhAWZgZQvYTkBMpy-0SIw/s1600/stellarsql.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKIRhrVbjWrkRKX5OvRTL9impEwPfm-y65nnNDdamjeQF3ZS7qaSzKgCZeAFJPV_tW0hFcEnP2zZ6tZW5O_I6JjeYlRPVN-DqnsP8HNlYWapOpfZASKgyLRlkhAWZgZQvYTkBMpy-0SIw/s1600/stellarsql.gif" /></a></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><br /></span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-32247317188076739062012-11-22T22:33:00.000-08:002013-11-15T03:04:59.954-08:00SQL Server Backup & Reduce Time to Accomplish a Backup<div dir="ltr" style="text-align: left;" trbidi="on">
As we all are aware that backup of data is essential for reducing the change of data loss due to failure and SQL server is not an exception. SQL server backup is helpful in restore & recovery of loss data. It can be created at different level of database. In addition to this, full recovery model is required for creating a backup of <a href="http://sql-server-recovery.blogspot.in/search/label/Transaction%20log%20file" rel="nofollow" target="_blank">transaction log file</a>. There are so many types of backup available in SQL server like: full backup, differential backup, log backup, file backup, partial backup, copy-only backup and so on. <br />
<br />
<b>Restrictions during Backup</b>: MS SQL server allows you to perform various operations on the backup but there are some restrictions too. In SQL server 2005 & later versions, you can't backup a database that is offline. Backup can occur only when database is online & being in use. <br />
<br />
<b>Example</b>: Suppose you want to make a full backup of database but one of the filegroup of the database is offline, here backup fails because filegroup is the part of database. <br />
<br />
Most of the operations are possible during backup is in process. You will be able to perform insert, delete, or update statement on the database during backup but there are some restrictions too.<br />
<ul style="text-align: left;">
<li>Following operation are not allowed during backup:</li>
<li>File management statements like ALTER database.</li>
<li>Create & Delete operations.</li>
<li><a href="http://sql-server-recovery.blogspot.in/search/label/shrink%20sql%20server%202005%20transaction%20log" rel="nofollow" target="_blank">Shrink transaction log</a> or database file.</li>
</ul>
<div style="text-align: left;">
<b>How to Make Backup?</b> You can make full SQL server database backup by the use of SQL server management studio (SSMS), Transact SQL (TSQL), and PowerShell. I personally prefer TSQL over SSMS & PowerShell. You can choose any one or all to make full database backup of your database. Backup of the newer versions can't be restored on the lower versions of SQL server. For Example, you can't restore SQL server 2000 backup on SQL server 2005.<br />
<br />
<b>How to Reduced Backup Time?</b> You can reduce the backup time of the SQL server database by doing following tasks:</div>
<ol style="text-align: left;">
<li><b>Use Backup Compression</b>: Backup time is directly proportional to the size of database & speed of backup. If database size is big & backup speed is slow then it will take long time to make backup of database. You can enhance the backup speed with the help of backup compression. In addition to this, backup compression is introduced in SQL server 2008 enterprise and later versions. It enhances the backup as well as restore speed for the database and works on 'read some & write some' concept.</li>
<li><b>Choose Separate Disk for Backup</b>: If the location of your database and backup is same so disk is trying to read & write operation simultaneously. Backing up a database on the separate disk can help in reducing the time to backup.</li>
</ol>
<div style="text-align: left;">
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-41077180673167032612012-10-30T21:13:00.002-07:002013-11-15T03:05:29.971-08:00How to Resolve SQL Server Error 5172?<div dir="ltr" style="text-align: left;" trbidi="on">
<title></title>
<style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
</style>
</div>
-->
<div style="margin-bottom: 0cm;">
<span lang="en-US">Sometimes when you
tried to re-attach a SQL server database, you may get error message</span> “<span lang="en-US"><b>5172:</b></span><span lang="en-US">
The header for file 'databaselog.ldf' is not a valid database file
header. The Page Audit property is incorrect. Device activation
error. The physical file name 'dataaselog.ldf' may be incorrect. New
Log file was created.”</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Solution</b></span><span lang="en-US">:
You can solve above described error message by two methods, restore
from updated backup, & create a test database.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Restore from
Updated Backup</b></span><span lang="en-US">: If you have maintained
a backup copy of the database then it is the right time to use it. In
case of unavailability of updated backup; you can go with the second
method that is creating a test database with same size & layout.
</span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Create a Test
Database</b></span><span lang="en-US">: If you don't have any updated
backup copy of your database then it is the time to create a test
database with the same database size & layout. </span></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"> </span>
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Steps to Star</b></span><span lang="en-US"><b>t
SQL server with test database:</b></span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Create a test
database with same size & layout.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Shutdown SQL server
database.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Swap-in database
files.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now restart your SQL
server database.
</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>For SQL Server
2005</b></span><span lang="en-US">:</span></div>
<ul>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Now your
database comes up in <a href="http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html" target="_blank"><b>S</b></a></span><span lang="en-US"><b><a href="http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html" rel="nofollow" target="_blank">uspect Mode</a>.</b></span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now you can export
all the data in a new database or rebuild the transaction log.
</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Run DBCC
CHECKDB</span><span lang="en-US"> with repair_allow_data_loss.</span></div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Bear in mind
that you will lose some amount of data.</span></div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Note</b></span><span lang="en-US">:
If you still get error message then I suggest you to call MS SQL
server support team. Waiting for solution is not a most ideal way
when your business is down. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>For SQL Server
2000</b></span><span lang="en-US">:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
If you are using SQL
server 2000 database then your database will not come in suspect mode
automatically. You have to bring your database in suspect mode. To
put the Database in suspect mode, you need to hack the sysdatabases
table so the status field has the 'bypass recovery' bit set. You can
do it by reconfiguring the server to allow updates. Run the bellow
script:</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
sp_configure "allow
updates",1</div>
<div lang="en-US" style="margin-bottom: 0cm;">
reconfigure with override</div>
<div lang="en-US" style="margin-bottom: 0cm;">
go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
update sysdatabases set
status= 32768 where name = "mydb"</div>
<div lang="en-US" style="margin-bottom: 0cm;">
go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
sp_configure "allow
updates",0</div>
<div lang="en-US" style="margin-bottom: 0cm;">
reconfigure with override</div>
<div lang="en-US" style="margin-bottom: 0cm;">
go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
# Now your database is in
suspect mode
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Perform bel</b></span><span lang="en-US"><b>ow
Steps</b></span><span lang="en-US">: </span>
</div>
<ul>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Now export all
the data to a new database or rebuild the transaction log. </span>
</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Run DBCC
CHECKDB with Repair_allow_data_loss. </span>
</div>
</li>
</ul>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-16583862621355719132012-10-28T21:47:00.000-07:002012-10-28T21:47:39.925-07:00How to Resolve SQL Server Error Message 18456?<div dir="ltr" style="text-align: left;" trbidi="on">
<title></title>
<style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">When you try to
connect with SQL server database, you </span><span lang="en-US">could
get success in most of the cases but sometimes you may fail to
connect with SQL server database too. In case of failure you may get
“Error: 18456, Severity: 14, State: 38” error message in the
error log. If you are running SQL Server 2008 R2 database on Window
server 2008 R2 then I will assist you some tips to get rid from this
problem. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Reason</b></span><span lang="en-US">:
SQL server 2008 R2 database generates error message 18456 when It
finds the desired database is either offline or not accessible. You
can't access a database when it is in restoring state, single user
mode, autoclose etc. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Solution</b></span><span lang="en-US">:
Solution for these types of problem depends upon the nature of the
database like a new database or an old database. </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Condition 1</b></span><span lang="en-US">:
If you have recently created your database under different user
account then make sure you are trying to connect with same user
account. Sometimes we forget to give full permission to newly created
database, so check the permission for newly created database. </span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY0ZJWdNQHtHnKal0jJ2oC9NOvF048AzZGA0L5HeyOb-quNS3YcYkSW90NlJ9uvVgeRYKWAgwdndEQvMtRXUTqt-fCmVwTC1EcCOe5W7DNyR6dnnLBw0e8so5yQM-81unxfunQv8PGCRY/s1600/image+1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY0ZJWdNQHtHnKal0jJ2oC9NOvF048AzZGA0L5HeyOb-quNS3YcYkSW90NlJ9uvVgeRYKWAgwdndEQvMtRXUTqt-fCmVwTC1EcCOe5W7DNyR6dnnLBw0e8so5yQM-81unxfunQv8PGCRY/s320/image+1.JPG" width="320" /></a></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"> </span>
</div>
<div lang="en-US" style="margin-bottom: 0cm; margin-left: 1.25cm; text-indent: 1.25cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Condition 2</b></span><span lang="en-US">:
If you are trying to connect with an old database then check the
status of that database in sys.databases and the query for check is:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #99284c;"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);">SELECT
state_desc, user_access_desc, is_auto_close_on, is_in_standby FROM
sys.databases WHERE name = 'your database name';</span></span></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">:
If you don't want to specify the name of database then check default
database for login and the query is:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: #99284c;"><span style="background: none repeat scroll 0% 0% rgb(192, 192, 192);">SELECT
default_database_name FROM sys.server_principals WHERE name =
N'login_name';</span></span></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/AVvXsEjqWWZ08Yh88lw-ClfyHaJCM6AJx2jKVPAxl9i-zT9ojSlVsRbARobHbGOvbBCt77jpgcWpbZSTDKyqSJ5BSPEE7pWpE1eU_3jsuGIIl2jCY7R5d-lezch-BP1Z2YabEVNlZbZsCj42DqA/s1600/image+2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqWWZ08Yh88lw-ClfyHaJCM6AJx2jKVPAxl9i-zT9ojSlVsRbARobHbGOvbBCt77jpgcWpbZSTDKyqSJ5BSPEE7pWpE1eU_3jsuGIIl2jCY7R5d-lezch-BP1Z2YabEVNlZbZsCj42DqA/s320/image+2.JPG" width="320" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br />
</div>
<div lang="en-US" style="margin-bottom: 0cm; margin-left: 1.25cm; text-indent: 1.25cm;">
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-13559440480624202032012-09-20T03:58:00.000-07:002013-11-17T19:16:50.037-08:00Three Methods to Shrink Tempdb System Database and Files<div dir="ltr" style="text-align: left;" trbidi="on">
<title></title>
<style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
</style>
</div>
-->
<div style="margin-bottom: 0cm;">
<span lang="en-US">Tempdb database is a
system database; available for all the users connected to the SQL
server instances. Database administrator can not perform <a href="http://sql-server-recovery.blogspot.in/2011/07/how-to-backup-transaction-log-in-sql.html" rel="nofollow" target="_blank">backup</a> &
restore operation on the tempdb database because it is automatically
dropped when system is shut down. All the operations in this database
are minimally logged and every time SQL server starts with a clean
copy of tempdb database. Size of tempdb database can affect the
performance of your SQL server database so it is the best practice to
<a href="http://sql-server-recovery.blogspot.in/2011/11/shrink-transaction-log-file-of-sql.html" rel="nofollow" target="_blank">shrink</a> tempdb database whenever you see rapidly growth in the size of
tempdb database or tempdb transaction log file. Here I will explain
top three methods to shrink tempdb database.</span></div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Shrink Using
Transact-SQL Command</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Shrink Using DBCC
SHRINKDATABASE Command</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Shrink Using DBCC
SHRINKFILE Command</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US">1. </span><span lang="en-US"><b>Shrink
using Transact-SQL command</b></span><span lang="en-US">: It will
provide you complete control over the size of tempdb database. After
running T-SQL command you have to restart SQL server database.
Perform bellow steps to shrink tempdb:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Stop SQL server.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
To start SQL server
type sqlserver -c -f command on command prompt.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now connect to the
SQL server using query analyzer & run following T-SQL commands.</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
ALTER DATABASE tempdb
MODIFY FILE</div>
<div lang="en-US" style="margin-bottom: 0cm;">
(NAME = 'tempdbdata', SIZE
= target_size_in_MB)
</div>
<div lang="en-US" style="margin-bottom: 0cm;">
ALTER DATABASE tempdb
MODIFY FILE</div>
<div lang="en-US" style="margin-bottom: 0cm;">
(NAME = 'tempdblog', SIZE
= target_size_in_MB)</div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Again stop the SQL
server.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now restart the SQL
server as a service and verify the size of tempdbdata.mdf &
tempdbdata.ldf files.</div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">2.</span><span lang="en-US"><b>
Shrink using DBCC SHRINKDATABASE Command</b></span><span lang="en-US">:
It will shrink the tempdb database and you may restart the SQL
server. To use this command your database must be in single user
mode. If database is not in single user mode then change the mode
using Alter command and the shrink command is given bellow:</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
DBCC Shrinkdatabase
(tempdb, 'target percent')</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">3.</span><span lang="en-US"><b>
Shrink using DBCC SHRINKFILE Command</b></span><span lang="en-US">:
It will enable you to shrink an individual tempdb database files
(.mdf & .ldf) and provides more flexibility than DBCC
shrinkdatabase command. As similar to DBCC Shrinkdatabase command
your database must be in single user mode to run this command also.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Use</span><span lang="en-US">
tempdb</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
dbcc shrinkfile
(tempdbdata, 'target size in MB')</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
dbcc shrinkfile
(tempdblog, 'target size in MB')</div>
<div lang="en-US" style="margin-bottom: 0cm;">
Go</div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Tips</b></span><span lang="en-US">:
Don't perform any operation on the tempdb database during last two
methods as DBCC SHRINKDATABASE & DBCC SHRINKFILE command. </span>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-2140774260162617582012-09-11T03:56:00.002-07:002013-11-17T19:17:39.311-08:00Top 4 methods to transfer SQL database from one location to another<div dir="ltr" style="text-align: left;" trbidi="on">
Are you looking for a simple method that <a href="http://sql-server-recovery.blogspot.in/2011/05/how-to-transfer-sql-database-from-one.html" rel="nofollow" target="_blank">transfers your SQL server database</a> from one location to another? It is the right place for you; here I will tell you top 4 simple methods that help you. Each method has its own benefits over other. You may finish by one method or need to try all four and the top four methods are...<br />
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Backup & Restore Method</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Detach & Attach Method</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Generate Create Script Method</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Copy Database Wizard using SSMS</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US">1. </span><span lang="en-US"><b>Backup & Restore Method</b></span><span lang="en-US">: As the name suggests; first backup the database from the old location and restore it on desired location. It creates a new copy of database in the desired location. Database administrator can't perform this method during database is offline.</span></div>
<div style="margin-bottom: 0cm;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw60FaT8aBfZwgVASpIPQ1oucok3OHfaVW_5FQQOgmYKlQIvOyILCEoB0WFw1SpWvDNUTjCRUa1LG2L9QZKn6rUJTxDNNv0aqoSdw6spATgLwPxAClkkKL1AQsyPsKjOl3BxgDVxDVPQ8/s1600/backup.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw60FaT8aBfZwgVASpIPQ1oucok3OHfaVW_5FQQOgmYKlQIvOyILCEoB0WFw1SpWvDNUTjCRUa1LG2L9QZKn6rUJTxDNNv0aqoSdw6spATgLwPxAClkkKL1AQsyPsKjOl3BxgDVxDVPQ8/s1600/backup.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">2. </span><span lang="en-US"><b>Detach & Attach Method</b></span><span lang="en-US">: It is purely an offline operation that means database administrator can't perform this method when SQL server database is online. To do this first detach the SQL server database from old location & attach it on desired location. This method moves database permanently from one location to another instead of copying.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZOZxqJhDq3QSZrmxAksb4cvjZD02T3_qfzcePKmj0TM7T0XOeZoq1gKZKYcA3HlOwxL1cXUDlGaL8P21-Nr8K-ZJqPEgKdK9NWvhGOISiSJ7JfgAcBxaNOxHBQq-YopFxNnHF-l6WONg/s1600/attach.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZOZxqJhDq3QSZrmxAksb4cvjZD02T3_qfzcePKmj0TM7T0XOeZoq1gKZKYcA3HlOwxL1cXUDlGaL8P21-Nr8K-ZJqPEgKdK9NWvhGOISiSJ7JfgAcBxaNOxHBQq-YopFxNnHF-l6WONg/s1600/attach.jpeg" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggUaKTH4ZHpExW7ES4dBPeX0uVQ20luY-o-GoMHsY8MU5ThqvMBSfsm1yupBYEqHCTtKAlLX6G2BDn4xyxpFnofKfGpG0ONgWceBBJFPdVe3v-CvJzn0lq6oFyFbdma-qt3QgLaw6ItuI/s1600/detach.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggUaKTH4ZHpExW7ES4dBPeX0uVQ20luY-o-GoMHsY8MU5ThqvMBSfsm1yupBYEqHCTtKAlLX6G2BDn4xyxpFnofKfGpG0ONgWceBBJFPdVe3v-CvJzn0lq6oFyFbdma-qt3QgLaw6ItuI/s1600/detach.jpeg" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">3. </span><span lang="en-US"><b>Generate Create Script Method</b></span><span lang="en-US">: It is another best method for transferring SQL server database from one location to another. To do this follow below steps:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Select the database from old location in SSMS</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Right click on the database and select “tasks”</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now choose 'generate script' to launch the Generate create script wizard.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Click next to close generate the script wizard.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Connect with the new desired server and create a new blank database in it.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Go to the new query windows & paste the above generated script</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now execute it with desired database context.</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US">4. </span><span lang="en-US"><b>Copy Database Wizard using SSMS</b></span><span lang="en-US">: Perform bellow steps to transfer database from one location to another.</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Select the database from the old location using SQL server management studio</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Right click on the database and select 'tasks'</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now choose 'copy database wizard' from the list to launch wizard.</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Enter the old & new server</span><span lang="en-US"> credentials.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Select either attach/detach or any other SMO type.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Click next to run & finish to execute the script.</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Apply</span><span lang="en-US"> For: All 4 methods can be applied for SQL server 2008 database.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin38y2rvQHNlAkOyVW9HqO8P8CpCglaiqO4Y9m8L7qzRnhG37SzCO-qKJN_Ge8yayx-ShYx2ahD2nZBv7hZSaQHbPcDhri_IKwaxLpoJ2QAS1OsiuEBNzV8X_w6QW_SlkwTKbyYm6wJYE/s1600/sql+2008.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEin38y2rvQHNlAkOyVW9HqO8P8CpCglaiqO4Y9m8L7qzRnhG37SzCO-qKJN_Ge8yayx-ShYx2ahD2nZBv7hZSaQHbPcDhri_IKwaxLpoJ2QAS1OsiuEBNzV8X_w6QW_SlkwTKbyYm6wJYE/s1600/sql+2008.jpeg" /></a></div>
<div style="margin-bottom: 0cm;">
</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-83072488496687417462012-09-10T03:30:00.000-07:002013-11-17T19:17:56.321-08:00SQL Server Compatibility Level: Check & Change <div dir="ltr" style="text-align: left;" trbidi="on">
<title></title> <style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
A.western:link { so-language: zxx }
A.ctl:link { so-language: zxx }
</style>
</div>
--> <div style="margin-bottom: 0cm;">
<span lang="en-US">Have you recently upgraded your SQL server database from lower to upper version by any of the method attach/detach or backup/restore and not changed the compatibility level for upgraded SQL server version? If your answer is YES then it is the right time to change the compatibility level of upgraded version because your database is still acting same as it was running on earlier version of SQL server. Compatibility level of database does not change automatically. It is not a major problem for overall standpoint but you will not take advantage of upgraded version unless your database compatibility is change. Here I will give you some tips regarding how to check the compatibility level of SQL server database, & how to change the compatibility level of upgraded database. </span> </div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to check the compatibility level of SQL server database</b></span><span lang="en-US">? It is the first step towards resolving above described problem. You will have to check the compatibility level of SQL server under that your database is running. You can do this by following methods: </span> </div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Using SQL server management studio</b></span><span lang="en-US">: Perform following steps to check the compatibility level of SQL server using SSMS: </span> </div>
<ul>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Right click on the database </span> </div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Select property & go-to the options tab, repeat this for all databases. </span> </div>
</li>
</ul>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1d0KQTD0L52W8cxXaAjKpF5kogh1WXSYaGujYIDREcT-t3Tf-0rC6C_KtYik3fvsccivEZZul266h8SdnyPxMWNhtjsJk38XivPyF8Vbhyu0nDiOKLFmTUSq1Qr8ai_4tl_3ZSEhNn4w/s1600/compatiblity.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="67" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1d0KQTD0L52W8cxXaAjKpF5kogh1WXSYaGujYIDREcT-t3Tf-0rC6C_KtYik3fvsccivEZZul266h8SdnyPxMWNhtjsJk38XivPyF8Vbhyu0nDiOKLFmTUSq1Qr8ai_4tl_3ZSEhNn4w/s320/compatiblity.jpg" width="320" /></a></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US">Note: In the above picture </span><span style="color: navy;"><span lang="zxx"><u><a class="western" href="http://sql-server-recovery.blogspot.in/search/label/adventureworks" rel="nofollow">AdventureWorks</a></u></span></span><span lang="en-US"> is the name of database & compatibility level is 90.</span></div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
</div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Using Query</b></span><span lang="en-US">: Run (“</span><span style="color: #b80047;"><span lang="en-US">SELECT</span></span><span lang="en-US"> </span><span style="color: green;"><span lang="en-US">* FROM</span></span><span lang="en-US"> </span><span style="color: #6b0094;"><span lang="en-US">sys.databases</span></span><span lang="en-US">”) query to get information for the all databases. It will show you compatibility level for all databases in current SQL server.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div lang="en-US" style="margin-bottom: 0cm;">
Bellow is the compatibility level list that will appear when you check compatibility level of database.</div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 6.0-------- 60</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 6.5-------- 65</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 7.0-------- 70</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 2000-------80</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 2005-------90</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 2008-------100</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 2008 R2---100</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
SQL server 2012------- 110 </div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>How to Change the Compatibility Level of Upgraded SQL server</b></span><span lang="en-US">? So once you have identified the compatibility level of upgraded SQL server, you can change it by ALTER command and the command is...</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span style="color: red;"><span lang="en-US">ALTER</span></span><span lang="en-US"> Database </span><span style="color: green;"><span lang="en-US">AdventureWorks</span></span></div>
<div style="margin-bottom: 0cm;">
<span style="color: #ff3366;"><span lang="en-US">Set</span></span><span lang="en-US"> Compatibility Level = </span><span style="color: green;"><span lang="en-US">90</span></span><span lang="en-US">;</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<span style="color: green;">Go</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Best Practices</b></span><span lang="en-US">: Attempting to change the compatibility of database while users are connected to the database may produce some problem so Microsoft recommends following procedure to change the compatibility level of database:</span></div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Convert your database from multi_user mode to single user mode.</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Syntax</b></span><span lang="en-US">: ALTER database set single_user</span></div>
<ol start="2">
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now change the compatibility level of the database</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Convert back to multi_user mode.</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Syntax</b></span><span lang="en-US">: ALTER database set multi_user.</span></div>
<ol start="4">
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Now run DBCC checkdb on the database to check the </span><span style="color: navy;"><span lang="zxx"><u><a class="western" href="http://sql-server-recovery.blogspot.in/search/label/database%20integrity%20check" rel="nofollow">integrity of database</a></u></span></span><span lang="en-US">. </span> </div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Permission Required</b></span><span lang="en-US">: ALTER permission on the database. </span> </div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-12897515259674976152012-09-06T22:57:00.000-07:002013-11-17T19:19:26.893-08:00How to Resolve SQL Server Error Message 3241?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Have you got below error message on your screen during restore of SQL server database?<br />
<br />
“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)"<br />
Additional information:<br />
The media family on device 'Backupfile.bak' is incorrectly formed. SQL Server cannot process this media family.<br />
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)”<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaRV1MuQuS_WB-CaqrXVHIWgpBrL7Tu-rA4BCNWLgEGUey-HMy8scXoOpkvzwXvzQR9n6cwbQGAshZOpK2oQdtaHEKlSO-ouoYHQGHKc1x24WTXdJa0ZhqP-doubzV5jU87m0jTJX6ec8/s1600/Error+3241.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="96" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaRV1MuQuS_WB-CaqrXVHIWgpBrL7Tu-rA4BCNWLgEGUey-HMy8scXoOpkvzwXvzQR9n6cwbQGAshZOpK2oQdtaHEKlSO-ouoYHQGHKc1x24WTXdJa0ZhqP-doubzV5jU87m0jTJX6ec8/s320/Error+3241.png" width="320" /></a></div>
<br />
<b>Reason</b>: This can happen due to many reasons but the most common reason is due to version mismatch between backup & restore SQL server (To perform backup & restore operation of your SQL server database, you will have required two SQL servers with same version or higher version). You can't restore a backup that was taken from higher version of SQL server to lower version of SQL server.<br />
<br />
For Example: You <a href="http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/32a67469-0636-4ded-923f-2e257f7e68e1/" rel="nofollow" target="_blank">cann't restore a backup</a> that was taken from SQL server 2008 to SQL server 2005.<br />
<br />
<b>Resolution</b>: To resolve above error message first <a href="http://sql-server-recovery.blogspot.in/2011/04/how-to-find-version-level-and-edition.html" rel="nofollow" target="_blank">check the version of both SQL server</a>. Run bellow command to check the version:<br />
<br />
<span style="color: #0b5394;">Select @@version;</span><br />
<span style="color: #0b5394;">Go</span><br />
<br />
If this command verifies that backup taken SQL server version is higher than restore SQL server version then you have two choice either upgrade restore SQL server or restore backup on the higher version of SQL server.<br />
<br />
<b>Alternate Solution</b>: You can also resolve SQL server error message 3241 by performing below steps:<br />
<br />
<ol style="text-align: left;">
<li>Create a blank database in SQL server 2005</li>
<li>Generate the script using 'Generate Script Wizard'</li>
<li>Now create structure of your database</li>
<li>Finally use Import/Export data wizard to import your data.</li>
</ol>
<br />
<br />
<ol></ol>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-24755097995764584272012-08-15T21:41:00.000-07:002013-11-17T19:18:31.154-08:00How to recover deleted records of SQL Server without updated backup?<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
<span lang="EN-IN">Have your colleague or partner deleted some of the records of your SQL server table? He has deleted their corresponding records from the audit log too. Are you searching an effective method for <a href="http://www.stellarsqldatabaserecovery.com/recover-mssql-table-records.php" rel="nofollow" target="_blank">recovery of records</a>? I am assuring you that you have reached at the right blog. Here we will discuss about recovery of deleted records in SQL server table.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ_eh5rftTaIxqAFHq3V1eKWDrUTtRhQ0eM4RPaC8z32QuxJR6Sckx4Jtg6zm1LFtIkrBTEyKdW2CnFGnZaFrbHQ_NFhfOyA0pYiZX2LuRQLO3trOFFl8JdgdrhjhLnJrZsZAcjNFX4q0/s1600/angry+cartoon.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ_eh5rftTaIxqAFHq3V1eKWDrUTtRhQ0eM4RPaC8z32QuxJR6Sckx4Jtg6zm1LFtIkrBTEyKdW2CnFGnZaFrbHQ_NFhfOyA0pYiZX2LuRQLO3trOFFl8JdgdrhjhLnJrZsZAcjNFX4q0/s200/angry+cartoon.jpg" width="200" /></a></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">Prerequisites for recovery of records</span></b><span lang="EN-IN">: </span></div>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">If your database was in full recovery model then recovery is possible otherwise not.</span></li>
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Database backup prior to the deletion of the records.</span></li>
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Transaction log backup of the database, if you have not taken any transaction log backup of your database then make transaction log backup of the database now.</span></li>
</ul>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">Steps to recover deleted records</span></b><span lang="EN-IN">: Follow the below steps to recover your deleted records:</span></div>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Make the backup of transaction log where records have been deleted and the script will be</span></li>
</ul>
<div class="MsoNormal">
<i style="mso-bidi-font-style: normal;"><span lang="EN-IN" style="color: #444444;">BACKUP LOG yourdatabasename TO DISK = 'some path';</span></i></div>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Restore your database backup (prior to the deletion of records) to a test environment with different name & NORECOVERY option and the script will be</span></li>
</ul>
<div class="MsoNormal" style="mso-pagination: widow-orphan;">
<i style="mso-bidi-font-style: normal;"><span lang="EN-IN" style="color: #444444;">RESTORE DATABASE yourdatabasename FROM DISK = 'some path to full' WITH NORECOVERY;</span></i></div>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Now restore the transaction log using STOPAT and the script will be...</span></li>
</ul>
<div class="MsoNormal" style="mso-pagination: widow-orphan;">
<i style="mso-bidi-font-style: normal;"><span lang="EN-IN" style="color: #444444;">RESTORE DATABASE dbname FROM DISK = 'some path to transaction log' WITH RECOVERY STOPAT 'August 15, 2012 00:00:00.000';</span></i></div>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Check your records</span></li>
</ul>
<ul style="text-align: left;">
<li><span lang="EN-IN" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span><span lang="EN-IN">Now take the backup of your database WITH RECOVERY option and the script will be...</span></li>
</ul>
<div class="MsoNormal" style="mso-pagination: widow-orphan;">
<i style="mso-bidi-font-style: normal;"><span lang="EN-IN" style="color: #444444;">RESTORE DATABASE yourdatabasename WITH RECOVERY;</span></i></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">Example</span></b><span lang="EN-IN">: For easy understanding of all above steps, I want to mention an example and the example is written below:</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">RESTORE DATABASE employee-records from DISK='E:\BackupFiles\employee-records.bak'</span></b></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">WITH NORECOVERY;</span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">RESTORE LOG employee-records FROM DISK='E:\BackupFiles\employee-records.trn'</span></b></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">WITH NORECOVERY, STOPAT = 'August 15, 2012 04:00 PM';</span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">RESTORE DATABASE employee-records WITH RECOVERY;<span style="mso-spacerun: yes;"> </span></span></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-IN">Where database name is <b style="mso-bidi-font-weight: normal;">employee-records</b> & location of the database is <b style="mso-bidi-font-weight: normal;">E drive</b></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">Note</span></b><span lang="EN-IN">: Not forget to backup your transaction log as well as database.</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><span lang="EN-IN">Don’t have any prior backup before the deletion</span></b><span lang="EN-IN">: It is recommended to try any data recovery software for all those database administrators who have not maintained any backup before the deletion.</span></div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-45828299969799155442012-08-13T21:12:00.000-07:002013-11-17T19:18:39.238-08:00Unable to recover your SQL server database due to incomplete transaction<div dir="ltr" style="text-align: left;" trbidi="on">
Microsoft SQL server database is the mostly used database among other relational databases like Oracle, MySQL, Access, SQL Anywhere and many more. Database administrator can make backup of his/her database to avoid any data loss. If you don't have maintained the backup of your database then I will strongly recommend you to do it now because corruption in database can happen at any time any reasons. <br />
<br />
Suppose you have made a database and the name of database is myfirstdatabase. You have successfully maintained the backup of your database and want to restore it from the backup. You can do it with the help of RESTORE command but during the restore process you have canceled the restoring process and after the refreshing the database you have got bellow error message the states “the database can not be recovered because the log was not restored”. Now your database is in restoring state and you will be unable to do any operation on the database.<br />
<br />
Msg 4333, Level 16, State 1, Line 1<br />
The database cannot be recovered because the log was not restored.<br />
Msg 3013, Level 16, State 1, Line 1<br />
RESTORE DATABASE is terminating abnormally.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZj65ib60UHo1E0uP3utOnxwVOcI7NC7w6xshgL61LapYNTfBf9kzcLwc7rgjp3NpqV58fEn31LXWOcG6OOuYvOY6z3cfjxMHve6DJdhwa_4pMmTeANRGm3A7OeumXol1GkIsdQHTl1EE/s1600/restore.jpeg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZj65ib60UHo1E0uP3utOnxwVOcI7NC7w6xshgL61LapYNTfBf9kzcLwc7rgjp3NpqV58fEn31LXWOcG6OOuYvOY6z3cfjxMHve6DJdhwa_4pMmTeANRGm3A7OeumXol1GkIsdQHTl1EE/s200/restore.jpeg" width="200" /></a></div>
<br />
Here are the steps that you have done on your database:<br />
<br />
Create database myfirstdatabase<br />
Make backup myfirstdatabase.bak<br />
Restore database myfirstdatabase<br />
Got error message 4333 & 3013<br />
<br />
<b>How to Bring database online</b>? You have canceled the restoring process in the middle of restore so the entire database was not restored. To <a href="http://www.stellarsqldatabaserecovery.com/" rel="nofollow" target="_blank">bring your database online</a> you have to use WITH RECOVERY option with your database restore command and the command should be.<br />
<br />
Restore Database myfirstdatabase<br />
From disk = 'myfirstdatabase.bak'<br />
WITH RECOVERY <br />
<br />
<b>Note</b>: This time don't kill or cancel the restoring process in the middle. Keep patient and wait for the complete restoration. Now you will be able to restore your database successfully.</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-24320788845230277212012-08-09T21:25:00.000-07:002012-08-09T21:25:45.494-07:00How Many Rows are Present in a Table?<div dir="ltr" style="text-align: left;" trbidi="on"> <br />
<div lang="en-US" style="margin-bottom: 0cm;">Have your manager or team leader asked to you about how many rows are present in a particular table? To answer this question, you have two option, first one is connect with the database developer who has created the database and second one is run a script yourself. Sometimes database developer may not present due to some reason to answer your question at this situation second solution is best for you. If you are a Sr. Developer then its your responsibility to answer your manager or team leader. You can write down bellow script to find out the total numbers rows in particular table.</div><div lang="en-US" style="margin-bottom: 0cm;"><br />
</div><div lang="en-US" style="margin-bottom: 0cm;">USE Sample-Database-Name</div><div lang="en-US" style="margin-bottom: 0cm;">GO</div><div lang="en-US" style="margin-bottom: 0cm;">SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count</div><div lang="en-US" style="margin-bottom: 0cm;">FROM sys.dm_db_partition_stats st</div><div lang="en-US" style="margin-bottom: 0cm;">WHERE index_id < 2</div><div lang="en-US" style="margin-bottom: 0cm;">ORDER BY st.row_count DESC</div><div lang="en-US" style="margin-bottom: 0cm;">GO </div><div lang="en-US" style="margin-bottom: 0cm;"><br />
</div><div lang="en-US" style="margin-bottom: 0cm;"><b>Result</b>: This script shows a list with table name & row count. With the help of list you can able to answer your manager or team leader. </div><div lang="en-US" style="margin-bottom: 0cm;"><br />
</div><div lang="en-US" style="margin-bottom: 0cm;"><b>For example</b>: Here is a list of all tables present in a particular database with their total number of rows.</div><div lang="en-US" style="margin-bottom: 0cm;"><br />
</div><table border="1" bordercolor="#000000" cellpadding="4" cellspacing="0" style="width: 100%px;"><colgroup><col width="128*"></col> <col width="128*"></col> </colgroup><tbody>
<tr valign="TOP"> <td width="50%"> <div lang="en-US"><b>Table_Name</b></div></td> <td width="50%"> <div lang="en-US"><b>Row_Count</b></div></td> </tr>
<tr valign="TOP"> <td width="50%"> <div lang="en-US">Employee</div></td> <td sdnum="16393;" sdval="120001" width="50%"> <div lang="en-US">120001</div></td> </tr>
<tr valign="TOP"> <td width="50%"> <div lang="en-US">Local Address</div></td> <td sdnum="16393;" sdval="100201" width="50%"> <div lang="en-US">100201</div></td> </tr>
<tr valign="TOP"> <td width="50%"> <div lang="en-US">Permanent Address</div></td> <td sdnum="16393;" sdval="100381" width="50%"> <div lang="en-US">100381</div></td> </tr>
<tr valign="TOP"> <td width="50%"> <div lang="en-US">Customer List & .....</div></td> <td width="50%"> <div lang="en-US">202841 & ......</div></td> </tr>
</tbody></table><div lang="en-US" style="margin-bottom: 0cm;"><br />
</div></div>Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-25297453416853197292012-07-30T20:52:00.001-07:002013-11-17T19:19:48.683-08:00Pre-requirement, Restriction, Steps to Create & benefits for Database Mirroring<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="margin-bottom: 0cm;">
<span lang="en-US">In<a href="http://www.blogger.com/goog_1522199611"> </a></span><span lang="en-US"><a href="http://sql-server-recovery.blogspot.in/2012/03/database-mirroring-log-shipping-in-msft.html" rel="nofollow" target="_blank">previous article</a></span><span lang="en-US"> on 21 March 2012, we had discussed about Database mirroring & log shipping in MSFT SQL server, here we will discuss on following topics:</span></div>
<ol>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Pre-requirement for Database Mirroring</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Restriction for database mirroring</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Steps to Create Mirrored database</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Benefits of database Mirroring</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>1. Pre-requirement for Database Mirroring</b></span><span lang="en-US">: Before going to create a mirrored database for your database, you must perform below operation:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Principle database must use the full recovery model</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Make sure that the mirror server has sufficient disk space for the mirror database</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Make sure that both the server must be running on the same version of SQL server</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Make sure that you restore the backup of principle database with specifying the same database name with NORecovery</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>2. Restriction for database mirroring</b></span><span lang="en-US">: There are some restrictions also when you have decided to create a mirror database; bellows are the lists of restriction:</span></div>
<ul>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">You can create mirror database for the user database only, can not create mirror database for the master, model, tempdb & msdb.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
It does not support filestream filegroup. You can not configure mirror database for the database that contains filestream filegroup.</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
You can not rename mirrored database during the database mirroring session</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
You can not configure mirror database for the database that is using simple or bulk-logged recovery model</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>3. Steps to Create Mirrored database</b></span><span lang="en-US">: If you have decided to configure a mirror database for your desired database then follow bellow steps to configure:</span></div>
<ul>
<li><div lang="en-US" style="margin-bottom: 0cm;">
first of all, make sure that both servers principle & mirror have same version of sql server installed</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now verify that principle database uses full recovery model</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Backup the principle database to a full database backup</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Now verify that your desire mirror server has sufficient disk space to restore full database backup created using principle database</div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
To create mirror database, restore full database backup on the mirror server instance. Restore database statement must be specify with Restore Database your-database-name with NoRecovery</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>Note</b></span><span lang="en-US">: It is recommended that try to use identical path for the mirrored and principle database.</span></div>
<div lang="en-US" style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<span lang="en-US"><b>4. Benefits of database Mirroring</b></span><span lang="en-US">: Database mirroring is the part of SQL server high availability solution and offers following benefits:</span></div>
<ol>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Increase availability of your database: In case of any disaster, failover quickly brings mirrored database online without data loss or with minimum data loss. It minimizes the downtime of your database and helps in </span><span lang="en-US"><a href="http://www.stellarsqldatabaserecovery.com/" rel="nofollow" target="_blank">recovery of SQL database</a></span><span lang="en-US">.</span></div>
</li>
<li><div lang="en-US" style="margin-bottom: 0cm;">
Increase the data protection</div>
</li>
<li><div style="margin-bottom: 0cm;">
<span lang="en-US">Increase the availability of your production database during upgradation </span> </div>
</li>
</ol>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-35678406514100174122012-07-16T20:45:00.000-07:002013-11-17T19:20:40.937-08:00Need a Sample databases to analyze the new feature in SQL server 2012 - Adventureworks<div dir="ltr" style="text-align: left;" trbidi="on">
<title></title> <style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
A:link { so-language: zxx }
</style>
</div>
--> <div style="margin-bottom: 0cm;">
Are you eager to learn about how to work with SQL server database engine? For you, Microsoft SQL server offers several samples & sample databases to analyze the working functionality of SQL server database engine. There are several samples & sample databases are available.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Here is the list of Samples</b>:</div>
<ol>
<li><div style="margin-bottom: 0cm;">
Programmability Sample: Demonstrates the use of SQL server management objects</div>
</li>
<li><div style="margin-bottom: 0cm;">
XML Sample: demonstartes the use of use of XML </div>
</li>
<li><div style="margin-bottom: 0cm;">
Administration Sample: Demonstartes the use of administrative features</div>
</li>
<li><div style="margin-bottom: 0cm;">
Data Access Sample: Demonstrates the use of SQL server </div>
</li>
<li><div style="margin-bottom: 0cm;">
Full-text Search Sample: Helps in how to use full-text search</div>
</li>
<li><div style="margin-bottom: 0cm;">
Query Processing sample: Helps in how to use query processing</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Here is the list of sample databases that help in analyzing of SQL server database engine</b>:</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<ol>
<li><div style="margin-bottom: 0cm;">
Pubs Sample Databases: It was used in SQL server 2000.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Northwind sample databases: It is the best sample databases for SQL server 2000.</div>
</li>
<li><div style="margin-bottom: 0cm;">
Adventurework Sample databases: It is newly introduced sample databases for SQL server 2005 and replaced above two sample databases from SQL server 2005.</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
For security purpose, Microsoft does not include any of the samples or sample databases with SQL server. To learn about SQL server storage engine, you have to locate and download samples & sample databases. All the samples & sample databases are available at <a href="http://www.codeplex.com/sqlserversamples" rel="nofollow" target="_blank">Microsoft SQL server community project</a><span style="color: navy;"><span lang="zxx"></span></span>.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>Note</b>: If you want to install the samples then the default location of samples files is <i>C:\Program Files\Microsoft SQL Server\100\Samples\<technology_name></technology_name></i>.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
SQL server 2012 is the latest version of SQL server, if you want to test some new features in SQL server 2012 then download Adventureworks sample databases <a href="http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=165399" rel="nofollow" target="_blank">from here</a><span style="color: navy;"><span lang="zxx"></span></span>. After the successful download of adventureworks, you will have to attach the adventureworks sample databases.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
You have two option to attach adventureworks sample databases:</div>
<ol>
<li><div style="margin-bottom: 0cm;">
Attach Adventureworks using SQL server management studio</div>
</li>
<li><div style="margin-bottom: 0cm;">
Attach Adventureworks using T-SQL</div>
</li>
</ol>
<div style="margin-bottom: 0cm;">
<b>Attach Adventureworks using SQL server management studio</b>: To do this, you have to follow bellow steps:</div>
<ul>
<li><div style="margin-bottom: 0cm;">
Right click on the databases</div>
</li>
<li><div style="margin-bottom: 0cm;">
Select Attach and click Add</div>
</li>
<li><div style="margin-bottom: 0cm;">
Now select the adventureworks mdf file</div>
</li>
<li><div style="margin-bottom: 0cm;">
If got any error message for log file then simpally select & remove log file(ldf)</div>
</li>
<li><div style="margin-bottom: 0cm;">
Click Ok</div>
</li>
</ul>
<div style="margin-bottom: 0cm;">
<b>Attach Adventureworks using T-SQL</b>: If you want to attach a database with the transaction log file then you have to select attach_rebuild_log option. Attach_rebuild_log option automatically creates 1 MB transaction log file and placed this file in the default log file location.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>The command would be</b>:</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
CREATE DATABASE SampledatabasesAdventureWorks_Data</div>
<div style="margin-bottom: 0cm;">
ON (FILENAME = N'E:\SQLData\sampledatabasesAdventureWorks_Data.mdf')</div>
<div style="margin-bottom: 0cm;">
FOR ATTACH_REBUILD_LOG </div>
<div style="margin-bottom: 0cm;">
Go</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
<b>How to Verify the Integrity of Adventureworks sample Databases?</b></div>
<div style="margin-bottom: 0cm;">
You can verify logical & physical integrity of adventureworks sample databases by runing this query</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
DBCC CHECKDB ('SampledatabasesAdventureWorks_Data')</div>
<div style="margin-bottom: 0cm;">
GO</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-75174973035101340682012-07-05T02:45:00.002-07:002013-11-17T19:20:52.905-08:00Are you Unable to Start SQL server services due to Suspect tempdb database?<div dir="ltr" style="text-align: left;" trbidi="on">
Tempdb is a system database and has two files, one is tempdb log file (tempdb.ldf) & another is tempdb data file (tempdb.mdf). It is available for the all the database administrators that are connected to the instances of the SQL server. Tempdb is re-created every-time when you start the SQL server service. All the temporary tables and stored procedure of the tempdb are dropped automatically when a database administrator shutdown the SQL server that means there is no any record available in the tempdb database after SQL server shutdown. Database administrators use tempdb for storing temporary user objects, internal objects & row versions that are generated by data modification transaction. Here is the list of operations that can't perform on the tempdb system database:<br />
<ul style="text-align: left;">
<li><span style="font-family: Symbol;"></span>Can't perform Backup & Restore Operation on tempdb database</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Can't change the database ownership</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Can't run DBCC CHECKALLOC & CHECKCATALOC command on the database</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Can't set tempdb database offline</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Can't perform database mirroring & snapshot on the database</li>
</ul>
<div class="MsoNormal">
Sometime when you try to start SQL server services, you may unable to start because of tempdb <a href="http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html" rel="nofollow" target="_blank">database is marked as suspect</a> and may get below error message:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
“<b><i><span style="color: #cc0000;">Database 'tempdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information</span>.</i></b>”</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Reasons</b>: There are several reasons for above error message like 'file is missing', 'tempdb database structure corruption' and many more.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b>Steps to Fix</b>: You can easily fix above error message by creating a new tempdb for the database. For creating a new tempdb database, follow given below steps:</div>
<ul style="text-align: left;">
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>If tempdb database files exist then rename those files for example newname.ldf & newname.mdf.</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Start the SQL server from the command prompt by typing or pasting 'sqlservr -c -f -T3608 -T4022 command.</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span>Now connect with the server by using query analyzer.</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Reset the status of tempdb database by using 'exec master..sp_resetstatus Tempdb' stored procedure.</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Now you have to shutdown the server by pressing CTRL+C in the window command prompt.</li>
<li><span style="font-family: Symbol;"><span style="font-family: 'Times New Roman'; font-size: 7pt; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"></span></span>Restart the SQL server, now you have a new tempdb database & recover old tempdb database.</li>
</ul>
<div class="MsoNormal">
<b>Applied for</b>: All above steps are applied for SQL server 2005</div>
</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-1270123876726887192012-06-26T03:41:00.002-07:002013-11-17T19:21:03.213-08:00How to Easily Fix and Recover from Statblob Corruption in SQL Server 2000<div dir="ltr" style="text-align: left;" trbidi="on">
<title></title> <style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
</style>
</div>
--> <div style="margin-bottom: 0cm;">
SQL users are often plagued by various odd cases of database corruption. One of them is corruption of a ‘statblob’. A ‘statblob’ corruption error in SQL Server 2000 would normally look like:<br />
<br />
'<i>Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2:<br />
Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1</i>.'<br />
<br />
'Statblob' refers to an image column that stores statistics for an index. There is a row for each index in the 'sysindexes' table and the image column of this table is used to keep the statistics data for the corresponding index. So, it is very similar to an LOB column that is prone to corruption. As this type of corruption is inside the system table, it cannot be repaired using DBCC CHECKDB.<br />
<br />
In order to fix this, you need to drop the offending index. The first step towards this approach is to find out the name of the index (or statistic), the table corresponding to the index, and if it is an index or column statistics set. This information can be obtained by using the ‘object_id’ and ‘index_id’ given in the error message. You can run a query on the system tables as follows:<br />
<br />
‘<span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>SELECT</i></span></span></span><i> </i><span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>object_name(id) as</i></span></span></span><i> </i><span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>TableName, name</i></span></span></span><i> </i><span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>as</i></span></span></span><i> </i><span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>IndexName, IndexProperty (id, name, 'IsStatistics') AS IsColumnStatistics</i></span></span></span></div>
<div style="margin-bottom: 0cm;">
<span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>FROM</i></span></span></span><i> </i><span style="font-family: Courier New,monospace;"><span style="font-size: x-small;"><span style="font-size: small;"><i>sysindexes</i></span></span></span></div>
<div style="margin-bottom: 0cm; orphans: 2; widows: 2;">
<i>WHERE (id = 238623893 and indid = 1)’ </i><br />
<br />
The output will display the table name under ‘TableName’, index name under ‘IndexName’, and ‘0’ or ‘1’ under ‘IsColumnStatistics’. You should drop all the indexes and statistics shown in the results by running these commands:<br />
<br />
‘<i>DROP INDEX index_name ON table_name’ </i></div>
<div style="margin-bottom: 0cm;">
‘<i>DROP STATISTICS table_name.statistic_name’</i><br />
<br />
After completion of the process, you can try running CHECKDB in order to make sure that the corruption has been repaired. If the above process fails, you can restore the database from a recent clean backup.<br />
<br />
If you receive the same error again, you should use <a href="http://www.stellarsqldatabaserecovery.com/software-detail.php" rel="nofollow" target="_blank">commercial third-party SQL database recovery tools</a>. This software easily fix corruption in your SQL databases to retrieve all valuable objects, including tables, queries, views, stored procedures, etc. They recover databases for which DBCC CHECKDB fails to perform a successful repair. With the help of these utilities, you can perform selective recovery of database components and restore them to a new database at any desired location of choice. Further, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.</div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0tag:blogger.com,1999:blog-187998322205884696.post-37168663687875778862012-05-24T21:06:00.001-07:002013-11-17T19:21:13.360-08:00Easy Way to Change the Location of SQL Server Datafiles<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">Have you decided to change the location of your SQL server datafiles from default location to your specified location? If yes then I will guide to you for this throughout the article. Before going to change the location of your SQL server data files (mdf & ndf), you will have to check the current location of your data files. To do this run below query:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="background-color: white; font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">Select Name, Physical Name<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">From sys.master-files<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="background-color: white; color: #b45f06;">Where Name = “my-database-name”</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">Where “my-database-name” is the name of your database. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><b>Output of query</b>:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name.mdf<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name2.mdf<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\mysql-database-name3.mdf<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">The output of query shows that you database’s data files are stored in the C drive of your computer.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<strong><span style="font-size: 12pt; line-height: 115%;">Prerequisites</span></strong><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">: Before going to change the location of your data files, you have to put your database in office mode. You can do this by “<b>ALTER</b>” command:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="background-color: white; font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">ALTER Database my-database-name<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="background-color: white; font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">SET Offline<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="background-color: white; color: #b45f06;">Go</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<b><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">To change location of Data Files</span></b><span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">: You have to run “Alter” command again to change the location of data files. The queries are:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">ALTER Database my-database-name<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">Modify File <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">(<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">Name = my-databse-name,<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">FileName = ‘C\data\my-database-name.mdf’<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">) <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><span style="color: #b45f06;">Go</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;">Now use copy & paste or drag & drop method to change the location of data files. To verify this, you have to set your database Online.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><br />
</span></div>
<br />
<span style="font-family: 'Times New Roman', serif; font-size: 12pt; line-height: 115%;"><b>You may like to read: </b><span style="color: #333333;"><a href="http://sql-server-recovery.blogspot.in/2011/11/sql-server-data-transfer-from-one.html" rel="nofollow" target="_blank">SQL server Data Transfer from One server to another</a></span></span></div>
Mark Williumhttp://www.blogger.com/profile/14377582145723925411noreply@blogger.com0