SQL Sever Error 3154: The backup set holds a backup of a database other than the existing database.
Tested only on MS SERVER 2008
USE master
GO
-- Database to restore : DJ_ADMIN
-- Resore With: DJ_ADMIN_Latest.bak
-- Backup existing database's to : DJ_ADMIN_Old.bak;DJ_ADMIN_Old_Log.bak
-- Database to restore : DJ_ADMIN
ALTER DATABASE DJ_ADMIN
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE DJ_ADMIN
FROM DISK = 'C:\Backups\DJ_ADMIN_Latest.bak'
WITH MOVE 'DJ_ADMIN' TO 'C:\Backups\DJ_ADMIN_Old.mdf',
MOVE 'DJ_ADMIN_Log' TO 'C:\Backups\DJ_ADMIN_Old_Log.ldf',
REPLACE
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:
USE [DATABASE_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [SCHEMA_NAME].[loop_Through_Tables_Columns]
AS
BEGIN
SET NOCOUNT ON
DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
SET @TableName = ''
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' /**You can remove this condition to go thourgh with both views and tables**/
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
PRINT 'TableName: '+ CAST(@TableName AS CHAR)
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)
/**You you want to go thourgh with certain type of data types**/
AND DATA_TYPE IN('char','varchar','nchar','nvarchar','text','ntext','image','xml','varbinary')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
PRINT 'ColumnName: '+ CAST(@ColumnName AS CHAR)
END
END
END
GRANT EXECUTE ON [SCHEMA_NAME].[loop_Through_Tables_Columns] to USER_NAME
Execute Stored Procedure:
EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns]
Posted: July 15th, 2009
Categories:
T-SQL
Tags:
database,
MS SQL SERVER,
Stored Procedure,
T-SQL
Comments:
1 Comment.
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:
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT
TABLE_CATALOG,/*'TABLE_CATALOG' IS SQL-92 STANDARD NAME FOR 'DATABASE'*/
TABLE_SCHEMA,/*'TABLE_SCHEMA' IS SQL-92 STANDARD NAME FOR 'OWNER'*/
TABLE_NAME,
TABLE_TYPE
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]
USE [master]
GO
CREATE DATABASE [Adventureworks] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008_Log.ldf' )
FOR ATTACH
GO
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
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.
Posted: July 14th, 2009
Categories:
T-SQL
Tags:
AdvenureWorks,
database,
MS SQL Server 2008
Comments:
No Comments.
