tag:blogger.com,1999:blog-82378984559226213182024-03-05T15:46:30.803-08:00Microsoft SQL ServerTSQL, Database Mirroring, SSIS, Replication, SQL Server Reporting Services, troubleshoot & optimize SQL Server performance, Certifications MCTS, MCITP-DBAAdeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-8237898455922621318.post-50200372815813340002011-03-20T17:28:00.000-07:002011-03-20T17:28:05.893-07:00SQL Server 2008 Books and CertificationsDear friends,<br />
<div><br />
</div><div>While learning something new is difficult it is also fun. If you are a professional or seasoned SQL Server 2005 DBA/Developer you have lots of materials and books at your disposal for learning SQL Server 2008. You obviously can't read and learn all of them but a good selection will make you at ease. I recommend anyone who wants to learn SQL Server 2008 the following titles which I found useful. Please feel free to comment and add any more titles you really think are useful.<br />
<br />
</div><div><a href="http://download.microsoft.com/DOWNLOAD/C/D/1/CD1648C1-46BC-4C79-B16F-7AF650C186B4/693883EBOOK.PDF">Introducing Microsoft SQL Server 2008 R2 (Free E-Book Offer)</a><br />
<a href="http://download.microsoft.com/DOWNLOAD/C/D/1/CD1648C1-46BC-4C79-B16F-7AF650C186B4/693883EBOOK.PDF"></a><a href="http://www.amazon.com/Accelerated-Server-2008-Michael-Coles/dp/1590599691?ie=UTF8&tag=mic083-20&link_code=btl&camp=213689&creative=392969" target="_blank">Accelerated SQL Server 2008</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=mic083-20&l=btl&camp=213689&creative=392969&o=1&a=1590599691" style="border: none !important; margin: 0px !important; padding: 0px !important;" width="1" /> from Apress</div><div><a href="http://www.amazon.com/Pro-Server-2008-Reporting-Services/dp/1590599926?ie=UTF8&tag=mic083-20&link_code=btl&camp=213689&creative=392969" target="_blank">Pro SQL Server 2008 Reporting Services</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=mic083-20&l=btl&camp=213689&creative=392969&o=1&a=1590599926" style="border: none !important; margin: 0px !important; padding: 0px !important;" width="1" /> by Apress</div><div><br />
Personally I love to read Microsoft Self-Paced Training Kit books for detailed knowledge and certifications. See below:</div><div><br />
</div><div><b>MCTS and MCITP on SQL Server 2008</b></div><div>For details please visit the following link to download the Certification Path Document for SQL Server 2008 designed and published by Microsoft. It is updated with required exams and recommended trainings and books as well as MCM and MCA certifications.</div><div><a href="http://download.microsoft.com/download/9/6/6/96692542-EC52-4E11-91A5-5106990E13E4/SQLServer2008CertificationPath.pdf">SQL Server 2008 Certification Path<br />
</a></div>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com6tag:blogger.com,1999:blog-8237898455922621318.post-8616523878514533232009-10-07T12:16:00.000-07:002009-10-07T12:16:43.595-07:00Edition Change Check (Warning)Edition Change Check (Warning)<br />
<br />
This is the most common warning message reported on Windows Vista and Windows 7 related to SQL Server installation. There are various reasons for this message but the most common issue is to run the setup from the same DVD from which you first installed SQL Server 2005. I have faced this issue several times on different machines running Windows Vista and Windows 7 Evaluation build 7100. My findings are as follows:<br />
<br />
Whenever you run SQL Server setup.exe on a Vista or Windows 7 machine the system configuration checker checks the system it issues this warning (Edition Change Check). No matter what Edition of SQL Server is installed on the system perviously. The prime reason behind this is that you have installed and applied service packs on SQL Server instance and its components. The installer you are running first gives you the compatibility warning and once you ignore it and run the setup, it will give you Edition change check warning. There are two approaches to this:<br />
<br />
1. Ignore the message and continue<br />
2. Run the SQL Server 2005 Setup from a command line and provide the upgarede option as follows:<br />
<br />
Setup.exe SKUUPGRADE = 1<br />
<br />
This will run the setup with upgrade option and the Edition Check warning disappears. See below:<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkYUOxWzqeo6oMGxeJxOgk-GkBR6BfwCj8rsr2A4rQHoNNuBCSZrnBui9z8QYdJBTd03YMCkVgR-TwVR3RNnnqyt9aCxlaa1iA42Q8q0aqpY8CKtWCgd_FMARzOcFVE9L-LpsEZCRjn30/s1600-h/Success_EditionChange.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkYUOxWzqeo6oMGxeJxOgk-GkBR6BfwCj8rsr2A4rQHoNNuBCSZrnBui9z8QYdJBTd03YMCkVgR-TwVR3RNnnqyt9aCxlaa1iA42Q8q0aqpY8CKtWCgd_FMARzOcFVE9L-LpsEZCRjn30/s400/Success_EditionChange.jpg" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Hope it helps.Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com6tag:blogger.com,1999:blog-8237898455922621318.post-82537225800520313392009-10-07T12:00:00.000-07:002009-10-07T12:06:50.250-07:00Unhandled Exception, Error while switching panels, SSRS, SQL Server 2005 Reporting ServicesI tried to add reporting services today to my existing SQL Server 2005 instance on Windows 7. I installed it successfully however upon first run I faced an Unhandled Exception in WMI Component. Also when I clicked Server Status or any other option it said unable to switch panels WMI error again. Well upon googling it for a while I found nothing useful and thought to dig it myself. I came to the conclusion that there must be some difference between Service packs. I queried my server and found that I had installed SQL Server 2005 Service Pack 3 (SP3). However reporting service has just been installed and the SP3 wasn’t applied to it. Here is how I resolved:<br />
<br />
<br />
<strong>Problem:</strong><br />
<br />
1. Error I faced upon first run of SSRS Configuration Manager<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnJ_s8Q-yLpm7bWo_yQrqWZa2Hmuaki8VE10EotmAcxiMnMZVQ_kA2Z2Eterc1y4VHX5t7ombim7OjUxTvu-nqNMyS6REGarVVQCo4n7_rVY2Je06BgLRNmaPSuauM4FijEow0NnOz4aQ/s1600-h/Error1.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnJ_s8Q-yLpm7bWo_yQrqWZa2Hmuaki8VE10EotmAcxiMnMZVQ_kA2Z2Eterc1y4VHX5t7ombim7OjUxTvu-nqNMyS6REGarVVQCo4n7_rVY2Je06BgLRNmaPSuauM4FijEow0NnOz4aQ/s320/Error1.jpg" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
2. Second Error I faced when I clicked on the panel on left side<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF-EUvgYeOkCMtyd7HvNhLkP1M-OuDIC3gQQxC6BL8gVuVeqlyFxc-15fxVgw7K-wl3V_o2rSpn-lUOhKX7OV88eqIhSZmu5-U0jLEe-VGOW6IjzbR4Lm98ubmNulMU_y7XLwXySX8Rco/s1600-h/Error2.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF-EUvgYeOkCMtyd7HvNhLkP1M-OuDIC3gQQxC6BL8gVuVeqlyFxc-15fxVgw7K-wl3V_o2rSpn-lUOhKX7OV88eqIhSZmu5-U0jLEe-VGOW6IjzbR4Lm98ubmNulMU_y7XLwXySX8Rco/s320/Error2.jpg" /></a><br />
</div> <br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<strong>Resolution:</strong> <br />
1. Download the relevant service pack you have already applied to SQL Server or most probably you already have it as you have installed it before.<br />
<br />
2. In my case I had applied SP3 before I installed SSRS. You can download it here:<br />
<br />
3. http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en<br />
<br />
4. You can download the versions for x86, x64 or IA64 versions depending upon you hardware and OS platform. I download this one SQLServer2005SP3-KB955706-x86-ENU<br />
<br />
5. Once downloaded double click it and you will see the following screen:<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ36yR194W1sF_Zr_-rWkSKDkrQCu6vYdq5b_RbwRTr2ly0PP7D5Tl1BPX6DMPBQTAayjVf5KLTboGv9sbVwC3slskyOedE_kEyfL2QHltY5waUB-3RUCWBgvop_1-yux2TBza0opJJEw/s1600-h/Step1.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJ36yR194W1sF_Zr_-rWkSKDkrQCu6vYdq5b_RbwRTr2ly0PP7D5Tl1BPX6DMPBQTAayjVf5KLTboGv9sbVwC3slskyOedE_kEyfL2QHltY5waUB-3RUCWBgvop_1-yux2TBza0opJJEw/s320/Step1.jpg" /></a><br />
</div> <br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
6. By default the checkboxes will be selected for whatever component the service pack wasn’t applied and in our case its Reporting Services <br />
7. Hit Next<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRkVcemcY2kDzyt3-37B2v0Gydp6ihyphenhyphen3VmGUgw-3JN44r_Q_XPm9D4eszZ5PVUzPCA4765vGVDaBR-dPXEAAsJaluJLhGH59txPtnQXs7h0orMBPd7sEeHthx7CWct_abx1XXblSubOZI/s1600-h/Step2.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRkVcemcY2kDzyt3-37B2v0Gydp6ihyphenhyphen3VmGUgw-3JN44r_Q_XPm9D4eszZ5PVUzPCA4765vGVDaBR-dPXEAAsJaluJLhGH59txPtnQXs7h0orMBPd7sEeHthx7CWct_abx1XXblSubOZI/s320/Step2.jpg" /></a><br />
</div> <br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
8. Once the service pack is applied you would see the above screen hit Next again then the following screen<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIZT-j1f2EmCGG6D9-vEPShG59amekiAub9rUsDXVEL9oh_XhQZgIq3C1UgQwXAFVhD3q-BT_KCiA6kixdJ0ErKr7CxRV9i1rpaa8qW4U2syIqlcwDHvaEKzHRiAPXEWebBwQ_v-0DceU/s1600-h/Step3.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIZT-j1f2EmCGG6D9-vEPShG59amekiAub9rUsDXVEL9oh_XhQZgIq3C1UgQwXAFVhD3q-BT_KCiA6kixdJ0ErKr7CxRV9i1rpaa8qW4U2syIqlcwDHvaEKzHRiAPXEWebBwQ_v-0DceU/s320/Step3.jpg" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
9. The last screen will be the following. You don’t need to apply windows vista provision tool at this stage since you must have already added your windows user to sysadmin group using this tool when you first installed SQL Server2005 on Vista or Windows 7. If not then do that too<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJgZPnmoqEC_JBgDHcG_QD6yT46_bW-icTWdoirETCfv1Q6dSB1lh10LUd1IjWEyfB3g8yx6MLjHH232jRTU20-iB9_JJYbEGT9EbSNejSrs7PnDZ95r4KU3cStzdkNLQkdSQFWrBQlko/s1600-h/Step4.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img $r="true" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJgZPnmoqEC_JBgDHcG_QD6yT46_bW-icTWdoirETCfv1Q6dSB1lh10LUd1IjWEyfB3g8yx6MLjHH232jRTU20-iB9_JJYbEGT9EbSNejSrs7PnDZ95r4KU3cStzdkNLQkdSQFWrBQlko/s320/Step4.jpg" /></a><br />
</div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Hope it Helps.Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-79155142199627748992009-10-07T03:26:00.000-07:002010-03-21T10:56:04.951-07:00IIS Feature Requirements (Warning) Windows 7 and VistaI had installed SQL Server 2005 on Windows 7 and applied SP3. Everything was working fine till today when i decided to install Reporting Services (SSRS). I installed IIS from Control Panel -> Programs and Features -> Turn Windows Features On or Off. Then i ran SQL Server steup and got to the point of System Configuration Checker. It gave me the IIS Feature Requirements (Warning) and when i tried to continue i saw Reporting Services Cehckbox disabled. Upon searching through the internet i found a very useful link which was the only one that fixed my problem. Thanks to Martin Poon Microsoft MVP whose blog helped me. The link to his blog is:<br />
<a href="http://msmvps.com/blogs/martinpoon/default.aspx">http://msmvps.com/blogs/martinpoon/default.aspx</a><br />
<br />
and the link to his article is:<br />
<a href="http://msmvps.com/blogs/martinpoon/archive/2006/12/08/installation-of-sql-server-2005-in-windows-vista-warning-message-on-iis-feature-requirement-during-system-configuration-check.aspx">http://msmvps.com/blogs/martinpoon/archive/2006/12/08/installation-of-sql-server-2005-in-windows-vista-warning-message-on-iis-feature-requirement-during-system-configuration-check.aspx</a> <br />
<br />
I am rewriting this article for my own reference and for others who need help in this scenario and becauase the background and text color of Martin's blog for this article made it very difficult to read so i had to select all the text and then read it properly.<br />
<br />
When you run the SQL Server install the system configuration checker displays the following message:<br />
Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.<br />
<br />
Steps to Resolve the Issue:<br />
<br />
Applies to Windows 7 and Vista:<br />
<ol><li>Start</li>
<ol><li>Control Panel</li>
<li>Programs and Features</li>
<li>Turn Windows features on or off</li>
<li>Enable <internet information="" services=""></internet></li>
</ol>
<li>Enable various features of IIS</li>
<ol><li>Web management tools</li>
<li>IIS 6 Management Compatibility</li>
<ol><li>IIS 6 WMI Compatibility</li>
<ol><li>IIS Metabase and IIS 6 configuration compatibility</li>
</ol>
<li>World Wide Web Services</li>
<ol><li>Application Development Features</li>
<ol><li>ASP.NET</li>
<li>ISAPI Extensions</li>
<li>ISAPI Filters</li>
</ol>
<li>Common Http Features</li>
<ol><li>Default Document</li>
<li>Directory Browsing</li>
<li>HTTP Redirection</li>
<li>Static Content</li>
</ol>
<li>Security</li>
<ol><li>Windows Authentication</li>
</ol></ol></ol></ol>
<li>Now run the SQL Server 2005 Setup again</li>
</ol><div class="separator" style="clear: both; text-align: left;"></div><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA3ssMi-A0TJlxzlFlrAbDIXHQ3DCSy1DfuRpD4wWnFM3uZ1YGBRIQ0VWj6k3Gh-T6lctkxxW-y-ifg2wAiAVjUV7vlbPluzfucoVOE__t7CMiynygK1Qope74a9pI8rPNL7Z6AMrBGD4/s1600-h/IIS_Features_Selection.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhA3ssMi-A0TJlxzlFlrAbDIXHQ3DCSy1DfuRpD4wWnFM3uZ1YGBRIQ0VWj6k3Gh-T6lctkxxW-y-ifg2wAiAVjUV7vlbPluzfucoVOE__t7CMiynygK1Qope74a9pI8rPNL7Z6AMrBGD4/s400/IIS_Features_Selection.jpg" width="400" /></a></div><br />
Hope this would be helpful.Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com52tag:blogger.com,1999:blog-8237898455922621318.post-43230063189554764232009-09-17T17:28:00.000-07:002009-09-18T00:34:39.747-07:00Cannot connect to SQL Server 2005 on VistaIf you are reading this article it means you already ran into the problem of connection (login) issue with SQL Server 2005 on Windows Vista. Let me explain the scenario:<br />
<br />
I opened up SQL Server Management Studio and tried to log in using Windows Authentication but SQL Server came up with login failed error (Login Failed for user blabla, Error: 18456)<br />
I tried using SQL Server Authentication and typed 'sa' (without quotes obviously) as username and the password. To my surprise i faced another error that the user sa is not a trusted user. Now i was wondering how to log in and change the settings or reset the passwords. The "sa" account was disabled and windows authentication was not working as required. I thought maybe due to my changing the password for my user account it happend, so I changed my windows account password back to the previous one however the attempt to connect to SQL Server was again failed. After a little thought I came to the conclusion that Windows Vista User Account Control is the culprit here.<br />
So what i did was as follows:<br />
<br />
1. Right click the shortcut or executable for SQL Server Management Studio and Select Run as Adminsitrator<br />
2. Select the server name for SQL Server installed on the machine<br />
3. Select Windows Authentication and click Connect. Vola! it worked this time.<br />
4. In Object Manager Click Security -> Logins Right click Logins and select New Login<br />
5. Search and add your windows account. e.g: ComputerName\TestUser<br />
6. Select Server Roles on the Left and check sysadmin box on the right at the very bottom of defined roles<br />
7. Click OK. That's it, you are done now.<br />
8. Also remember to enable "sa" account and give it a strong password if you are using Mixed Mode Authentication.<br />
<br />
I hope it helps.Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-33624678198784718472008-08-01T13:04:00.000-07:002008-08-01T13:16:04.463-07:00Analyze SQL Server Performance Graphically<div align="justify">SQL Server 2005 Performance Dashboard utility will alow you to have a comprehensive glance of Server performance. If you are not familiar with DMVs to sort out performance bottelnecks this tool is for you. But don't forget learning and interpreting the output of DMVs will give you the most accurate and detailed analysis.</div><div align="justify"> </div><div align="justify"><a name="Description">The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.</a></div><div align="justify"> </div><div align="justify">Common performance problems that the dashboard reports may help to resolve include:</div><div align="justify"> </div><ul><li><div align="justify">CPU bottlenecks (and what queries are consuming the most CPU)</div></li><li><div align="justify">IO bottlenecks (and what queries are performing the most IO)</div></li><li><div align="justify">Index recommendations generated by the query optimizer (missing indexes)</div></li><li><div align="justify">Blocking</div></li><li><div align="justify">Latch contention</div></li></ul><div align="justify">The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.Reporting Services is not required to be installed to use the Performance Dashboard Reports.</div><div align="justify"> </div><div align="justify">Click the link below to download it from Microsoft website.</div><div align="justify"> </div><div align="justify"><a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en</a></div><div align="justify"> </div><div align="justify">System Requirements<br />Supported Operating Systems: Windows Server 2003; Windows Vista; Windows XP<br />SQL Server 2005 Service Pack 2 or later</div><div align="justify"> </div><div align="justify">Additional Information<br /><a name="AdditionalInfo"></a>The SQL Server instance being monitored must be running SP2 or later. After completing the installation, you must:1. Run the Setup.sql file on each instance of SQL Server 2005 that you wish to monitor with the SQL Server 2005 Performance Dashboard Reports.2. Open the performance_dashboard_main.rdl file with the Custom Reports functionality new to Management Studio in Service Pack 2.</div><div align="justify"> </div><div align="justify">Also you can read the PerfDash.chm that is located in the installation directory.</div><div align="justify"> </div><div align="justify">Have fun!</div>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-64325524587159647232008-07-23T13:45:00.000-07:002008-07-23T13:47:46.479-07:00TechEd 2008 Demonstration of SQL Server 2008Here is the youtube video for Microsoft TechEd 2008 where they demonstrate MS SQL Server 2008 features.<br /><br /><object height="344" width="425"><param name="movie" value="http://www.youtube.com/v/gfzEZTiGNIc&hl=en&fs=1"><param name="allowFullScreen" value="true"><embed src="http://www.youtube.com/v/gfzEZTiGNIc&hl=en&fs=1" type="application/x-shockwave-flash" allowfullscreen="true" width="425" height="344"></embed></object>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com0tag:blogger.com,1999:blog-8237898455922621318.post-53449751043352751722008-07-21T09:19:00.000-07:002008-07-21T09:31:23.917-07:00SQL Server 2005 Reporting Services Video TutorialHere is the youtube video for setting up SQL Server 2005 Reporting Services.<br />Part 1.<br /><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/aafHygddNqc&hl=en&fs=1"></param><param name="allowFullScreen" value="true"></param><embed src="http://www.youtube.com/v/aafHygddNqc&hl=en&fs=1" type="application/x-shockwave-flash" allowfullscreen="true" width="425" height="344"></embed></object>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-4532509782302889622008-07-15T07:24:00.000-07:002008-07-21T09:31:37.618-07:00Installing a SQL Server 2005 Instance<span style="font-family:times new roman;"><strong>Learn how to install SQL Server 2005 instance with screenshots. (for newbies)</strong></span><br /><strong>Video</strong><br /><br /><object width="425" height="344"><param name="movie" value="http://www.youtube.com/v/5Yq75lkXtL4&hl=en&fs=1"></param><param name="allowFullScreen" value="true"></param><embed src="http://www.youtube.com/v/5Yq75lkXtL4&hl=en&fs=1" type="application/x-shockwave-flash" allowfullscreen="true" width="425" height="344"></embed></object><br /><br />1. Start the Microsoft SQL Server Installation Wizard by running the Setup application<br />using the menu that appears when you insert the CD. On the End User<br />License Agreement page, select the I Accept The Licensing Terms And Conditions<br />check box and click Next.<br />2. On the Installing Prerequisites page, click Next when the installation of the<br />required components completes.<br />3. On the Welcome To The Microsoft SQL Server Installation Wizard page, click<br />Next.<br />4. Verify the completion of the System Configuration Checker.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6QmoW_aC9Linlo48-RJ6ijlHWPqacJ2y5Yw6wk3lr3Ke64_GlIazvbWBfArzqJwaLg2eW4S4H4OxyV3tWbN3ifH6gA8Pxlnid7iLi593-MH2_0t5WLFMrhuK19T40un5xtxNZVSCW8X8/s1600-h/1.JPG"><img id="BLOGGER_PHOTO_ID_5223253287153644946" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6QmoW_aC9Linlo48-RJ6ijlHWPqacJ2y5Yw6wk3lr3Ke64_GlIazvbWBfArzqJwaLg2eW4S4H4OxyV3tWbN3ifH6gA8Pxlnid7iLi593-MH2_0t5WLFMrhuK19T40un5xtxNZVSCW8X8/s320/1.JPG" border="0" /></a><br /><br /><br />5. Click Next.<br />6. Fill in the appropriate user and organization name in the text boxes<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Qs7nJj6ba6hnQI43EeLwKMhTa7fMDcVrz4YVytdu9d0Y7QQBFiKqPTYmk7WLxQIE_Gzz1ElnxbVdbBkPOuPQJUo2zEGu8VwPbJ8ZYCNcRxlp5i6HJwbzpebq0jEm0nePAqsIznYRrFE/s1600-h/2.JPG"><img id="BLOGGER_PHOTO_ID_5223259070392050402" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Qs7nJj6ba6hnQI43EeLwKMhTa7fMDcVrz4YVytdu9d0Y7QQBFiKqPTYmk7WLxQIE_Gzz1ElnxbVdbBkPOuPQJUo2zEGu8VwPbJ8ZYCNcRxlp5i6HJwbzpebq0jEm0nePAqsIznYRrFE/s320/2.JPG" border="0" /></a><br /><br />7. Click Next.<br />8. Select the appropriate components to install by checking the box next to the<br />component. For this practice, you should install all the available components<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXfeE3dMeO_Pv0-dxsW8GSIJYHSVqsJesW7eVfDuL-FQJnobcHjrVNLIXOz-AI6aKiiNaPInbvxTUq7snKos4kzyfZE3cLV7VCSLNDcCzPfD1jwR4fbI72zh_k4b8k7Bj2dyNeG31Gnks/s1600-h/3.JPG"><img id="BLOGGER_PHOTO_ID_5223260064969061842" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXfeE3dMeO_Pv0-dxsW8GSIJYHSVqsJesW7eVfDuL-FQJnobcHjrVNLIXOz-AI6aKiiNaPInbvxTUq7snKos4kzyfZE3cLV7VCSLNDcCzPfD1jwR4fbI72zh_k4b8k7Bj2dyNeG31Gnks/s320/3.JPG" border="0" /></a><br /><br />9. Click Advanced. Expand the Documentation, Samples, And Sample Databases tree.<br />10. From the drop-down list associated with Sample Databases select Entire Feature Will Be Installed On Local Hard Drive.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0xcYLuO7o4fmhgWroXs3voD5ZtDtAEakHXpwuiLjWR9DBa4FuyOTEEPV50U1zogUhSYNetgaxahqbxrtOcTpLsocohbrPQ73YRg6yKN6ikGbz3amMWXKriau6Nw_5xrdGaSW6cSGUHa4/s1600-h/4.JPG"><img id="BLOGGER_PHOTO_ID_5223261056783831986" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0xcYLuO7o4fmhgWroXs3voD5ZtDtAEakHXpwuiLjWR9DBa4FuyOTEEPV50U1zogUhSYNetgaxahqbxrtOcTpLsocohbrPQ73YRg6yKN6ikGbz3amMWXKriau6Nw_5xrdGaSW6cSGUHa4/s320/4.JPG" border="0" /></a><br /><br />11. Click Next.<br />12. Determine which instance names are already in use on the server by clicking<br />Installed Instances, which displays the window<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDwCijiLopKHwspT_6hbS0es11S4tOn71-DvAFMH43uj7cx4fVv2wjrHky1f6RG_AOLE79zjuiP48auWI5sXzOfqfcWwIS2paAed63BOje5AsI6Y47gpxcIfgOPbcqM5sBY-L-VPsd9BQ/s1600-h/5.JPG"><img id="BLOGGER_PHOTO_ID_5223261488144073618" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDwCijiLopKHwspT_6hbS0es11S4tOn71-DvAFMH43uj7cx4fVv2wjrHky1f6RG_AOLE79zjuiP48auWI5sXzOfqfcWwIS2paAed63BOje5AsI6Y47gpxcIfgOPbcqM5sBY-L-VPsd9BQ/s320/5.JPG" border="0" /></a><br /><br />13. Click OK.<br />14. Install a default instance<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBF6SnJoUtD7hDdSe57lnftqcY4M1Fn04hbXbAvoZFQHux472bRBcfANpVezg8xd60KekIe3q6QXbq8uEN8lJyHPAfLEbKyUt4id5n2RZNeq2xxo-q12a-o8U3kkZ_HAZEOJKdGtfBpB4/s1600-h/6.JPG"><img id="BLOGGER_PHOTO_ID_5223262026708641138" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBF6SnJoUtD7hDdSe57lnftqcY4M1Fn04hbXbAvoZFQHux472bRBcfANpVezg8xd60KekIe3q6QXbq8uEN8lJyHPAfLEbKyUt4id5n2RZNeq2xxo-q12a-o8U3kkZ_HAZEOJKdGtfBpB4/s320/6.JPG" border="0" /></a><br /><br />15. Click Next.<br />16. Review the components that will be installed and click Next.<br />17. Configure the service account information and the component startup information<br />by selecting Use The Built-In System Account and choosing Local System<br />from the drop-down list<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoio-oLT_ED2H_ft5Kh54sLxU5WhZ2CepU2eb86tpYDFRvP1qvh4THUAM9D7HXG6G2uT2KWN3NaXsHNe-NLgpjPsC2m__HpJBfFGh1ELwcJNgMaMR_sQPhaP_-gn_Id5DjRCSKlzPpCtA/s1600-h/7.JPG"><img id="BLOGGER_PHOTO_ID_5223262028614541858" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoio-oLT_ED2H_ft5Kh54sLxU5WhZ2CepU2eb86tpYDFRvP1qvh4THUAM9D7HXG6G2uT2KWN3NaXsHNe-NLgpjPsC2m__HpJBfFGh1ELwcJNgMaMR_sQPhaP_-gn_Id5DjRCSKlzPpCtA/s320/7.JPG" border="0" /></a><br /><br />18. Select the SQL Server Agent check box.<br />19. Click Next.<br />20. Configure the authentication mode by selecting Mixed Mode (Windows<br />Authentication And SQL Server Authentication)<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMr-ZLTxuz0uldypkiekv4Y4TzLiqWe5mNTT0cDh3plv1OE9A3gObQlkLxClD-fuSEgkueAViAJyKe9426s5TJjKVwy9-hJgfM-dpstydh4H0K-9Hbu_T21JeKENZAGSwW1CR633t7EhE/s1600-h/8.JPG"><img id="BLOGGER_PHOTO_ID_5223262788012925858" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMr-ZLTxuz0uldypkiekv4Y4TzLiqWe5mNTT0cDh3plv1OE9A3gObQlkLxClD-fuSEgkueAViAJyKe9426s5TJjKVwy9-hJgfM-dpstydh4H0K-9Hbu_T21JeKENZAGSwW1CR633t7EhE/s320/8.JPG" border="0" /></a><br /><br />21. Enter a strong password in the password text boxes. This password is used by<br />the sa login ID.<br />22. Click Next.<br />23. Review the default collation settings<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM44GiYj3T_Sya7gevPder7n41CxrV1FMhmVlKUaUk4lNyVMs3-Be7NFehjtmSB4U7WoLFSodkiOPaYRUmDaDJ0i2Sb60SGGAb7plyKiAjSV1fonUrzYzl0bYPFbifQyNW_Kcnw0R7DSw/s1600-h/9.JPG"><img id="BLOGGER_PHOTO_ID_5223262788316410834" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhM44GiYj3T_Sya7gevPder7n41CxrV1FMhmVlKUaUk4lNyVMs3-Be7NFehjtmSB4U7WoLFSodkiOPaYRUmDaDJ0i2Sb60SGGAb7plyKiAjSV1fonUrzYzl0bYPFbifQyNW_Kcnw0R7DSw/s320/9.JPG" border="0" /></a><br /><br />24. Click Next.<br />25. Configure error and usage reporting by selecting both the Automatically Send<br />Error Reports and Automatically Send Feature Usage Data check boxes<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1LO_3Q8ZdjEUSckJ8T7eKURVmpO4BUUEJ0xMzqd58RusP382lFNz-Z7MKT_o9qicdMQ8OYlO8EMRYjcxvC9lJkzzlxhFWqL3uaahSaegQmrU1Yu3A3sDytvs9yL83JCMVm6XpVPjvssI/s1600-h/10.JPG"><img id="BLOGGER_PHOTO_ID_5223263684142480994" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1LO_3Q8ZdjEUSckJ8T7eKURVmpO4BUUEJ0xMzqd58RusP382lFNz-Z7MKT_o9qicdMQ8OYlO8EMRYjcxvC9lJkzzlxhFWqL3uaahSaegQmrU1Yu3A3sDytvs9yL83JCMVm6XpVPjvssI/s320/10.JPG" border="0" /></a><br /><br />26. Click Next.<br />27. Verify which components will be installed<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimEK6xbicGXnIFCvLjlwPeWS0KlPsycTrckz6jqYrxzE6GXvp1u-XpSrpbRLAhyphenhyphenR1lUFipt2kbMEcMtOUWKtOluI8Rzs69RtiCCJxB7y_HqIKZjudxruCIG_B3PNx_f7UWLemkJPdKe-I/s1600-h/11.JPG"><img id="BLOGGER_PHOTO_ID_5223263683079814738" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimEK6xbicGXnIFCvLjlwPeWS0KlPsycTrckz6jqYrxzE6GXvp1u-XpSrpbRLAhyphenhyphenR1lUFipt2kbMEcMtOUWKtOluI8Rzs69RtiCCJxB7y_HqIKZjudxruCIG_B3PNx_f7UWLemkJPdKe-I/s320/11.JPG" border="0" /></a><br /><br />28. Click Install.<br />29. Verify that setup is complete<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKJqgBvb99jzlApFhWaph49RWVagG6IWEnAywkTRehbGZiRDlHwQG_UJGSYGYW6uNfOk5vZGg7UvjTjtoSDCxjkHrodnQHhpCm4fKmZLO_adY9FGYsOKsdGIlWGffnYp1eF186cUnLNsA/s1600-h/12.JPG"><img id="BLOGGER_PHOTO_ID_5223263696540123890" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKJqgBvb99jzlApFhWaph49RWVagG6IWEnAywkTRehbGZiRDlHwQG_UJGSYGYW6uNfOk5vZGg7UvjTjtoSDCxjkHrodnQHhpCm4fKmZLO_adY9FGYsOKsdGIlWGffnYp1eF186cUnLNsA/s320/12.JPG" border="0" /></a><br /><br />30. Click Next and then review the completion report.<br />31. Click Finish.Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-60062566468781205682008-07-01T04:38:00.000-07:002008-07-01T06:33:43.308-07:00Recreate Corrupt MSDB database<p align="justify">If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...<br /></p><p align="justify">Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.<br /></p><p align="justify">Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.<br /></p><p align="justify">So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):<br /></p><p align="justify">Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608<br /></p><p align="justify">Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)<br /></p><p align="justify">Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory<br /></p><p align="justify">Shutdown and restart the server without the 3608 trace flag<br /></p><p align="justify"></p><p align="justify">This works on SQL Server 2000 as well.</p>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com15tag:blogger.com,1999:blog-8237898455922621318.post-19470935490003275862008-07-01T04:31:00.000-07:002008-07-05T13:39:04.231-07:00MCITP - DBA<div align="justify">Microsoft Certified IT Professional: Database Administrator<br />(MCITP: Database Administrator) is the premier certification for database server administrators. This certification demonstrates that you can keep up with your enterprise business solutions 24 hours a day, 7 days a week.<br /><br /><strong>Exam 70-431</strong> <a href="http://gatesdemon.wordpress.com/learning/exams/70-431.mspx">Review the preparation guide for Exam 70–431</a>: TS: Microsoft SQL Server 2005 – Implementation and Maintenance<br /><strong>Exam 70-443</strong> <a href="http://gatesdemon.wordpress.com/learning/exams/70-443.mspx">Review the preparation guide for Exam 70-443</a>: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005<br /><strong>Exam 70-444 </strong><a href="http://gatesdemon.wordpress.com/learning/exams/70-444.mspx">Review the preparation guide for Exam 70-444</a>: PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005<br /><br /><strong>Need Learning Resources</strong><br /><br />CBT Nuggets 70-431, CBT Nuggets 70-443, CBT Nuggets 70-444<br /><a href="http://rapidshare.com/files/126172774/CBT_70-444_1.zip">http://rapidshare.com/files/126172774/CBT_70-444_1.zip</a></div><div align="justify"><a href="http://rapidshare.com/files/126391794/CBT_70-444_2.zip">http://rapidshare.com/files/126391794/CBT_70-444_2.zip</a></div><div align="justify"><a href="http://rapidshare.com/files/126616018/CBT_70-443.part1.rar">http://rapidshare.com/files/126616018/CBT_70-443.part1.rar</a></div><div align="justify"><a href="http://rapidshare.com/files/127415670/CBT_70-443.part2.rar">http://rapidshare.com/files/127415670/CBT_70-443.part2.rar</a> </div><div align="justify">Latest Practice Test: Testking, Pass4sure and Actual Test<br />Link: <a href="http://www.discussbits.com/forum">http://www.discussbits.com/forum</a><br />TorrentDownloads: <a href="http://www.torrentspider.org/">http://www.torrentspider.org/</a></div>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com1tag:blogger.com,1999:blog-8237898455922621318.post-41183354367464814132008-07-01T04:27:00.000-07:002008-07-01T06:34:45.522-07:00Fixing Suspect DB SQL Server<div align="justify">Steps to Fix a suspect Database<br /><br />1. Back up the .mdf/.ndf files at first!!!<br />2. Change the database context to Master and allow updates to system tables:<br /><br />Use Master<br />Go<br />sp_configure ‘allow updates’, 1<br />reconfigure with override<br />Go<br /><br />3. Set the database in Emergency (bypass recovery) mode:<br /><br />select * from sysdatabases where name = "<br />– note the value of the status column for later use in # 6<br /><br />begin tran<br />update sysdatabases set status = 32768 where name = "<br /><br />– Verify one row is updated before committing<br />commit tran<br /><br />4. Stop and restart SQL server.<br />5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the suspected db.<br />The syntax for DBCC REBUILD_LOG is as follows:<br /><br />DBCC rebuild_log(",")<br /><br />where is the name of the database and is the physical path to the new log file, not a logical file name. If you do not specify the full path, the new log is created in the Windows NT system root directory (by default, this is the Winnt\System32 directory).<br /><br />6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency:<br /><br />sp_dboption ", ’single user’, ‘true’<br /><br />OR<br /><br />Alter Database databasename SET SINGLE_USER<br />DBCC checkdb(")<br />Go<br /><br />begin tran<br />update sysdatabases set status = where name = "<br /><br />– verify one row is updated before committing<br />commit tran<br />Go<br /><br />7. Turn off the updates to system tables by using:<br /><br />sp_configure ‘allow updates’, 0<br />reconfigure with override<br />Go</div>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com2tag:blogger.com,1999:blog-8237898455922621318.post-91501107657816426532008-07-01T04:15:00.000-07:002008-07-01T06:35:06.740-07:00Backup & Restore SQL Server 2005<div align="justify">This article covers the basics of full backup backups and restores in SQL Server. The examples are from SQL Server 2005 however it applies to SQL Server 2000 and SQL Server 2005. This is a very basic article covering full database backups, database restores and the simple and full recovery models. In a typical installation SQL Server stores its data in two files. One has an MDF extension and stores the data itself and the other has an LDF extension and stores the transaction log. You can configure SQL Server to have multiple data files and multiple transaction log files if you’d like but that’s beyond the scope of this article. When SQL Server processes a transaction it goes through the following steps: It writes what it’s going to do to the transaction log. It makes the change to the data file. This change is typically made on the in-memory copy of that portion of the data file. It writes to the log that the transaction is committed. The CHECKPOINT process writes the portion data file associated with the transaction to disk. This might happen anywhere from seconds to minutes after the step above. It writes to the log that the transaction is “hardened”. The simplest type of backup is the Full Backup. The screen shots below are from SQL Server 2005’s Management Studio (SSMS). SQL Server 2000’s Enterprise Manager (EM) is very similar. In SSMS you right click on the database and choose Tasks -> Backup to bring up the window shown below. At a minimum you need to verify three things on this screen. First, that the correct database is selected. Second, that the backup type is set to FULL. Finally you need to choose the backup file name. On the Options tab you can specify whether SQL Server should replace or append the backup to the backup file. Keep in mind that the backup file is relative to where SQL Server is installed and not where you’re running SSMS.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGC2yzzQrCkHm8WjW_EmI2VHg5T9H7uPmhKZ1PqQuOF5pYGd3iQJQhL1v0JmVtpiu1ZK5RFTT0jjk-yHh2gq-wvSoOUk0EsLinyLY9pLVUKy5Zrlie-KhFCeZPCU6CH8OottYO1z8Y0nE/s1600-h/26689backup1.jpg"><img id="BLOGGER_PHOTO_ID_5218003850089789570" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGC2yzzQrCkHm8WjW_EmI2VHg5T9H7uPmhKZ1PqQuOF5pYGd3iQJQhL1v0JmVtpiu1ZK5RFTT0jjk-yHh2gq-wvSoOUk0EsLinyLY9pLVUKy5Zrlie-KhFCeZPCU6CH8OottYO1z8Y0nE/s320/26689backup1.jpg" border="0" /></a><br /><br />If you want to issue a backup statement yourself you can use SSMS to script it out for you. Click the Script button at the top of the dialog box and SSMS will generate this SQL statement for you:BACKUP DATABASE [AdventureWorks] TO DISK = N’\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10<br />You can see how these options map back to the dialog box. The NOINIT clause is what says to append the backup to the existing backup file. The other option is INIT which will overwrite the backup file. The BACKUP statement will create a single file with a BAK extension that contains what is in your data file and log file. You can backup the database while SQL Server is running and people can still use the database. It might be a little bit slower depending on your disk throughput. Restoring a database is a little more complicated. Right-clicking on Databases in SSMS bring up a dialog box like this:<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih31eO-YxYUDgInzpUGzEic3lEzMeHFPsWttBQMCiRF1oT2R8_7DWtjiHhNu_xp7SUZOA3AxbUy6xRTJcNAXpctPCtzwpz865WaoPhJETr2plEk1FTNtcHI_kRk7Pg-WTto-coDybcdB8/s1600-h/26689backup2.jpg"><img id="BLOGGER_PHOTO_ID_5218004743128966962" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih31eO-YxYUDgInzpUGzEic3lEzMeHFPsWttBQMCiRF1oT2R8_7DWtjiHhNu_xp7SUZOA3AxbUy6xRTJcNAXpctPCtzwpz865WaoPhJETr2plEk1FTNtcHI_kRk7Pg-WTto-coDybcdB8/s320/26689backup2.jpg" border="0" /></a><br /><br />I’ve already changed the database name to AdventureWorksNew. I clicked the From Device radio button and navigated to my backup file. If you’re restoring on the same computer where the original database resides you can just leave the From Database radio button selected and choose the database. It will automatically select the backup. Clicking on the options tab brings us to the second part of the dialog:<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjQ4coKSu6GhinAGmMZdLLxNjirauNmGOeM2N5daryA7kZiNlnN21SntyXsdOXTORA8BJHJ9piF5KXslv5a2VB1odqB9xi4aKegDbeCLNTdkK96NtGPavWA8oPcvKTMYbH_cT6GfrNGpI/s1600-h/26689backup3.jpg"><img id="BLOGGER_PHOTO_ID_5218004769907456498" style="CURSOR: hand" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjQ4coKSu6GhinAGmMZdLLxNjirauNmGOeM2N5daryA7kZiNlnN21SntyXsdOXTORA8BJHJ9piF5KXslv5a2VB1odqB9xi4aKegDbeCLNTdkK96NtGPavWA8oPcvKTMYbH_cT6GfrNGpI/s320/26689backup3.jpg" border="0" /></a><br /><br />Notice that it wants to restore the two file names right on top of the file names for AdventureWorks. SQL Server won’t actually let you do that unless you check the “Overwrite the existing database” checkbox above. You’ll need to edit those filenames to change the name. If I script this statement out it gives me this:RESTORE DATABASE [AdventureWorksNew]<br />FROM DISK = N’\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak’<br />WITH FILE = 1,<br />MOVE N’AdventureWorks_Data’ TO<br />N’C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf’,<br />MOVE N’AdventureWorks_Log’ TO<br />N’C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf’,<br />NOUNLOAD, STATS = 10<br />Notice the MOVE commands have the new file name that I typed in.<br />One thing to be aware of is the SQL Server Recovery Model. If you right-click on a database and choose Properties and then click the Options tab you’ll see the recovery model as the second item listed. The two main settings for this are Simple and Full. In Simple Recovery SQL Server doesn’t keep transactions in the transaction log that have already been “hardened” to disk. They are automatically removed and the space in the file is reused. In Full Recovery mode SQL Server keeps every transaction in the transaction log file until you explicitly backup the transaction log. Simple Recovery mode is better for developers or servers that are only backed up nightly. In Full Recovery mode you’ll need to do transaction log backups which I’ll cover in a future article. If you see your database growing larger and larger the most likely cause is a growing transaction log. To resolve this, change the recovery model to Simple, backup the database and then shrink the database. You can shrink the database by right-clicking on the database and choosing Tasks -> Shrink -> Database and then clicking OK.<br />When you create a database, SQL Server starts with a copy of the “model” database. If you set the Recovery Model of the “model” database to Simple all future databases will start out in Simple Recovery mode.</div>Adeel Zafarhttp://www.blogger.com/profile/13435003782533520480noreply@blogger.com6