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

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.