RESTORE HEADERONLY

If you don't know which backup files hold the right database to restore?
If you need to locate the correct backup file to restore, use RESTORE BACKUP WITH HEADERONLY option.

See msdn definition file:
http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx

How Heavy is my SQL Server database is being used?

I wrote this script after studying about I/O statistics.
This view is based on sys.dm_io_virtual file status
This looks at the physical file reads and writes, and how many times it stalled.
If it is stalling, you can dig deeper to see what is causing the stall… then you can partition the offensive table/object etc.

Cool system view!

-----------@Mydatabase-----------------------

-- type in name of database between the ''
-- or type in 'all' between the ''
--------------------------------------------

declare @Mydatabase nvarchar(255)
set @Mydatabase = 'all'
--------------------------------------------

if @Mydatabase = 'all'
begin

select

db_name(database_id)
as database_name,
file_name(file_id)
as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes

from
sys.dm_io_virtual_file_stats(null, null) ;
end
else
begin
declare @database int
set @database = db_id(@Mydatabase) ;

select

db_name(database_id)
as database_name,
file_name(file_id) as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from

sys.dm_io_virtual_file_stats(@database, null) ;
end

I keep losing this helpfile.. when I need it the most, it is located in the BOL labeled "sp_addlinkedserver (Transact-SQL)", if you scroll to the bottom of the article.

The frustrating thing about learning about linked servers is the coordination between the different types of connections! Viva la difference?

Remote OLE DB data source	OLE DB provider (@srvproduct)	product_name (whatever you want to name it "blah blah"…)	provider_name (@provider)	data_source (@datasrc)	location (anyone?)	provider_string (@providerstr?)	catalog (@catalog)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server 1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle	Microsoft OLE DB Provider for Oracle	Any2	MSDAORA	SQL*Net alias for Oracle database
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB. Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

Link SQL Server 2008 to 2005

Have on hand:

1. A name for your linked server you want to create; can be anything, just keep it clean.
   I will call mine MyLinkedServer
   
   
2. If default instance, name of the server only (as opposed to server/instance)
   Mine is MyComputerName\SQLEXPRESS2
   
   
3. If second instance, server/instance_name
   
   
4. It is presumed you have same login name as the server you are reaching over and linking to.
   I tested these two servers on the same computer (SQL Server 2005 & 2008)
   If they are not, impersonate yourself to the server you are trying to talk to:
   
   1. After scripting this out, go to SMSS and right-click your near MyLinkedServer à Properties
      
   2. Select the security option
      
   3. Add the login name the other server uses and click impersonate.
      

   EXEC
   master.dbo.sp_addlinkedserver
   

   @server =
   N'MyLinkedServer',
   

   @srvproduct=N'',
   

   @provider=N'SQLNCLI',
   

   @datasrc=N'
   MyComputerName\SQLEXPRESS2'
   

If you use the graphical interface, go for it. This way is easier for me.

Now you can call whatever database you have there. Here's what I did:

select
*
from [MyLinkedServer].[MyDatabase].dbo.[MyTable]

Obama speasks at Georgetown University, covers all religious symbols

I am infuriated with the covering up of religious symbols.

This is a part of our heritage, great and small.

It is an absolute insult to all who have immigrated here.

Sin of omission... omit our history of religous heritage...

Respectfully submitted, this is a passive way to say you reject your constituants

Last time statistics were done?

-- view the date the statistics were last updated:

select 'index Name' = i.[name],
'Statistics Date' = stats_date(i.[object_id], i.index_id)
from sys.objects o
inner join sys.indexes i
on o.name = 'Employee'
and o.[object_id] = i.[object_id]

-- if you need to update all the indexes:

update statistics HumanResources.Employee
with fullscan

dm_db_index_operational_stats

here's a method I found:

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc