automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
qin_23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1835350.html
quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1835350.html
>
sql

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual
data from the old db to the new one, but i would like to take the table
structure. in DB2, i could run through a ddl file that contained CREATE
DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
tables, databases, and the like. how exactly do i do this using MS SQL
server? im thinking along the lines of writing my own file, and then
using something in SQL server to basically read the file and execute
the SQL commands. any ideas? thanks.
qin_23
--
qin_23
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message1835350.htmlquin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>

automating table creation

hi there.
moving from IBM DB2 to MS SQL server. i dont need to take the actual data fr
om the old db to the new one, but i would like to take the table structure.
in DB2, i could run through a ddl file that contained CREATE DATABASE, CREAT
E TABLE, etc. kinds of commands to automatically create tables, databases, a
nd the like. how exactly do i do this using MS SQL server? im thinking along
the lines of writing my own file, and then using something in SQL server to
basically read the file and execute the SQL commands. any ideas? thanks.
qin_23quin_23,
The easiest way is to generated the script, copy and paste it into Query
Analyser. Then run it. You may have some tweeking to do before it will
execute without error.
Alternatively, use osql to execute the DDL file.
-- Bill
"qin_23" <qin_23.2n3acb@.mail.webservertalk.com> wrote in message
news:qin_23.2n3acb@.mail.webservertalk.com...
> hi there.
> moving from IBM DB2 to MS SQL server. i dont need to take the actual
> data from the old db to the new one, but i would like to take the table
> structure. in DB2, i could run through a ddl file that contained CREATE
> DATABASE, CREATE TABLE, etc. kinds of commands to automatically create
> tables, databases, and the like. how exactly do i do this using MS SQL
> server? im thinking along the lines of writing my own file, and then
> using something in SQL server to basically read the file and execute
> the SQL commands. any ideas? thanks.
> qin_23
>
> --
> qin_23
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message1835350.html
>|||alright. thanks it worked.
qin_23

Automate restore

hello people!

I am very new to the SQL World. Currently I work on DB2 and ORACLE and
this is a new "fruit" for me.

I have the task to automate the following scenario:
- backup database A
- restore database A into database B (overwrite database B)
- perform the above every night at 23:00.

Can anyone indicate me where do i find info how to perform the above?
I know that you can do it through the Enterprise Manager but I need to
script the above (probably).

Thanks"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.
> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks

Demetris,

whilst I've restored databases I've never scripted it, so i can't help
directly. But, if your objective is to have a second database that is made
available for reporting purposes then have you considered using replication
instead? Replication with Oracle was a pig (it may be better now, I used it
with Oracle 8), but with SQL Server its a doddle. That way you could keep
your database B either almost up to date or you could schedule the
replication to only occur once every night.

Just an idea.

all the best with it,

Brian.

www.cryer.co.uk/brian|||Most operations you perform via Enterprise Manager can be performed directly
using Tranact-SQL scripts. This is handy for scheduling or performing
repetitive tasks.

You can schedule scripts like the examples below using a SQL Agent job. See
the Books Online for reference of the commands used in these scripts.

BACKUP DATABASE DatabaseA
TO DISK='C:\Backups\DatabaseA.bak'
WITH INIT

ALTER DATABASE DatabaseB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE DatabaseB
FROM DISK='C:\Backups\DatabaseA.bak'
WITH
MOVE 'DatabaseA' TO 'C:\DataFiles\DatabaseB.mdf',
MOVE 'DatabaseA_Log' TO 'D:\LogFiles\DatabaseB_Log.ldf',
REPLACE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.
> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks|||Thanks for your reply.
Do you have any links to give me on where to find out about the
replication on SQL Server?

"Brian Cryer" <brianc@.127.0.0.1.activesol.co.uk> wrote in message news:<1102073395.85278.0@.doris.uk.clara.net>...
> "Demetris" <DMina@.laiki.com> wrote in message
> news:c5285638.0412030136.955c184@.posting.google.co m...
> > hello people!
> > I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> > this is a new "fruit" for me.
> > I have the task to automate the following scenario:
> > - backup database A
> > - restore database A into database B (overwrite database B)
> > - perform the above every night at 23:00.
> > Can anyone indicate me where do i find info how to perform the above?
> > I know that you can do it through the Enterprise Manager but I need to
> > script the above (probably).
> > Thanks
> Demetris,
> whilst I've restored databases I've never scripted it, so i can't help
> directly. But, if your objective is to have a second database that is made
> available for reporting purposes then have you considered using replication
> instead? Replication with Oracle was a pig (it may be better now, I used it
> with Oracle 8), but with SQL Server its a doddle. That way you could keep
> your database B either almost up to date or you could schedule the
> replication to only occur once every night.
> Just an idea.
> all the best with it,
> Brian.
> www.cryer.co.uk/brian|||Brian Cryer (brianc@.127.0.0.1.activesol.co.uk) writes:
> whilst I've restored databases I've never scripted it, so i can't help
> directly. But, if your objective is to have a second database that is
> made available for reporting purposes then have you considered using
> replication instead? Replication with Oracle was a pig (it may be better
> now, I used it with Oracle 8), but with SQL Server its a doddle. That
> way you could keep your database B either almost up to date or you could
> schedule the replication to only occur once every night.

I guess that depends on the purpose of this database copying is. If there
is no need to have today's data in the copy database, the backup/restore
strategy is definitely preferrable. While replication may be simpler than
in Oracle, it still adds complexity to your system that is unnecessary if
not wanted.

As Dan demonstrated, using BACKUP/RESTORE commands is a real breeze, and
it can easily be added to an Agent job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412030136.955c184@.posting.google.co m...
> hello people!
> I am very new to the SQL World. Currently I work on DB2 and ORACLE and
> this is a new "fruit" for me.
> I have the task to automate the following scenario:
> - backup database A
> - restore database A into database B (overwrite database B)
> - perform the above every night at 23:00.

Google for Log Shipping.

You can do this as a single job.

I have a backup job on Server A that I created with a normal maintenance
plan and then added a second step.

That step is basically exec ServerB.master.dbo.restore_FOO

Restore_FOO looks like

CREATE procedure restore_FOO as

declare @.backup_file as varchar(255)

select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from serverA.msdb.dbo.backupset where database_name='FOO')

print @.backup_file

restore database FOO from disk=@.backup_file with
move 'FOO_Data' to 'e:\sql_data\FOO_data.mdf',
move 'FOO_Log' to 'f:\SQL_LOGs\FOO_log.ldf',
move 'FOO_Index' to 'g:\sql_index\FOO_Index_Data.NDF',
replace

--

However, if I could, I'd simply do this as a log-shipping job and ship over
the logs every 1/2 hour or so. That would be far faster and easier. But
given the way the DB is (and it's SQL 7.0) I don't have that choice here.

> Can anyone indicate me where do i find info how to perform the above?
> I know that you can do it through the Enterprise Manager but I need to
> script the above (probably).
> Thanks|||"Demetris" <DMina@.laiki.com> wrote in message
news:c5285638.0412031035.1d3ef489@.posting.google.c om...
> Thanks for your reply.
> Do you have any links to give me on where to find out about the
> replication on SQL Server?

The SQL Server books online (which you should have installed) is a very good
source of information. My own notes on replication are online at
http://www.cryer.co.uk/brian/sqlser...replication.htm, but they are a
little long in the tooth because they were written around SQL Server 7 and
not 2000 (but I doubt much has changed).

Hope this helps,

Brian.