Database backup

I am currently building a new production server. For that I need to move
the databases and set up replication between the SQL Server databases.
I have backed-up the databases on the present server.
How can I now restore these databases on the other(new) server? I dont
have those databases already on that server and SQL Server asks which
database I wanna restore, if try to start the restoration, so basically
it assumes the database is already on the server!!
Do I have to create the databases on the other(new) Server before a
restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
A point to a good article or explanation will be highly appreciated.
Best Regards!
--
Posted via http://dbforums.comAssuming that both servers can be online at the same time, go to the new
server and run the T-SQL RESTORE DATABASE statement. In the FROM line of
the RESTORE statement, use DISK=<UNC path pointing to backup file on old
server> In effect, you will be pulling the database backup from the old
server to the new one.
Should be something like:
RESTORE DATABASE mydatabase
FROM DISK = '\\oldserver\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Mydbbackup.bak'
Books online points out that the account under which you are running SQL
Server on the new server must have READ access to the remote "old" server's
backup file.
See "RESTORE" in books online for more info.
HTH,
Rob
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3146187.1059020556@.dbforums.com...
> I am currently building a new production server. For that I need to move
> the databases and set up replication between the SQL Server databases.
> I have backed-up the databases on the present server.
> How can I now restore these databases on the other(new) server? I dont
> have those databases already on that server and SQL Server asks which
> database I wanna restore, if try to start the restoration, so basically
> it assumes the database is already on the server!!
> Do I have to create the databases on the other(new) Server before a
> restore? If yes, how? I m running SQL 2k on Win 2k on both servers.
> A point to a good article or explanation will be highly appreciated.
> Best Regards!
> --
> Posted via http://dbforums.com|||It is asking which database you wish to create. It doesn't
have to exist. If it does exists you have to check the
option to overwrite.
Also look at the next tab which holds the location of the
files to create - you may have to change them if the paths
are different on this m/c.
>--Original Message--
>I am currently building a new production server. For that
I need to move
>the databases and set up replication between the SQL
Server databases.
>I have backed-up the databases on the present server.
>How can I now restore these databases on the other(new)
server? I dont
>have those databases already on that server and SQL
Server asks which
>database I wanna restore, if try to start the
restoration, so basically
>it assumes the database is already on the server!!
>Do I have to create the databases on the other(new)
Server before a
>restore? If yes, how? I m running SQL 2k on Win 2k on
both servers.
>A point to a good article or explanation will be highly
appreciated.
>Best Regards!
>--
>Posted via http://dbforums.com
>.
>

Data Warehouse Structure and Indexing when using MOLAP

Hi there,

I am building a cube in AS2005 which is made up of one fact table (20 million rows) and 7 dimension tables. I am planning to use MOLAP storage and my question is this. Is it important to add indexes, primary keys, foreign keys to my data source to improve performance? I can appreciate that it might speed up processing time, but since all the data and aggregations would be stored in the cube, I can't see how it would have any imact on performance (querying the cube).

If there is a benefit to adding indexes, keys etc. then could somebody please tell the best strategy, i.e where to put them, clustered or nonclustered etc.

Many thanks,

David

I have the same opinion, with MOLAP indexes will only help when you process a cube. MOLAP is separated from the data source when the cube is finished/processed.

If you use the same datamart for Reporting Services you will benefit when running queries.

The strategy for index is clustered indexes for dimension primary keys and non-clustered on their foreign keys in the fact table. A non clustered composite index on all the foreigh keys in the fact table is also recommended. Clustered index in the fact table is not recommended.

You will pay for using indexes when you do inserts, deletes and updates so it is often recommended to drop indexes when you update your data mart or data warehouse, and recreate the when the update/load is finished.

Primary and foreign keys are good help if you like to receive errors when you are loading the data mart/warehouse. With them you know that all fact records have related dimension members Else these errors will appear when you process the cube.

Here is a web site I can recommend for performance issues:

http://www.sql-server-performance.com/default.asp

Regards

Thomas Ivarsson

Data Warehouse Structure and Indexing when using MOLAP

Hi there,

I am building a cube in AS2005 which is made up of one fact table (20 million rows) and 7 dimension tables. I am planning to use MOLAP storage and my question is this. Is it important to add indexes, primary keys, foreign keys to my data source to improve performance? I can appreciate that it might speed up processing time, but since all the data and aggregations would be stored in the cube, I can't see how it would have any imact on performance (querying the cube).

If there is a benefit to adding indexes, keys etc. then could somebody please tell the best strategy, i.e where to put them, clustered or nonclustered etc.

Many thanks,

David

I have the same opinion, with MOLAP indexes will only help when you process a cube. MOLAP is separated from the data source when the cube is finished/processed.

If you use the same datamart for Reporting Services you will benefit when running queries.

The strategy for index is clustered indexes for dimension primary keys and non-clustered on their foreign keys in the fact table. A non clustered composite index on all the foreigh keys in the fact table is also recommended. Clustered index in the fact table is not recommended.

You will pay for using indexes when you do inserts, deletes and updates so it is often recommended to drop indexes when you update your data mart or data warehouse, and recreate the when the update/load is finished.

Primary and foreign keys are good help if you like to receive errors when you are loading the data mart/warehouse. With them you know that all fact records have related dimension members Else these errors will appear when you process the cube.

Here is a web site I can recommend for performance issues:

http://www.sql-server-performance.com/default.asp

Regards

Thomas Ivarsson

Data warehouse question

Hi,
I am building the data warehouse in SQL 2005 and have the following
questions want to clarify.
1. Any method and suggestion for incremental loading for data warehouse? If
I create a field in each record in my source table and select the one havent
been loaded in ETL, is it a method or any other better suggestion?
2. If there are schema changes in the source table and the data warehouse,
is it necessary (or best practise) to reload all data?
Thanks a lot.
Ryan
Ryan:
I am stumbling through my ETL systems initial design and have had to work
this one out as well. Take this following bit of free advice with caution:
I created a tracking table in a separate db with TableName and LastLoadDate
columns. After runnin a package that is my "Initial Load" package (ie:
truncating the whole Fact table), it runs a SQL task that populates the
LastLoadDate in the corresponding record.
For Incremental Loads, I run two data flows, one for 'changed' rows and one
for 'new' rows:
"Changed": SELECT * FROM x WHERE [created] < LastLoadDate and [Modified] >
LastLoadDate. That goes into a staging table. At the end of the package,
another SQL task runs a simple UPDATE query that updates the FACT table from
the statging table.
"NEW ROWS" SELECT * FROM x WHERE [created] > LastLoadDate. That data flow
gets appended directly to the FACT table.
Check out books by Ralph Kimball on Microsoft Data Warehouse Design.
HTH
Todd Chittenden
"Ryan" wrote:

> Hi,
> I am building the data warehouse in SQL 2005 and have the following
> questions want to clarify.
> 1. Any method and suggestion for incremental loading for data warehouse? If
> I create a field in each record in my source table and select the one havent
> been loaded in ETL, is it a method or any other better suggestion?
> 2. If there are schema changes in the source table and the data warehouse,
> is it necessary (or best practise) to reload all data?
> Thanks a lot.
> Ryan

Data warehouse question

Hi,
I am building the data warehouse in SQL 2005 and have the following
questions want to clarify.
1. Any method and suggestion for incremental loading for data warehouse? If
I create a field in each record in my source table and select the one havent
been loaded in ETL, is it a method or any other better suggestion?
2. If there are schema changes in the source table and the data warehouse,
is it necessary (or best practise) to reload all data?
Thanks a lot.
RyanRyan:
I am stumbling through my ETL systems initial design and have had to work
this one out as well. Take this following bit of free advice with caution:
I created a tracking table in a separate db with TableName and LastLoadDate
columns. After runnin a package that is my "Initial Load" package (ie:
truncating the whole Fact table), it runs a SQL task that populates the
LastLoadDate in the corresponding record.
For Incremental Loads, I run two data flows, one for 'changed' rows and one
for 'new' rows:
"Changed": SELECT * FROM x WHERE [created] < LastLoadDate and [Modif
ied] >
LastLoadDate. That goes into a staging table. At the end of the package,
another SQL task runs a simple UPDATE query that updates the FACT table from
the statging table.
"NEW ROWS" SELECT * FROM x WHERE [created] > LastLoadDate. That data flo
w
gets appended directly to the FACT table.
Check out books by Ralph Kimball on Microsoft Data Warehouse Design.
HTH
--
Todd Chittenden
"Ryan" wrote:

> Hi,
> I am building the data warehouse in SQL 2005 and have the following
> questions want to clarify.
> 1. Any method and suggestion for incremental loading for data warehouse? I
f
> I create a field in each record in my source table and select the one have
nt
> been loaded in ETL, is it a method or any other better suggestion?
> 2. If there are schema changes in the source table and the data warehouse,
> is it necessary (or best practise) to reload all data?
> Thanks a lot.
> Ryan

Data Types

Hello, I am following this article about building an ASP.Net application which uses SQL Express 2005. The document calls for building a couple of tables. One column is identified with data type of "Byte" which are is available for selection within the Visual Studio 2005 interface. The following values show in the drop down of data type in VS2005:

Bigint

Binary(5)

Bit

Char(10)

DateTime

Decimal (18,0)

Float

Image

Int

Money

NChar(10)

NText

Numeric(18,0)

Nvarchar(50)

Nvarchar(max)

Real

SmallDateTime

SmallInt

SmallMoney

Sql_Variant

Text

Timestamp

TinyInt

UniqueIdentifier

Varbinary(50)

VarBinary(max)

varchar(10)

Varchar(max)

XML

I choose Char with a size of 1. Would this be correct, or am I missing something?

Thanks in advance for your assistance!!

SqlByte maps to tinyint.