Create DB using 2000 script in 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Create DB using 2000 script in 2005

Author

Topic

govthamb
Starting Member

27 Posts

Posted - 2009-08-13 : 03:31:20

Hi,

I have a script of SQL Server 2000 DB which creates a database. I need to create the same DB in 2005 version.

Please suggest me how do I convert the existing 2000 script to 2005 to create database. I do not know what are the specific changes I need to check before I execute this script in 2005 apart from Collation change.

I have scripts of all the tables also which needs to be created in 2005. Please suggest me how do I proceed on this.

Below is the 2000 create DB script
-----------------------------------
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'testApps')
DROP DATABASE [testApps]
GO

CREATE DATABASE [testApps] ON (NAME = N'testApps_Data', FILENAME = N'f:\active\testApps_Data.MDF' , SIZE = 4249, MAXSIZE = 5501, FILEGROWTH = 100) LOG ON (NAME = N'testApps_Log', FILENAME = N'g:\active\testApps_Log.LDF' , SIZE = 3800, MAXSIZE = 5501, FILEGROWTH = 100)
COLLATE SQL_Latin1_General_CP850_BIN
GO

exec sp_dboption N'testApps', N'autoclose', N'false'
GO

exec sp_dboption N'testApps', N'bulkcopy', N'false'
GO

exec sp_dboption N'testApps', N'trunc. log', N'false'
GO

exec sp_dboption N'testApps', N'torn page detection', N'true'
GO

exec sp_dboption N'testApps', N'read only', N'false'
GO

exec sp_dboption N'testApps', N'dbo use', N'false'
GO

exec sp_dboption N'testApps', N'single', N'false'
GO

exec sp_dboption N'testApps', N'autoshrink', N'false'
GO

exec sp_dboption N'testApps', N'ANSI null default', N'false'
GO

exec sp_dboption N'testApps', N'recursive triggers', N'false'
GO

exec sp_dboption N'testApps', N'ANSI nulls', N'false'
GO

exec sp_dboption N'testApps', N'concat null yields null', N'false'
GO

exec sp_dboption N'testApps', N'cursor close on commit', N'false'
GO

exec sp_dboption N'testApps', N'default to local cursor', N'false'
GO

exec sp_dboption N'testApps', N'quoted identifier', N'false'
GO

exec sp_dboption N'testApps', N'ANSI warnings', N'false'
GO

exec sp_dboption N'testApps', N'auto create statistics', N'true'
GO

exec sp_dboption N'testApps', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'testApps', N'db chaining', N'false'
GO

use [testApps]
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-13 : 08:56:37

your script will work. sp_dboption is deprecated though. use ALTER DATABASE


ALTER DATABASE [testApps] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [testApps] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [testApps] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [testApps] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [testApps] SET ARITHABORT OFF 
GO
ALTER DATABASE [testApps] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [testApps] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [testApps] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [testApps] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [testApps] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [testApps] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [testApps] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [testApps] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [testApps] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [testApps] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [testApps] SET  ENABLE_BROKER 
GO
ALTER DATABASE [testApps] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [testApps] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [testApps] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [testApps] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [testApps] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [testApps] SET  READ_WRITE 
GO
ALTER DATABASE [testApps] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [testApps] SET  MULTI_USER 
GO
ALTER DATABASE [testApps] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [testApps] SET DB_CHAINING OFF

govthamb
Starting Member

27 Posts

Posted - 2009-08-13 : 09:05:59

Thanks a lot Russell.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources