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;