Move a database path during a restore!!

SQL Dba's are you moving databases between PROD and DEV. They have different file structures right? Well try using the MOVE command with your restore. It will save time and effort.

restore database BuildingDepartment from disk =
'G:\backup\BuildingDepartment_backup_201112080500.bak'
with recovery,
MOVE 'BuildingDepartment' TO 'G:\Data\BuildingDepartment.mdf',
MOVE 'BuildingDepartment_Log' TO 'G:\Logs\BuildingDepartment.ldf'
go

Database diagram support objects can not be installed?

First make sure that there is a valid owner for the database. I.E. make sure owner is not blank.

If that doesnt fix your problem then try this command on the new query line.

ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login

Version error while executing SSIS package

To clarify a bit more, packages created in Visual Studio 2008 are SQL Server 2008 packages. If you have both 2005 and 2008 installed on your system, you'll need to provide the explicit path to the 2008 version of DTEXEC when you run the package:

MS SQL 2008 DTS package error

Description: The version number in the package is not valid. The version number cannot be greater than current version number.

you'll need to provide the explicit path to the 2008 version of DTEXEC when you run the package:

How to setup the PREREQS for oracle databases

ALter database test

Set READ_COMMITTED_SNAPSHOT = ON

go

Alter database test

Set ALLOW_SNAPSHOT_ISOLATION = ON

go

Select the SQL Server authentication option
when you set the security
properties for the database.

go
>>The schema owner for the above repositories will require DB_OWNER and
>>BULK_INSERT.
>>
>>Run the collate script on the repositories:

>>DECLARE @collate sysname
>>SELECT @collate = convert(sysname, serverproperty('COLLATION'))
>>IF ( charindex(N'_CI', @collate) > 0 )
>>BEGIN
>>select @collate = replace(@collate, N'_CI', N'_CS')
>>exec ('ALTER database OBIAPP COLLATE ' + @collate)
>>END
>>GO

How to reindex all tables in a database

Let assume that the database is named clo

USE clo
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

Find failed SQL jobs in one click

--The code to convert run_date and run_time into an actual--datetime is NOT my code, and I can't remember what blog post--I found it from. But it's not mine.select sj.name, sjs.step_name,CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6 + run_time % 100 * 10) / 216e4from msdb.dbo.sysjobs sjinner join msdb.dbo.sysjobsteps sjson sj.job_id = sjs.job_idinner join msdb.dbo.sysjobhistory sjhon sjs.job_id = sjh.job_idand sjs.step_id = sjh.step_idwhere sjh.run_status = 0and datediff (hour, (CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6 + run_time % 100 * 10) / 216e4), current_timestamp) <= 24

Service Pack 2 for MS Sql 2008 is it worth it?

What's New:
Reporting Services in SharePoint Integrated
Mode. SQL Server 2008 SP2 provides updates for Reporting Services
integration with SharePoint products. SQL Server 2008 SP2 report servers can
integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new
add-in to support the integration of SQL Server 2008 R2 report servers with
SharePoint 2007 products. For more information see the “What’s New in SharePoint
Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What's New (Reporting
Services).
SQL Server 2008 R2 Application and Multi-Server Management Compatibility
with SQL Server 2008.
SQL Server 2008 Instance Management.With SP2 applied, an instance of
the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2
Utility Control Point as a managed instance of SQL Server. For more information,
see Overview of SQL
Server Utility in SQL Server 2008 R2 Books Online.
Data-tier Application (DAC) Support.Instances of the SQL Server 2008
Database Engine support all DAC operations delivered in SQL Server 2008 R2 after
SP2 has been applied. You can deploy, upgrade, register, extract, and delete
DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You
must use the SQL Server 2008 R2 client tools, such as SQL Server Management
Studio, to perform DAC operations. A data-tier application is an entity that
contains all of the database objects and instance objects used by an
application. A DAC provides a single unit for authoring, deploying, and managing
the data-tier objects. For more information, see Designing and Implementing
Data-tier Applications.

Transaction logs full in MS SQL 2008

This script should help. Assuming that your database is named test.

--changes the database to simple mode

Alter database test

set recovery simple

go

--makes the transaction log one mb

DBCC shrinkfile (test,1)

go

--changes the database back to full recovery

Alter database test

set recovery full

Monitor server with MS SQL profiler

Start off by clicking on the start button>all programs>sql server>performance tools>SQL profiler.

1. File>New trace


2. Choose server instance


3. give trace a name


4. save to file (I recommend storing to a .trc file).


5. trace files can get rather large so check use a stop time.


6. Click run

This utility is great for monitoring , finding slow query response, CPU overhead and auditing users. You even have the option of storing the trace to a database(Not recommended).

Good luck!!!

recover MS SQL 2005 master database with original media

Here are the instructions on how to rebuild master database. This would be useful during DR.

SQL SERVER 2005 install media should be restored to the same location from where sql server was installed originally.


The location can be found from SQLSetup001__SQL.log ? Search for Product in this log file. This point to the location from where the sql server 2005 w

Go the sql server configuration and change the properties to startup manual and stop the sql server services

start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=

Default instance name is MSSQLSERVER

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin distributed transaction

One morning I get a call from a developer about this issue. It turns out that the MSDTC service had stop on a Linked server. So, to resolve this issue, you must first start the MSDTC service on both servers involved. Windows 2008 example here: From "administrative tools" -> "component services" -> "computers" -> "my computer" , "local dtc" right click "properties" goto MSDTC tab and select "security configuration", enable checkbox for "network dtc access" and "allow outbound". Check No Authentication Required.

How to migrate databases from MS sql 2000 to MS SQL 2005/2008

There are 4 ways to migrate databases from one server to another:

1. backup bak files and restore to new server
2. copy the mdf and ldf files to new server and attached the databases to new server
   
3. use the migration wizard to move the objects and databases over
4. script the entire restore process and run in sqlcmd

DTS or SSIS works manual not scheduled???

By default , when you run an SSIS package manually it assumes your current login . However, when you schedule a package, sql uses the account running MS SQL agent service.

So you can either change the account running the agent service or create a proxy account.
To change the account running the sql agent, please use MS SQL server configuration manager.

Lastly, create a proxy in the SQL studio manager under SQL agent>proxy