Wednesday, September 4, 2013

0 SQL Server 2012 Sample database

This post is to illustrate how to install sample database for SQL Server 2012.

  • Adventure Work 2012
  • Adventure Work 2012 DW
  • Pubs
  • Northwind

AdventureWorks2012

Down load from here.

image

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf'), 
    (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf') 
    FOR ATTACH; 

 

adventure work DW 2012

Down load from here.

image

The file for this database is one single MDF file. so we need to use ATTACH_REBUILD_LOG;

CREATE DATABASE MyAdventureWorksDW
    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorksDW2012_Data.mdf')
    FOR ATTACH_REBUILD_LOG; 

North Wind and Pubs

Download from here.

image

image

image

The installer actually just extra the files into the directory.

image

If we try to attached the data file, we would get the below errors. It is the database file is created on the SQL Server 2000 and the 2000 backward compatibly mode has been removed from SQL server 2012.

image

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PUBS'. CREATE DATABASE is aborted.
Msg 950, Level 20, State 1, Line 1
Database 'PUBS' cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

The solution is to create the database by executing the script.  However, we would receive below errors .  The errors is happen while executing sp_dboption. sp_dboption is being removed from SQL Server 2012 as well. The database are actually being created successfully. It is just the options are not set. 

image

image

We can use alter database to fix the options

ALTER DATABASE [pubs] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [Northwind] SET RECOVERY BULK_LOGGED
GO

 

Alternatively, if you have SQL Server 2008R2 , you can attach the database file to the instance and change the compatibility mode from 2000 to 2008R2 . Then backup/restore or detach/attach to the SQL 2012 instance.

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates