Dushyanth Jyothi

SQL Sever Error 3154: The backup set holds a backup of a database other than the existing database.

Tested only on MS SERVER 2008

 T-SQL | 
 copy code |
?
 
01
USE master
02
GO
03
 
04
-- Database to restore : DJ_ADMIN
05
-- Resore With: DJ_ADMIN_Latest.bak
06
-- Backup existing database's to : DJ_ADMIN_Old.bak;DJ_ADMIN_Old_Log.bak
07
-- Database to restore : DJ_ADMIN
08
 
09
ALTER DATABASE DJ_ADMIN
10
SET SINGLE_USER WITH
11
ROLLBACK IMMEDIATE
12
RESTORE DATABASE DJ_ADMIN
13
FROM DISK = 'C:\Backups\DJ_ADMIN_Latest.bak'
14
WITH MOVE 'DJ_ADMIN' TO 'C:\Backups\DJ_ADMIN_Old.mdf',
15
MOVE 'DJ_ADMIN_Log' TO 'C:\Backups\DJ_ADMIN_Old_Log.ldf',
16
REPLACE

Resetting/Changing the identity[auto-increment number] column value of a table

DBCC CHECKIDENT checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column

Tested only on MS SERVER 2008

Table to alter:

 T-SQL | 
 copy code |
?
 
1
CREATE TABLE ADMIN.DocumentType_List
2
(
3
	DocumentType_ID INT IDENTITY(0,1) NOT NULL,
4
	DocumentType NVARCHAR(250) NOT NULL,
5
	Description_ NVARCHAR(250) NOT NULL
6
CONSTRAINT PK_DocumentType PRIMARY KEY (DocumentType_ID)
7
)
8

In the above code 'DocumentType_ID' column has declared as Identity, having auto increment value by 1 starting with 0. This means the first row inserted will have 'DocumentType_ID' value of 1. then 2,3,so on..

In future if we want to reset this value, for what so ever reason, we can use T-SQL:

 T-SQL | 
 copy code |
?
 
1
2
DBCC CHECKIDENT('Admin.DocumentType_List', RESEED, 0)

This code will reset table Identity back to 0.

Further Reading/Details:http://msdn.microsoft.com/en-us/library/ms176057(SQL.90).aspx

T-SQL code [Stored Procedure] to loop through all tables and all columns in a database

Applies to MS SQL SERVER 2008

Change [DATABASE_NAME],[SCHEMA_NAME],[USER_NAME] accordingly


Stored Procedure:

 T-SQL | 
 copy code |
?
 
01
USE [DATABASE_NAME]
02
GO
03
SET ANSI_NULLS ON
04
GO
05
SET QUOTED_IDENTIFIER ON
06
GO
07
CREATE PROC [SCHEMA_NAME].[loop_Through_Tables_Columns]
08
AS
09
BEGIN
10
 
11
	SET NOCOUNT ON
12
	DECLARE @TableName NVARCHAR(256)
13
	DECLARE @ColumnName NVARCHAR(128)
14
	SET  @TableName = ''
15
 
16
	WHILE @TableName IS NOT NULL
17
	BEGIN
18
		SET @ColumnName = ''
19
		SET @TableName = 
20
			(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES
21
				WHERE TABLE_TYPE = 'BASE TABLE' /**You can remove this condition to go thourgh with both views and tables**/
22
					AND	 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
23
					AND	OBJECTPROPERTY(
24
						OBJECT_ID(
25
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
26
							 ), 'IsMSShipped'
27
						       ) = 0
28
			)
29
 
30
		PRINT 'TableName:  '+ CAST(@TableName AS CHAR)
31
 
32
			WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
33
			BEGIN
34
				SET @ColumnName =
35
				(
36
					SELECT MIN(QUOTENAME(COLUMN_NAME))
37
					FROM INFORMATION_SCHEMA.COLUMNS
38
					WHERE TABLE_SCHEMA	= PARSENAME(@TableName, 2)
39
						AND	TABLE_NAME	= PARSENAME(@TableName, 1)
40
						/**You you want to go thourgh with certain type of data types**/
41
						AND	DATA_TYPE IN('char','varchar','nchar','nvarchar','text','ntext','image','xml','varbinary')
42
						AND	QUOTENAME(COLUMN_NAME) > @ColumnName
43
				)
44
				PRINT 'ColumnName:  '+ CAST(@ColumnName AS CHAR)				
45
			END
46
	END
47
 
48
END
49
GRANT EXECUTE ON [SCHEMA_NAME].[loop_Through_Tables_Columns] TO USER_NAME 

Execute Stored Procedure:

 T-SQL | 
 copy code |
?
 
1
EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns]

Information schema is part of the SQL-92 standard, holds the structure if your database.

Applies to MS SQL SERVER 2008

T-SQL CODE to see the metada:

 T-SQL | 
 copy code |
?
 
1
SELECT * FROM INFORMATION_SCHEMA.TABLES

 T-SQL | 
 copy code |
?
 
1
SELECT 
2
TABLE_CATALOG,/*'TABLE_CATALOG' IS SQL-92 STANDARD NAME FOR 'DATABASE'*/
3
TABLE_SCHEMA,/*'TABLE_SCHEMA' IS SQL-92 STANDARD NAME FOR 'OWNER'*/
4
TABLE_NAME,
5
TABLE_TYPE
6
FROM INFORMATION_SCHEMA.TABLES

Further Reading/Details:
http://msdn.microsoft.com/en-us/library/ms186778.aspx

An error occurred when attaching the database – AdvenureWorks database

The below steps applies to MS SQL Server 2008

A. Make sure you enabled database instance to use FILESTREAM as instructed here:
http://msdn.microsoft.com/en-us/library/cc645923.aspx

B. If you downloaded Adventureworks from codeplex, make sure you have set the right user permissions to these files

C. Copy AdventureWorks2008_Data.mdf and AdventureWorks2008_Log.ldf to
‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\’
If you see a folder called ‘Documents’ in the downloaded files set, then copy this folder also to ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\’

D. In the managment studio, open new query window and execute the below code: [Change the files path accordingly]

 T-SQL | 
 copy code |
?
 
1
USE [master]
2
GO
3
CREATE DATABASE [Adventureworks] ON
4
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
5
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Log.ldf' )
6
FOR ATTACH
7
GO
8
IF not exists (SELECT name FROM master.sys.databases sd WHERE name = N'Adventureworks' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [Adventureworks].dbo.SP_CHANGEDBOWNER @loginame=N'sa', @MAP=FALSE
9
GO

OR you can attach a databse either using MS:
1. In SQL Server Management Studio Object Explorer, connect to an instance of the Microsoft SQL Server Database Engine, and then expand that instance.
2. Right click Databases, then Tasks, and then click Attach.
3. In the Attach Databases dialog box, to specify the database to be attached, click Add; and in the Locate Database Files dialog box, select the disk drive where the database resides and expand the directory tree to find and select the .mdf file of the database; for example: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Data.mdf

Optionall : Specify a different name, Change the ownership
4. When you are ready to attach the database, click OK.

Search all columns of all tables in a database

How to search all columns of all tables in a database for a keyword?

Credits to: Vyas

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Here is the complete stored procedure code:

–To search all columns of all tables in Pubs database for the keyword “Computer”

 T-SQL | 
 copy code |
?
 
01
CREATE PROC SearchAllTables
02
(
03
 @SearchStr NVARCHAR(100)
04
)
05
AS
06
BEGIN
07
<span style="font-family: verdana; color: gray; font-size: xx-small;">
08
 -- Copyright � 2002 Narayana Vyas Kondreddi. All rights reserved.
09
 -- Purpose: To search all columns of all tables for a given search string
10
 -- Written by: Narayana Vyas Kondreddi
11
 -- Site: http://vyaskn.tripod.com
12
 -- Tested on: SQL Server 7.0 and SQL Server 2000
13
 -- Date modified: 28th July 2002 22:50 GMT
14
</span>
15
 
16
 CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))
17
 
18
 SET NOCOUNT ON
19
 
20
 DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110)
21
 SET  @TableName = ''
22
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
23
 
24
 WHILE @TableName IS NOT NULL
25
 BEGIN
26
 SET @ColumnName = ''
27
 SET @TableName =
28
 (
29
 SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
30
 FROM 	INFORMATION_SCHEMA.TABLES
31
 WHERE 		TABLE_TYPE = 'BASE TABLE'
32
 AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
33
 AND	OBJECTPROPERTY(
34
 OBJECT_ID(
35
 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
36
 ), 'IsMSShipped'
37
        ) = 0
38
 )
39
 
40
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
41
 BEGIN
42
 SET @ColumnName =
43
 (
44
 SELECT MIN(QUOTENAME(COLUMN_NAME))
45
 FROM 	INFORMATION_SCHEMA.COLUMNS
46
 WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
47
 AND	TABLE_NAME	= PARSENAME(@TableName, 1)
48
 AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
49
 AND	QUOTENAME(COLUMN_NAME) > @ColumnName
50
 )
51
 
52
 IF @ColumnName IS NOT NULL
53
 BEGIN
54
 INSERT INTO #Results
55
 EXEC
56
 (
57
 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
58
 FROM ' + @TableName + ' (NOLOCK) ' +
59
 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60
 )
61
 END
62
 END
63
 END
64
 
65
 SELECT ColumnName, ColumnValue FROM #Results
66
END
67

 T-SQL | 
 copy code |
?
 
1
EXEC SearchAllTables 'Computer'
2
GO
3