2000 to 2008 DB Transafer - SQL Server Forums

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

SQL Server 2008 Forums

SQL Server Administration (2008)

2000 to 2008 DB Transafer

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2013-03-22 : 08:26:11

I have database size 250 GB in 2000 Server is going to restore on 2008 server.

However this is ok in getting restored and even changing its compatibility and collation from Latin1_general_CP1_CI_AS to SQL_Latin1_CP1_CI_AS. Everything works fine.

Since the feature of Compressing the database is to be implemented.

Could anyone help me in let me know as to how to compress the database / Tables underlying.

Many thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-22 : 08:39:00

start here

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2013-03-23 : 04:53:06

Thanks For the Article which I have gone through which is very generic in adopting to Data Compresssion.

As per my query the database has got couple of tables with Views and Stored Procedures and currently in 2000 Server.

I have the DB.BAK and restored on the 2008 R 2 Version, however, I need to get some clarifications as to how I should compress the database (ROW OR PAGE).

I have started with drawing the statistical information using to determine on changing compression state will affect a table or an index, using sp_estimate_data_compression_savings.

Now unsure based on this result - should I use the ROW or PAGE compression.

Should I apply either PAGE / ROW based then what are the possible measures / repurcussions?

Is there anything that I will be missing while doing this upgrade from 2000 to 2008 R 2?

Your expertise highly appreciated and many thanks.

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2013-03-25 : 00:18:11

While the Code below implemented after restoring the Database from 2000 to 2008 R 2 platform, haven't changed any.

USE [IMAX007]
GO

/****** Object: Index [PK_IA_NOCOM_AE] Script Date: 03/25/2013 13:36:35 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IA_NOCOM_AE]') AND name = N'PK_IA_NOCOM_AE')
ALTER TABLE [dbo].[IA_NOCOM_AE] DROP CONSTRAINT [PK_IA_NOCOM_AE]
GO

USE [IMAX007]
GO

/****** Object: Index [PK_IA_NOCOM_AE] Script Date: 03/25/2013 13:36:36 ******/
ALTER TABLE [dbo].[IA_NOCOM_AE] ADD CONSTRAINT [PK_IA_NOCOM_AE] PRIMARY KEY CLUSTERED
(
[AE_TITLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
GO

ALTER TABLE IA_NOCOM_AE
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

There is no change in the compression.

Any Suggestions as how to compress, absolutely no idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources