Copying a Database from SQL Server 2005 to SQL Server 2008

Right click the Management folder to open a contextual menu as shown.



Click on Copy Database... menu item.

This opens up the Copy Database Wizard's Welcome screen. Read the info on this screen which says that you can migrate database on your SQL 2000 and SQL 2005 servers with this tool.



Click on the Next button.

This brings up the screen where you need to choose a source of your database. This source can either be SQL 2000 server or SQL 2005 server. However (oops!) the default that comes up is SQL 2008 server as shown. Click on the Browse... button which brings up the Browse for Servers window where you expand the Database Engine and highlight the SQL Express server (Junior version of SQL 2005 Server) as shown. Note that the figure is a collage of two windows and couple of clicks.



Click on the Next button. This brings up the window where you choose a destination server showing SQLExpress as the default server (oops again, they seem to have it backwards). Use the ellipsis button to add the Hodentek2HTEK2008 server to the choose the destination server window as shown. Again as this is set up with windows authentication the default is accepted.



Click on the Next button. This takes you to the Select Transfer Method window of the Copy Database Wizard where you can use either the attach / detach method, or the method that uses the SQL DMO API by making the right choice. The second method which does not stop the server is chosen here although it may be a little slower than the other method.



Click on the Next button. This opens up the Select Databases section of the copy database wizard. Here you can pick and choose what databases will be used in this migration. Here the database pubsx on the SQL Express is chosen to be copied. Note that you can also move the database. It is good practice to refresh before you move away from this screen.



Click on the Next button. This brings up the Configure Destination Database (1 of 1) window. If a database with the same name exists on the destination, you can either stop the transfer, or you can drop the existing one before transfer by choosing the appropriate radio button at the bottom of the screen as shown. The folder locations where the transferred database files will be saved to are shown here . If you want you can choose another name for this database. Here the default is accepted.



Click on the Next button. In the screen that shows up, you can pick and choose database objects, an option that is not available in manual attach / detach procedure. Similarly you can choose what logins to migrate by clicking on the ellipsis button in this window (the right hand area). You can use the >> and << buttons to add, or remove objects that needs transferring. Here the default is accepted.



Click on the Next button. This is where an "Integration Services Package" is created whose properties will be configured here. The execution logging options can be chosen here. This can be Windows events log or, a text file that can be chosen as shown.



Click on the Next button. This brings up the window for the Schedule the Package step of the wizard which needs to be configured. It can be, Run immediately or, it can be scheduled using a screen that would pop-up if that option is chosen. Here the "Run immediately" option is chosen. This is the screen where you would use the proxy account for the SQL Server Agent you created earlier. Click on the drop-down handle and choose the proxy you created earlier.



After choosing the proxy indicated, click on the Next button. This takes you to the summary of actions taken so far in the Complete the Wizard screen as shown.



Click on the Finish button in the Complete the Wizard window. This brings up the Performing Operations section of the wizard and shows the processing of the various steps and finally displays the success of the operation. In case there is an error it will stop processing and display a hyperlink, which when clicked will show the error in more detail.



Click the Close button to close the window. You may now refresh the databases node in the SQL Server Management Studio server and verify that the database has been migrated. The two figures show the before and after migration contents of the databases folder in the SQL Server Management Studio.

Before Transfer



After Transfer



In the SQL Server Agent folder you can see the job created for this migration as shown. There are two other jobs created earlier.



If you bring up the properties of this page by right clicking the job you can also see the job history.





Summary

Keyword or Search for Data in a DB

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END


--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Query to find Columns in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%contact%'
ORDER BY schema_name, table_name;

Reporting Services Configuration Manager: "Invalid namespace"

http://support.microsoft.com/kb/960374/EN-US

Above will work for 2005 as well.

Method 2: Add a WMI namespace
Add a WMI namespace that corresponds to the namespace that the SQL Server 2008 Reporting Services Configuration Manager tool is trying to use. To do this, follow these steps:

Note This procedure refers to a sample instance that is named "SQL2008."

Locate the Reportingservices.mof file for the SQL Reporting Services 2008 instance that is named "SQL2008." By default, this file is located in the following folder:
C:\Progam Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services\ReportServer\bin
Save or copy the Reportingservices.mof to a file that has a unique name, such as Reportingservicesalt.mof.
Open Reportingservicealt.mof file by using a text editor, such as Notepad.
On the Edit menu, click Replace.
In the Find what area, type the changed instance name, such as RS_SQL2008.
In the Replace with area, type the unmodified instance name, such as SQL2008.
Click Replace All.
Save the file, and then exit Notepad.
Open a command prompt, and then move to the same folder that you used in step 1.
Type mofcomp reportingservicesalt.mof, and then press ENTER.

NOTE:- it may need not to be same as file name reportingservicesalt just check and execute mofcomp reportingservicesalt.mof from folder location.


Note If the reporting services instance name contains an underscore (_), a dollar sign ($) or a number sign (#), you have to use method 1. Or you have to reinstall the instance.

Suggested Max Memory Settings for SQL Server 2005/2008

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.



This is for x64, on a dedicated DB server.

Physical RAM MaxMem Setting

2GB 1500

4GB 3200

6GB 4800

8GB 6700

12GB 10600

16GB 14500

24GB 22400

32GB 30000

48GB 45000

64GB 59000



This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM Target Avail RAM in Task Manager

< 4GB 512MB – 1GB

4-32GB 1GB – 2GB

32-128GB 2GB – 4GB

> 128GB > 4GB



You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

RESTORE DATABASE is terminating abnormally.

http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/

Workaround:-

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

SQL Server Performance

http://sql-articles.com/articles/dba/performance-data-collector-part-1

http://sql-articles.com/articles/dba/performance-data-collector-part-2