TechEd 2008 Demonstration of SQL Server 2008

Here is the youtube video for Microsoft TechEd 2008 where they demonstrate MS SQL Server 2008 features.

SQL Server 2005 Reporting Services Video Tutorial

Here is the youtube video for setting up SQL Server 2005 Reporting Services.
Part 1.

Installing a SQL Server 2005 Instance

Learn how to install SQL Server 2005 instance with screenshots. (for newbies)
Video



1. Start the Microsoft SQL Server Installation Wizard by running the Setup application
using the menu that appears when you insert the CD. On the End User
License Agreement page, select the I Accept The Licensing Terms And Conditions
check box and click Next.
2. On the Installing Prerequisites page, click Next when the installation of the
required components completes.
3. On the Welcome To The Microsoft SQL Server Installation Wizard page, click
Next.
4. Verify the completion of the System Configuration Checker.




5. Click Next.
6. Fill in the appropriate user and organization name in the text boxes




7. Click Next.
8. Select the appropriate components to install by checking the box next to the
component. For this practice, you should install all the available components



9. Click Advanced. Expand the Documentation, Samples, And Sample Databases tree.
10. From the drop-down list associated with Sample Databases select Entire Feature Will Be Installed On Local Hard Drive.



11. Click Next.
12. Determine which instance names are already in use on the server by clicking
Installed Instances, which displays the window



13. Click OK.
14. Install a default instance


15. Click Next.
16. Review the components that will be installed and click Next.
17. Configure the service account information and the component startup information
by selecting Use The Built-In System Account and choosing Local System
from the drop-down list



18. Select the SQL Server Agent check box.
19. Click Next.
20. Configure the authentication mode by selecting Mixed Mode (Windows
Authentication And SQL Server Authentication)


21. Enter a strong password in the password text boxes. This password is used by
the sa login ID.
22. Click Next.
23. Review the default collation settings



24. Click Next.
25. Configure error and usage reporting by selecting both the Automatically Send
Error Reports and Automatically Send Feature Usage Data check boxes


26. Click Next.
27. Verify which components will be installed



28. Click Install.
29. Verify that setup is complete



30. Click Next and then review the completion report.
31. Click Finish.

Recreate Corrupt MSDB database

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...

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.

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.

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):

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

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)

Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

Shutdown and restart the server without the 3608 trace flag

This works on SQL Server 2000 as well.

MCITP - DBA

Microsoft Certified IT Professional: Database Administrator
(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.

Exam 70-431 Review the preparation guide for Exam 70–431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance
Exam 70-443 Review the preparation guide for Exam 70-443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
Exam 70-444 Review the preparation guide for Exam 70-444: PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

Need Learning Resources

CBT Nuggets 70-431, CBT Nuggets 70-443, CBT Nuggets 70-444
http://rapidshare.com/files/126172774/CBT_70-444_1.zip

http://rapidshare.com/files/126391794/CBT_70-444_2.zip

http://rapidshare.com/files/126616018/CBT_70-443.part1.rar

http://rapidshare.com/files/127415670/CBT_70-443.part2.rar

Latest Practice Test: Testking, Pass4sure and Actual Test
Link: http://www.discussbits.com/forum
TorrentDownloads: http://www.torrentspider.org/

Fixing Suspect DB SQL Server

Steps to Fix a suspect Database

1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure ‘allow updates’, 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = "
– note the value of the status column for later use in # 6

begin tran
update sysdatabases set status = 32768 where name = "

– Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log(",")

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).

6. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency:

sp_dboption ", ’single user’, ‘true’

OR

Alter Database databasename SET SINGLE_USER
DBCC checkdb(")
Go

begin tran
update sysdatabases set status = where name = "

– verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure ‘allow updates’, 0
reconfigure with override
Go

Backup & Restore SQL Server 2005

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.



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
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:




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:



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]
FROM DISK = N’\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak’
WITH FILE = 1,
MOVE N’AdventureWorks_Data’ TO
N’C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf’,
MOVE N’AdventureWorks_Log’ TO
N’C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf’,
NOUNLOAD, STATS = 10
Notice the MOVE commands have the new file name that I typed in.
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.
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.