Question on System and Data Restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Thanks in advance.

GerryDataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.
>
Does SQL Server have anything similar?

I don't see why you would anything else than a normal database backup?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In DB2, to save off data and structure we use utilties db2look and
db2export.

I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

>
I don't see why you would anything else than a normal database backup?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

In DB2, to save off data and structure we use utilties db2look and
db2export.
>
I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

There are of course situations where you want to duplicate a schema or
copy the data from one database to another.

But since you talked about future recreation, it sounded more like a
backup to me.

Note that for development you should keep all your SQL code under
version control. If you do this, there is rarely any reason to script
from the database, since the version-control system holds the truth
about the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question on SQL Server SQL

In DB2 one can issue a query like

db2 -x 'select tabname from syscat.tables'

and the output is cleaned up so all you get is/are the requested table
name(s)...no dotted lines...no column headings.

Does SQL Server have a similar construct?

Thanks
Gerryno dotted lines...no column headings.???

Check out the information_schema views in bol. Otherwise check out system tables also in bol. Without the version of SS you are using I can't be more specific.|||I think what you are looking for is bcp.exe

it's a console app for exporting data from SQL Server to a flat file.|||I think what you are looking for is bcp.exeAh! Now I get it.|||specifically:

bcp "select name from mydatabase.dbo.sysobjects where xtype='U'" queryout file.txt -c -T -SSERVERNAME|||Sorry about not posting the version.
Its SQL Server 2005.

Ok so it seems that SQL Server SQL does not have the -x construct and that the bcp.exe is needed.

Thanks

Question on SQL Server 2000 - 2005

I am a DB2 DBA that has been asked to become familiar enough with SQL
Server in order to become actively involved in its installation,
implementation, and to review database backup/recovery procedures. SQL
Server will be used by a vendor packaged application.

The problem is that we are awaiting word on whether it will be SQL
Server 2000 or 2005 we will be working with. I have been told that
there are considerable differences between the two versions.

There is an upcoming local 5 day class on Administering SQL Server 2000
that I would like to take. (outline below)

Question is, are the differences between the two versions so drastic as
to render this class less than useful, especially in terms of
installation and basic db maintenance functions?

Many thanks in advance.
Gerry

Course Outline: 2072 Administering a Microsoft SQL Server 2000 Database

Lesson 1: SQL Server Overview

What Is SQL Server
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server

Top

Lesson 2: Planning to Install SQL Server

Hardware Installation Considerations
SQL Server 2000 Editions
Software Installation Considerations
Methods of Installing SQL Server
Verifying the Installation
Configuring SQL Server Enterprise Manager
Troubleshooting

Lesson 3: Managing Database Files

Introduction to Data Structures
Creating Databases
Managing Databases
Placing Database Files and Logs
Optimizing the Database Using Hardware-based RAID
Optimizing the Database Using Filegroups
Optimizing the Database Using Filegroups with Hardware-based
RAID
Capacity Planning
Performance Considerations

Lesson 4: Managing Security

Implementing an Authentication Mode
Assigning Logins to Users and Roles
Assigning Permissions to Users and Roles
Managing Security Within SQL Server
Managing Application Security
Managing SQL Server Security in the Enterprise

Lesson 5: Performing Administrative Tasks

Configuration Tasks
Routine SQL Server Administrative Tasks
Automating Routine Maintenance Tasks
Creating Alerts
Troubleshooting SQL Server Automation
Automating Multiserver Jobs

Top

Lesson 6: Backing Up Databases

Preventing Data Loss
Setting and Changing a Database Recovery Model
SQL Server Backup
When to Back Up Databases
Performing Backups
Types of Backup Methods
Planning a Backup Strategy
Performance Considerations

Lesson 7: Restoring Databases

SQL Server Recovery Process
Preparing to Restore a Database
Restoring Backups
Restoring Databases from Different Backup Types
Restoring Damaged System Databases

Lesson 8: Monitoring SQL Server for Performance

Why to Monitor SQL Server
Performance Monitoring and Tuning
Tools for Monitoring SQL Server
Common Monitoring and Tuning Tasks

Lesson 9: Transferring Data

Introduction to Transferring Data
Tools for Importing and Exporting Data in SQL Server
Introduction to DTS
Transforming Data with DTS

Top

Lesson 10: Maintaining High Availability

Introduction to Availability
Increasing Availability Using Failover Clustering
Standby Servers and Log Shipping

Lesson 11: Introducing Replication

Introduction to Distributed Data
Introduction to SQL Server Replication
SQL Server Replication Agents
SQL Server Replication Types
Physical Replication ModelsIn my humble opinion you better get training on SQL 2005. But knowledge
of SQL 2000 will add on to SQL 2005|||(datapro01@.yahoo.com) writes:
> Question is, are the differences between the two versions so drastic as
> to render this class less than useful, especially in terms of
> installation and basic db maintenance functions?

Everything that works in SQL 2000 works in SQL 2005, at least almost. But
SQL 2005 may offer new and better ways that what you learn on the SQL 2000
class.

The one area where the SQL 2000 class may be more confusing than helpful
if you settle on SQL 2005, is installation. The setup program is different,
there are different configuration issues etc.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the replies and the info. New Horizons is telling me that
the training materials for 2005 have not yet been released. At least to
them.

I will certainly do my best to understand the installation differences
between the versions.

Question on Data/structure restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Failing that, our plan is to backup the data and logs then image the entire disk.

Thanks in advance.

GerryNot exactly sure what you want.

SQL Server 2000 of course has backup and restore capability, and the Enterprise Manager utility has the ability to script database objects and relationships.

A lot depends upon why you are archiving the data and what its intended use is.|||OK. If you had to save off a database...both the data and the 'structure' of the tables, relationships between tables etc....for possible recreation years down the road...how would you do it?|||sql server has a backup wizard, backup the DB to a BKF file and put it wherever, you can restore it just as easily with the wizard - table structures and all the data

also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessary|||Look up BACKUP in Books online

Do you have the SQL Server Client tools installed?|||Also look here

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx|||also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessaryIn a Word document?
Just script the database ddl to a text file.|||I believe the scipting of the ddl of the objects in addition to a backup AND a data export is a desired redundancy. I belive the scripting capabilties of SS should do the job. That's for all the interesting info .

Gerry|||Should read Thanks :)