How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it, I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for each
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file of
multiple backups to restore via script.
Any help much appreciated -Thanks
Amelia
Read this greate article
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks
|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:

> Amelia
> Read this greate article
> <http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> it, I
> DB
> each
> or
> of
>
>

How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it,
I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for eac
h
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file o
f
multiple backups to restore via script.
Any help much appreciated -ThanksAmelia
Read this greate article
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:

> Amelia
> Read this greate article
> <[url]http://vyaskn.tripod.com/ sql_server_administration_best_practices
.htm#Step1[/ur
l]
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> it, I
> DB
> each
> or
> of
>
>

How to create seperate Differential Backup Files

Instead of creating one file with the different Differential Backups in it, I
would like to create seperate files so I can script it easier - like the DB
Maintenance Plan wizard generates a job which creates seperate files for each
of the Full Backups.
I cannot seem to find the option to specify this thru the GUI/Script for
Differential DB Backups though. Using All Tasks->DB BAckup (only way to
create diff backup except via script?) you only get the choice of Append or
Overwrite Existing Media. Or alt, how do I specify which Backup in my file of
multiple backups to restore via script.
Any help much appreciated -ThanksAmelia
Read this greate article
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> Instead of creating one file with the different Differential Backups in
it, I
> would like to create seperate files so I can script it easier - like the
DB
> Maintenance Plan wizard generates a job which creates seperate files for
each
> of the Full Backups.
> I cannot seem to find the option to specify this thru the GUI/Script for
> Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> create diff backup except via script?) you only get the choice of Append
or
> Overwrite Existing Media. Or alt, how do I specify which Backup in my file
of
> multiple backups to restore via script.
> Any help much appreciated -Thanks|||Thanks Uri,
I read the article. It was good but similar to the Backup Strategy's I've
read in the Online Books in SQL Server.
I have the plan I want to implement but was stuck on my original question.
Regards
"Uri Dimant" wrote:
> Amelia
> Read this greate article
> <http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> > --administaiting best practices
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:6A9FF6C5-079E-4963-BECE-A0C5CABAA490@.microsoft.com...
> > Instead of creating one file with the different Differential Backups in
> it, I
> > would like to create seperate files so I can script it easier - like the
> DB
> > Maintenance Plan wizard generates a job which creates seperate files for
> each
> > of the Full Backups.
> >
> > I cannot seem to find the option to specify this thru the GUI/Script for
> > Differential DB Backups though. Using All Tasks->DB BAckup (only way to
> > create diff backup except via script?) you only get the choice of Append
> or
> > Overwrite Existing Media. Or alt, how do I specify which Backup in my file
> of
> > multiple backups to restore via script.
> >
> > Any help much appreciated -Thanks
>
>

How to create or save files in a different domain

Hey,

I have an SSIS package that creates a new file and saves results into that file. However, the requirement has it that I need to create this file on a server residing in a different domain. How can I achieve this in an SSIS package when it is scheduled as a job?

Thanks,

Aravind

When using SQLAgent, you should be aware of permission-related issues like the use of proxy accounts that may come into play. SSIS doesn't have special handling for different domains. For example, if you are using the flat file destination adapter to write data, it will just attempt to write to the file without recognizing where the file is residing. If you are having trouble writing directly to a file in a different domain, it might be easier if you create and write to a file locally using IS (feasibility depending on the size of file) and then copy it over using another mechanism that's proven to work across domains.|||

The obvious method would be to setup a trust between the domains, and just grant permission in the normal way. If that is not an option for you, then the only other method I have used is to manipulate files accross domains is NET USE the old DOS command. You can connect to a share and supply a username and password.

How to create maintenance plan to backup files and filegoups

Hello Everyone,
Database Maintenance Plan Wizard in SQL Server 2000 helps
to create a maintenance plan to perform database backups
on a regular basis. How could I create a plan to backup
file and filegroups on a regular basis.
I also appreciate if you would comment on what conditions
would you suggest to place a table in a different
filegroup than the rest of the tables in a database?
Thank you,
-Mitra
Mitra,
You can't use the Maintenance Plan to perform backups other than Full and
Log. You can easily create your own scheduled job to do this though. The
two biggest reasons why you might place a table in a separate filegroup are:
1. You have two drive arrays and want to place the table (Data or clustered
index) on one and the non-clustered indexes onthe other. Done when you have
heavy access to the table and lots of drive space.
2. If you need to back up one or several tables at a different schedule
than the others. This might be because you have large tables that are
mostly static and don't need backing up each night or if you simply have
VLDB's that take too long to backup all at once.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> Hello Everyone,
> Database Maintenance Plan Wizard in SQL Server 2000 helps
> to create a maintenance plan to perform database backups
> on a regular basis. How could I create a plan to backup
> file and filegroups on a regular basis.
> I also appreciate if you would comment on what conditions
> would you suggest to place a table in a different
> filegroup than the rest of the tables in a database?
> Thank you,
> -Mitra
|||In addition to Andrew's explanation, filegroups can provide you with the
ability to make part of your database read-only for a period of the time(and
bring it to read-write state when finished your job). It could be useful in
particular situations for administrative tasks.
Also I believe it would be still better to keep the data(table) and indexes
in two different filegroups even when we have one physical disk dedicated to
them, because at least it could help to reduce the fragmentation's growth
(IMHO of course!).
Regards,
Amin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OORtG31nEHA.3216@.tk2msftngp13.phx.gbl...
> Mitra,
> You can't use the Maintenance Plan to perform backups other than Full and
> Log. You can easily create your own scheduled job to do this though. The
> two biggest reasons why you might place a table in a separate filegroup
are:
> 1. You have two drive arrays and want to place the table (Data or
clustered
> index) on one and the non-clustered indexes onthe other. Done when you
have
> heavy access to the table and lots of drive space.
> 2. If you need to back up one or several tables at a different schedule
> than the others. This might be because you have large tables that are
> mostly static and don't need backing up each night or if you simply have
> VLDB's that take too long to backup all at once.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
> news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
>
|||Thanks everyone for the suggestions!
One of the reasons I am thinking that I should take this
approach, creating this one table on a different
filegroup, is because we have this one table that
consumes 75% of the size of the database. And it will
take long time to backup the whole database. This
particular table is not a statice table. It has three
columns, id(int), size(int), content(Text). And we are
storing the message body of all emails inside the
organization inside the content column.
Do you think one table causing a database to become a
VLDB is good enough reason to create this one table on a
separate filegroup to reduce to time of the backup?
Thanks,
-Mitra

>--Original Message--
>Mitra,
>You can't use the Maintenance Plan to perform backups
other than Full and
>Log. You can easily create your own scheduled job to do
this though. The
>two biggest reasons why you might place a table in a
separate filegroup are:
>1. You have two drive arrays and want to place the
table (Data or clustered
>index) on one and the non-clustered indexes onthe
other. Done when you have
>heavy access to the table and lots of drive space.
>2. If you need to back up one or several tables at a
different schedule
>than the others. This might be because you have large
tables that are
>mostly static and don't need backing up each night or if
you simply have
>VLDB's that take too long to backup all at once.
>
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
helps[vbcol=seagreen]
backups[vbcol=seagreen]
conditions
>
>.
>
|||Mitra,
SQL Server can under take the task for physically separating the BLOBs
('Content' field for your table) from the rest of table.
If the speed of backup operation is the most important factor, you could
place the BLOBs on other filegroup(physical disk) and add a mirrored disk
for that filegroup. By this approach, imagine that the important part of
table is being backed up as you write to the table.
Although it does not completely eliminate the need for backup, but now
you're able to backup only 'Content' field of table that will cause to
reduce time and size of backup.
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> other than Full and
> this though. The
> separate filegroup are:
> table (Data or clustered
> other. Done when you have
> different schedule
> tables that are
> you simply have
> wrote in message
> helps
> backups
> conditions
|||If the table is 75% of the entire db you won't save a whole lot of time by
splitting them but it si certainly an option. I would look at other
measures to speed it up. How are you doing backups now? Have you tried SQL
LiteSpeed? Have you thought of archiving some of the table?
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> other than Full and
> this though. The
> separate filegroup are:
> table (Data or clustered
> other. Done when you have
> different schedule
> tables that are
> you simply have
> wrote in message
> helps
> backups
> conditions
|||Andrew, currently, we have created a maintenance plan
using Enterprise Manager to perform our database backups.
We have not tried SQLLiteSpeed.
You asked if we have thought about archiving some of the
tables. What is archiving table?
Amin, the time it takes to perform a database backup is
what we are trying to reduce.
You suggested to place only the TEXT column, 'Content',
on a different filegroup than the table itself. I read
the same thing in Book Online that any text column within
a table can be created on a filegroup other than the one
that contains the base table. Why separating only the
column? What's the advantage?
After creating and populating a table or a column in a
different filegroup, is there a way to incorporate this
specific filegroup with the default filegroup if we had
to?
Thanks,
-Mitra

>--Original Message--
>If the table is 75% of the entire db you won't save a
whole lot of time by
>splitting them but it si certainly an option. I would
look at other
>measures to speed it up. How are you doing backups
now? Have you tried SQL
>LiteSpeed? Have you thought of archiving some of the
table?
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
this[vbcol=seagreen]
a[vbcol=seagreen]
do[vbcol=seagreen]
if[vbcol=seagreen]
backup
>
>.
>
|||Mitra,
I was hoping to get a little more detail about exactly how you are backing
the db up currently. Are you backing up directly to tape or to disks? If
it is to disk is this a disk array that is separate from any of the data or
log files? What type array is it? How large is the database? How long does
it currently take etc.?
There are lots of things you can do to speed up a backup but depending on
how you are doing it now. Chances are you are backing up to a relatively
slow disk array or one that has only a few physical disks in it. If your
disk array has lots of capacity or better yet you have multiple disk arrays
you can issue a backup with multiple files. This can make a big difference
in the time it actually takes to complete the backup depending on the
hardware and number of processors.
You should definitely look at using SQL LiteSpeed to do your backups as
well. It can not only backup to multiple files but can compress the backup
on the fly at the same time. This not only results in backup files that are
dramatically smaller than the originals but speeds the backup process at the
same time. www.imceda.com
By archiving I was referring to removing some of the data that you might not
need anymore. Or possibly placing older emails in a separate database once
every so often to keep the main db size at a reasonable level.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...[vbcol=seagreen]
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> whole lot of time by
> look at other
> now? Have you tried SQL
> table?
> wrote in message
> this
> a
> do
> if
> backup
|||Mitra,
A table can be placed on maximum of two different filegroups. One specified
for BOLBs(fields of type text,ntext,image) and one specified for non-BLOBs.
Therefore in your table, only 'Content' field can be separated.
When you backup filegroup of 'Contents' field, the amount of data that is
being backed up is smaller than the whole of table.
Please attention to this text from books online:
**************
Microsoft SQL ServerT 2000 supports backing up or restoring individual
files or file groups within a database. This is a relatively sophisticated
backup and restore process usually reserved for very large databases (VLDB)
with high availability requirements. If the time available for backups is
not long enough to support backing up the full database, subsets of the
database can be backed up at different times.
For example, it takes three hours for a site to back up a database, and
backups can be performed only during a two-hour period each day. The site
can back up half the files or file groups on one night and half the next. If
a disk holding database files or filegroups fails, the site can restore just
the lost files or filegroups. The site must also be making transaction log
backups, and must restore all transaction log backups made after the file or
filegroup backup.
File and filegroup restores can also be made from a full database backup
set. This allows for a quicker recovery because only the damaged files or
filegroups are restored in the first step, not the entire database.
**************
And yes. You may change the filegroup of BLOBs if you have to. In EM, go to
design mode of your table. Bring up the properties dialogbox. Note the 'Text
Filegroup' and 'Table Filegroup' dropdown lists.
However this is good solution when you don't have enough budgette to spend
on more disks. If you can dedicate more disks for backup operation, it will
be much faster to make a media family consisting of two or more files(on
your additional disk).
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...[vbcol=seagreen]
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> whole lot of time by
> look at other
> now? Have you tried SQL
> table?
> wrote in message
> this
> a
> do
> if
> backup

How to create maintenance plan to backup files and filegoups

Hello Everyone,
Database Maintenance Plan Wizard in SQL Server 2000 helps
to create a maintenance plan to perform database backups
on a regular basis. How could I create a plan to backup
file and filegroups on a regular basis.
I also appreciate if you would comment on what conditions
would you suggest to place a table in a different
filegroup than the rest of the tables in a database?
Thank you,
-MitraMitra,
You can't use the Maintenance Plan to perform backups other than Full and
Log. You can easily create your own scheduled job to do this though. The
two biggest reasons why you might place a table in a separate filegroup are:
1. You have two drive arrays and want to place the table (Data or clustered
index) on one and the non-clustered indexes onthe other. Done when you have
heavy access to the table and lots of drive space.
2. If you need to back up one or several tables at a different schedule
than the others. This might be because you have large tables that are
mostly static and don't need backing up each night or if you simply have
VLDB's that take too long to backup all at once.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> Hello Everyone,
> Database Maintenance Plan Wizard in SQL Server 2000 helps
> to create a maintenance plan to perform database backups
> on a regular basis. How could I create a plan to backup
> file and filegroups on a regular basis.
> I also appreciate if you would comment on what conditions
> would you suggest to place a table in a different
> filegroup than the rest of the tables in a database?
> Thank you,
> -Mitra|||In addition to Andrew's explanation, filegroups can provide you with the
ability to make part of your database read-only for a period of the time(and
bring it to read-write state when finished your job). It could be useful in
particular situations for administrative tasks.
Also I believe it would be still better to keep the data(table) and indexes
in two different filegroups even when we have one physical disk dedicated to
them, because at least it could help to reduce the fragmentation's growth
(IMHO of course!).
Regards,
Amin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OORtG31nEHA.3216@.tk2msftngp13.phx.gbl...
> Mitra,
> You can't use the Maintenance Plan to perform backups other than Full and
> Log. You can easily create your own scheduled job to do this though. The
> two biggest reasons why you might place a table in a separate filegroup
are:
> 1. You have two drive arrays and want to place the table (Data or
clustered
> index) on one and the non-clustered indexes onthe other. Done when you
have
> heavy access to the table and lots of drive space.
> 2. If you need to back up one or several tables at a different schedule
> than the others. This might be because you have large tables that are
> mostly static and don't need backing up each night or if you simply have
> VLDB's that take too long to backup all at once.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
> news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> > Hello Everyone,
> >
> > Database Maintenance Plan Wizard in SQL Server 2000 helps
> > to create a maintenance plan to perform database backups
> > on a regular basis. How could I create a plan to backup
> > file and filegroups on a regular basis.
> >
> > I also appreciate if you would comment on what conditions
> > would you suggest to place a table in a different
> > filegroup than the rest of the tables in a database?
> >
> > Thank you,
> >
> > -Mitra
>|||Thanks everyone for the suggestions!
One of the reasons I am thinking that I should take this
approach, creating this one table on a different
filegroup, is because we have this one table that
consumes 75% of the size of the database. And it will
take long time to backup the whole database. This
particular table is not a statice table. It has three
columns, id(int), size(int), content(Text). And we are
storing the message body of all emails inside the
organization inside the content column.
Do you think one table causing a database to become a
VLDB is good enough reason to create this one table on a
separate filegroup to reduce to time of the backup?
Thanks,
-Mitra
>--Original Message--
>Mitra,
>You can't use the Maintenance Plan to perform backups
other than Full and
>Log. You can easily create your own scheduled job to do
this though. The
>two biggest reasons why you might place a table in a
separate filegroup are:
>1. You have two drive arrays and want to place the
table (Data or clustered
>index) on one and the non-clustered indexes onthe
other. Done when you have
>heavy access to the table and lots of drive space.
>2. If you need to back up one or several tables at a
different schedule
>than the others. This might be because you have large
tables that are
>mostly static and don't need backing up each night or if
you simply have
>VLDB's that take too long to backup all at once.
>
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
>> Hello Everyone,
>> Database Maintenance Plan Wizard in SQL Server 2000
helps
>> to create a maintenance plan to perform database
backups
>> on a regular basis. How could I create a plan to backup
>> file and filegroups on a regular basis.
>> I also appreciate if you would comment on what
conditions
>> would you suggest to place a table in a different
>> filegroup than the rest of the tables in a database?
>> Thank you,
>> -Mitra
>
>.
>|||Mitra,
SQL Server can under take the task for physically separating the BLOBs
('Content' field for your table) from the rest of table.
If the speed of backup operation is the most important factor, you could
place the BLOBs on other filegroup(physical disk) and add a mirrored disk
for that filegroup. By this approach, imagine that the important part of
table is being backed up as you write to the table.
Although it does not completely eliminate the need for backup, but now
you're able to backup only 'Content' field of table that will cause to
reduce time and size of backup.
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> >--Original Message--
> >Mitra,
> >
> >You can't use the Maintenance Plan to perform backups
> other than Full and
> >Log. You can easily create your own scheduled job to do
> this though. The
> >two biggest reasons why you might place a table in a
> separate filegroup are:
> >
> >1. You have two drive arrays and want to place the
> table (Data or clustered
> >index) on one and the non-clustered indexes onthe
> other. Done when you have
> >heavy access to the table and lots of drive space.
> >2. If you need to back up one or several tables at a
> different schedule
> >than the others. This might be because you have large
> tables that are
> >mostly static and don't need backing up each night or if
> you simply have
> >VLDB's that take too long to backup all at once.
> >
> >
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> Hello Everyone,
> >>
> >> Database Maintenance Plan Wizard in SQL Server 2000
> helps
> >> to create a maintenance plan to perform database
> backups
> >> on a regular basis. How could I create a plan to backup
> >> file and filegroups on a regular basis.
> >>
> >> I also appreciate if you would comment on what
> conditions
> >> would you suggest to place a table in a different
> >> filegroup than the rest of the tables in a database?
> >>
> >> Thank you,
> >>
> >> -Mitra
> >
> >
> >.
> >|||If the table is 75% of the entire db you won't save a whole lot of time by
splitting them but it si certainly an option. I would look at other
measures to speed it up. How are you doing backups now? Have you tried SQL
LiteSpeed? Have you thought of archiving some of the table?
--
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> Thanks everyone for the suggestions!
> One of the reasons I am thinking that I should take this
> approach, creating this one table on a different
> filegroup, is because we have this one table that
> consumes 75% of the size of the database. And it will
> take long time to backup the whole database. This
> particular table is not a statice table. It has three
> columns, id(int), size(int), content(Text). And we are
> storing the message body of all emails inside the
> organization inside the content column.
> Do you think one table causing a database to become a
> VLDB is good enough reason to create this one table on a
> separate filegroup to reduce to time of the backup?
> Thanks,
> -Mitra
>
> >--Original Message--
> >Mitra,
> >
> >You can't use the Maintenance Plan to perform backups
> other than Full and
> >Log. You can easily create your own scheduled job to do
> this though. The
> >two biggest reasons why you might place a table in a
> separate filegroup are:
> >
> >1. You have two drive arrays and want to place the
> table (Data or clustered
> >index) on one and the non-clustered indexes onthe
> other. Done when you have
> >heavy access to the table and lots of drive space.
> >2. If you need to back up one or several tables at a
> different schedule
> >than the others. This might be because you have large
> tables that are
> >mostly static and don't need backing up each night or if
> you simply have
> >VLDB's that take too long to backup all at once.
> >
> >
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> Hello Everyone,
> >>
> >> Database Maintenance Plan Wizard in SQL Server 2000
> helps
> >> to create a maintenance plan to perform database
> backups
> >> on a regular basis. How could I create a plan to backup
> >> file and filegroups on a regular basis.
> >>
> >> I also appreciate if you would comment on what
> conditions
> >> would you suggest to place a table in a different
> >> filegroup than the rest of the tables in a database?
> >>
> >> Thank you,
> >>
> >> -Mitra
> >
> >
> >.
> >|||Andrew, currently, we have created a maintenance plan
using Enterprise Manager to perform our database backups.
We have not tried SQLLiteSpeed.
You asked if we have thought about archiving some of the
tables. What is archiving table?
Amin, the time it takes to perform a database backup is
what we are trying to reduce.
You suggested to place only the TEXT column, 'Content',
on a different filegroup than the table itself. I read
the same thing in Book Online that any text column within
a table can be created on a filegroup other than the one
that contains the base table. Why separating only the
column? What's the advantage?
After creating and populating a table or a column in a
different filegroup, is there a way to incorporate this
specific filegroup with the default filegroup if we had
to?
Thanks,
-Mitra
>--Original Message--
>If the table is 75% of the entire db you won't save a
whole lot of time by
>splitting them but it si certainly an option. I would
look at other
>measures to speed it up. How are you doing backups
now? Have you tried SQL
>LiteSpeed? Have you thought of archiving some of the
table?
>--
>Andrew J. Kelly SQL MVP
>
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
>> Thanks everyone for the suggestions!
>> One of the reasons I am thinking that I should take
this
>> approach, creating this one table on a different
>> filegroup, is because we have this one table that
>> consumes 75% of the size of the database. And it will
>> take long time to backup the whole database. This
>> particular table is not a statice table. It has three
>> columns, id(int), size(int), content(Text). And we are
>> storing the message body of all emails inside the
>> organization inside the content column.
>> Do you think one table causing a database to become a
>> VLDB is good enough reason to create this one table on
a
>> separate filegroup to reduce to time of the backup?
>> Thanks,
>> -Mitra
>>
>> >--Original Message--
>> >Mitra,
>> >
>> >You can't use the Maintenance Plan to perform backups
>> other than Full and
>> >Log. You can easily create your own scheduled job to
do
>> this though. The
>> >two biggest reasons why you might place a table in a
>> separate filegroup are:
>> >
>> >1. You have two drive arrays and want to place the
>> table (Data or clustered
>> >index) on one and the non-clustered indexes onthe
>> other. Done when you have
>> >heavy access to the table and lots of drive space.
>> >2. If you need to back up one or several tables at a
>> different schedule
>> >than the others. This might be because you have large
>> tables that are
>> >mostly static and don't need backing up each night or
if
>> you simply have
>> >VLDB's that take too long to backup all at once.
>> >
>> >
>> >
>> >--
>> >Andrew J. Kelly SQL MVP
>> >
>> >
>> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
>> >> Hello Everyone,
>> >>
>> >> Database Maintenance Plan Wizard in SQL Server 2000
>> helps
>> >> to create a maintenance plan to perform database
>> backups
>> >> on a regular basis. How could I create a plan to
backup
>> >> file and filegroups on a regular basis.
>> >>
>> >> I also appreciate if you would comment on what
>> conditions
>> >> would you suggest to place a table in a different
>> >> filegroup than the rest of the tables in a database?
>> >>
>> >> Thank you,
>> >>
>> >> -Mitra
>> >
>> >
>> >.
>> >
>
>.
>|||Mitra,
I was hoping to get a little more detail about exactly how you are backing
the db up currently. Are you backing up directly to tape or to disks? If
it is to disk is this a disk array that is separate from any of the data or
log files? What type array is it? How large is the database? How long does
it currently take etc.?
There are lots of things you can do to speed up a backup but depending on
how you are doing it now. Chances are you are backing up to a relatively
slow disk array or one that has only a few physical disks in it. If your
disk array has lots of capacity or better yet you have multiple disk arrays
you can issue a backup with multiple files. This can make a big difference
in the time it actually takes to complete the backup depending on the
hardware and number of processors.
You should definitely look at using SQL LiteSpeed to do your backups as
well. It can not only backup to multiple files but can compress the backup
on the fly at the same time. This not only results in backup files that are
dramatically smaller than the originals but speeds the backup process at the
same time. www.imceda.com
By archiving I was referring to removing some of the data that you might not
need anymore. Or possibly placing older emails in a separate database once
every so often to keep the main db size at a reasonable level.
Andrew J. Kelly SQL MVP
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> >--Original Message--
> >If the table is 75% of the entire db you won't save a
> whole lot of time by
> >splitting them but it si certainly an option. I would
> look at other
> >measures to speed it up. How are you doing backups
> now? Have you tried SQL
> >LiteSpeed? Have you thought of archiving some of the
> table?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> >> Thanks everyone for the suggestions!
> >>
> >> One of the reasons I am thinking that I should take
> this
> >> approach, creating this one table on a different
> >> filegroup, is because we have this one table that
> >> consumes 75% of the size of the database. And it will
> >> take long time to backup the whole database. This
> >> particular table is not a statice table. It has three
> >> columns, id(int), size(int), content(Text). And we are
> >> storing the message body of all emails inside the
> >> organization inside the content column.
> >> Do you think one table causing a database to become a
> >> VLDB is good enough reason to create this one table on
> a
> >> separate filegroup to reduce to time of the backup?
> >>
> >> Thanks,
> >>
> >> -Mitra
> >>
> >>
> >> >--Original Message--
> >> >Mitra,
> >> >
> >> >You can't use the Maintenance Plan to perform backups
> >> other than Full and
> >> >Log. You can easily create your own scheduled job to
> do
> >> this though. The
> >> >two biggest reasons why you might place a table in a
> >> separate filegroup are:
> >> >
> >> >1. You have two drive arrays and want to place the
> >> table (Data or clustered
> >> >index) on one and the non-clustered indexes onthe
> >> other. Done when you have
> >> >heavy access to the table and lots of drive space.
> >> >2. If you need to back up one or several tables at a
> >> different schedule
> >> >than the others. This might be because you have large
> >> tables that are
> >> >mostly static and don't need backing up each night or
> if
> >> you simply have
> >> >VLDB's that take too long to backup all at once.
> >> >
> >> >
> >> >
> >> >--
> >> >Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> >> wrote in message
> >> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> >> Hello Everyone,
> >> >>
> >> >> Database Maintenance Plan Wizard in SQL Server 2000
> >> helps
> >> >> to create a maintenance plan to perform database
> >> backups
> >> >> on a regular basis. How could I create a plan to
> backup
> >> >> file and filegroups on a regular basis.
> >> >>
> >> >> I also appreciate if you would comment on what
> >> conditions
> >> >> would you suggest to place a table in a different
> >> >> filegroup than the rest of the tables in a database?
> >> >>
> >> >> Thank you,
> >> >>
> >> >> -Mitra
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Mitra,
A table can be placed on maximum of two different filegroups. One specified
for BOLBs(fields of type text,ntext,image) and one specified for non-BLOBs.
Therefore in your table, only 'Content' field can be separated.
When you backup filegroup of 'Contents' field, the amount of data that is
being backed up is smaller than the whole of table.
Please attention to this text from books online:
**************
Microsoft® SQL ServerT 2000 supports backing up or restoring individual
files or file groups within a database. This is a relatively sophisticated
backup and restore process usually reserved for very large databases (VLDB)
with high availability requirements. If the time available for backups is
not long enough to support backing up the full database, subsets of the
database can be backed up at different times.
For example, it takes three hours for a site to back up a database, and
backups can be performed only during a two-hour period each day. The site
can back up half the files or file groups on one night and half the next. If
a disk holding database files or filegroups fails, the site can restore just
the lost files or filegroups. The site must also be making transaction log
backups, and must restore all transaction log backups made after the file or
filegroup backup.
File and filegroup restores can also be made from a full database backup
set. This allows for a quicker recovery because only the damaged files or
filegroups are restored in the first step, not the entire database.
**************
And yes. You may change the filegroup of BLOBs if you have to. In EM, go to
design mode of your table. Bring up the properties dialogbox. Note the 'Text
Filegroup' and 'Table Filegroup' dropdown lists.
However this is good solution when you don't have enough budgette to spend
on more disks. If you can dedicate more disks for backup operation, it will
be much faster to make a media family consisting of two or more files(on
your additional disk).
Amin
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:359601c4a039$3c005010$a501280a@.phx.gbl...
> Andrew, currently, we have created a maintenance plan
> using Enterprise Manager to perform our database backups.
> We have not tried SQLLiteSpeed.
> You asked if we have thought about archiving some of the
> tables. What is archiving table?
> Amin, the time it takes to perform a database backup is
> what we are trying to reduce.
> You suggested to place only the TEXT column, 'Content',
> on a different filegroup than the table itself. I read
> the same thing in Book Online that any text column within
> a table can be created on a filegroup other than the one
> that contains the base table. Why separating only the
> column? What's the advantage?
> After creating and populating a table or a column in a
> different filegroup, is there a way to incorporate this
> specific filegroup with the default filegroup if we had
> to?
> Thanks,
> -Mitra
> >--Original Message--
> >If the table is 75% of the entire db you won't save a
> whole lot of time by
> >splitting them but it si certainly an option. I would
> look at other
> >measures to speed it up. How are you doing backups
> now? Have you tried SQL
> >LiteSpeed? Have you thought of archiving some of the
> table?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0bca01c49ff0$b3bd46b0$a601280a@.phx.gbl...
> >> Thanks everyone for the suggestions!
> >>
> >> One of the reasons I am thinking that I should take
> this
> >> approach, creating this one table on a different
> >> filegroup, is because we have this one table that
> >> consumes 75% of the size of the database. And it will
> >> take long time to backup the whole database. This
> >> particular table is not a statice table. It has three
> >> columns, id(int), size(int), content(Text). And we are
> >> storing the message body of all emails inside the
> >> organization inside the content column.
> >> Do you think one table causing a database to become a
> >> VLDB is good enough reason to create this one table on
> a
> >> separate filegroup to reduce to time of the backup?
> >>
> >> Thanks,
> >>
> >> -Mitra
> >>
> >>
> >> >--Original Message--
> >> >Mitra,
> >> >
> >> >You can't use the Maintenance Plan to perform backups
> >> other than Full and
> >> >Log. You can easily create your own scheduled job to
> do
> >> this though. The
> >> >two biggest reasons why you might place a table in a
> >> separate filegroup are:
> >> >
> >> >1. You have two drive arrays and want to place the
> >> table (Data or clustered
> >> >index) on one and the non-clustered indexes onthe
> >> other. Done when you have
> >> >heavy access to the table and lots of drive space.
> >> >2. If you need to back up one or several tables at a
> >> different schedule
> >> >than the others. This might be because you have large
> >> tables that are
> >> >mostly static and don't need backing up each night or
> if
> >> you simply have
> >> >VLDB's that take too long to backup all at once.
> >> >
> >> >
> >> >
> >> >--
> >> >Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> >"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
> >> wrote in message
> >> >news:275201c49f55$5cd18cd0$a501280a@.phx.gbl...
> >> >> Hello Everyone,
> >> >>
> >> >> Database Maintenance Plan Wizard in SQL Server 2000
> >> helps
> >> >> to create a maintenance plan to perform database
> >> backups
> >> >> on a regular basis. How could I create a plan to
> backup
> >> >> file and filegroups on a regular basis.
> >> >>
> >> >> I also appreciate if you would comment on what
> >> conditions
> >> >> would you suggest to place a table in a different
> >> >> filegroup than the rest of the tables in a database?
> >> >>
> >> >> Thank you,
> >> >>
> >> >> -Mitra
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

How to create Logs?

Can anyone please tell me where and how to configure SQL log files. I am a
Network Admin person and don't know anything in SQL.
Can you specify if its server error log file or database log file?
Thanks,
Sree
"iice" wrote:

> Can anyone please tell me where and how to configure SQL log files. I am a
> Network Admin person and don't know anything in SQL.
|||I want to know all the details of the database starting from accessing,
execution details, error logs etc. Is there any way of seeing? Pls do help me.
"Sreejith G" wrote:
[vbcol=seagreen]
> Can you specify if its server error log file or database log file?
> Thanks,
> Sree
> "iice" wrote:

How to create Logs?

Can anyone please tell me where and how to configure SQL log files. I am a
Network Admin person and don't know anything in SQL.Can you specify if its server error log file or database log file?
Thanks,
Sree
"iice" wrote:

> Can anyone please tell me where and how to configure SQL log files. I am a
> Network Admin person and don't know anything in SQL.|||I want to know all the details of the database starting from accessing,
execution details, error logs etc. Is there any way of seeing? Pls do help m
e.
"Sreejith G" wrote:
[vbcol=seagreen]
> Can you specify if its server error log file or database log file?
> Thanks,
> Sree
> "iice" wrote:
>sql

How to create Logs?

Can anyone please tell me where and how to configure SQL log files. I am a
Network Admin person and don't know anything in SQL.Can you specify if its server error log file or database log file?
Thanks,
Sree
"iice" wrote:
> Can anyone please tell me where and how to configure SQL log files. I am a
> Network Admin person and don't know anything in SQL.|||I want to know all the details of the database starting from accessing,
execution details, error logs etc. Is there any way of seeing? Pls do help me.
"Sreejith G" wrote:
> Can you specify if its server error log file or database log file?
> Thanks,
> Sree
> "iice" wrote:
> > Can anyone please tell me where and how to configure SQL log files. I am a
> > Network Admin person and don't know anything in SQL.

How to create Link Server ODBC DBase

Hi,
I tried a lot of things to connect a linked server to an ODBC for dBase IV
and I didn't get any successfull result. I have the path, the files names an
d
sysadmin access to my SQL server. I got a lot confused on all connection
string that news group use to connect to an ODBC for dBase. Does someone can
help me creating my Linked Server or tell me a link or doc that explain it
carefully?
Here is my last try:
EXEC master.dbo.sp_addlinkedserver @.server = @.LinkedServerName,
@.srvproduct=N'Haha', @.provider=N'Microsoft.Jet.OLEDB.4.0',
@.datasrc=N'eTimeDB', @.provstr=N'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=S:\DBFFiles;Extended Properties=dBase IV;User ID=Admin; Password=;'
but this doesn't work:
select * from
OPENQUERY( ETimeODBC04, 'SELECT * FROM EMP')
Thanks a lot in advance.
DavidI'm using a SQL Server 2005 server
"David Parenteau" wrote:

> Hi,
> I tried a lot of things to connect a linked server to an ODBC for dBase IV
> and I didn't get any successfull result. I have the path, the files names
and
> sysadmin access to my SQL server. I got a lot confused on all connection
> string that news group use to connect to an ODBC for dBase. Does someone c
an
> help me creating my Linked Server or tell me a link or doc that explain it
> carefully?
> Here is my last try:
> EXEC master.dbo.sp_addlinkedserver @.server = @.LinkedServerName,
> @.srvproduct=N'Haha', @.provider=N'Microsoft.Jet.OLEDB.4.0',
> @.datasrc=N'eTimeDB', @.provstr=N'Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=S:\DBFFiles;Extended Properties=dBase IV;User ID=Admin; Password=;'
> but this doesn't work:
> select * from
> OPENQUERY( ETimeODBC04, 'SELECT * FROM EMP')
> Thanks a lot in advance.
> David

How to create Link Server ODBC DBase

Hi,
I tried a lot of things to connect a linked server to an ODBC for dBase IV
and I didn't get any successfull result. I have the path, the files names and
sysadmin access to my SQL server. I got a lot confused on all connection
string that news group use to connect to an ODBC for dBase. Does someone can
help me creating my Linked Server or tell me a link or doc that explain it
carefully?
Here is my last try:
EXEC master.dbo.sp_addlinkedserver @.server = @.LinkedServerName,
@.srvproduct=N'Haha', @.provider=N'Microsoft.Jet.OLEDB.4.0',
@.datasrc=N'eTimeDB', @.provstr=N'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=S:\DBFFiles;Extended Properties=dBase IV;User ID=Admin; Password=;'
but this doesn't work:
select * from
OPENQUERY( ETimeODBC04, 'SELECT * FROM EMP')
Thanks a lot in advance.
David
I'm using a SQL Server 2005 server
"David Parenteau" wrote:

> Hi,
> I tried a lot of things to connect a linked server to an ODBC for dBase IV
> and I didn't get any successfull result. I have the path, the files names and
> sysadmin access to my SQL server. I got a lot confused on all connection
> string that news group use to connect to an ODBC for dBase. Does someone can
> help me creating my Linked Server or tell me a link or doc that explain it
> carefully?
> Here is my last try:
> EXEC master.dbo.sp_addlinkedserver @.server = @.LinkedServerName,
> @.srvproduct=N'Haha', @.provider=N'Microsoft.Jet.OLEDB.4.0',
> @.datasrc=N'eTimeDB', @.provstr=N'Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=S:\DBFFiles;Extended Properties=dBase IV;User ID=Admin; Password=;'
> but this doesn't work:
> select * from
> OPENQUERY( ETimeODBC04, 'SELECT * FROM EMP')
> Thanks a lot in advance.
> David
sql

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.
You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--TEXTCOPY OUT
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.
|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table
.
My table contains three fields, file name, file data, and an index. I need t
o
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value fro
m the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from[/vbc
ol]
the[vbcol=seagreen]
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a tab
le.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name, whic
h
> is already in the correct format [filename.txt] and I need the value f
rom the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:
> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels
sql

How to Create Assembly function using dll files in SQL Server 2005?

Hiiiiiiii all

I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration

I have tried like this

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

WITH PERMISSION_SET = SAFE

but it gives me

incorrect syntax error

so plzzzzz anyone help me wht to do in my probbbbbbbbb?

Pratik Kansara

What is the exact error-message, and are you sure you are doing it on the SQL 2005 instance (I seem to remember you have both a SQL 2000 as well as 2005 installed)?

Niels
|||
Hiii

yes i have both sql server 2000 and sql server 2005 and .net 2003 and .net 2005 both are installed

Actually there is no any type of group by concate function in sql server 2000- 2005 so tht i have made a class file in c# 2.0
and make a dll file of tht class file

and now sql server 2005 is supporting CLR Integration so tht i want to use tht function in my SQL Server 2005 instead of group by concate, I have already enabled clr enable in my sql server 2005.

but as i think i need to create a assembly function in sql server like

CREATE ASSEMBLY abc
From "c:\abc.dll"

So this statement gives me error so if you have any other option to do this then plz plz help me

thanking youuuuuuuuuuuuuuu...

Pratik Kansara
|||Yes, I understand your problem from your first post. So once again:
1. what is the exact error message you are getting
2. are you sure you are running the CREATE ASSEMBLY statement on the SQL 2005 instance.

The reason I'm asking no 2, is becuase your statement looks OK to me - and the only thing I can think of is that you try to run it on the SQL 2000 instance.

Niels
|||

Hiiiiiii

the exactly error message and syntax is like tht

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message i m getting is:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in sql server 2000 instance but as far as i know tht CLR Integration in SQL Server 2000 is not possible.

plz plz give me solution, i need it urgently...

Thnxxxxxxx

Pratik Kansara

|||

Hiiiii

the exact code tht i m writing is and error message is as bellow...

CREATE ASSEMBLY abc

FROM 'C:\abc.dll'

GO

and the error message is :

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ASSEMBLY'.

and i have also tried in SQL Server 2000 instance also but as far as i know tht this is not possible in SQL Server 2000 because SQL Server 2000 does not support CLR Integration

so plzz plzzzz help me i need it urgently

Thnxxxxxxxxx so muchhh

Pratik

|||You are right, CLR integration is not supported in SQL 2000, and that is why I believe that when you run your CREATE ASSEMBLY code that you are logged into the SQL 2000 system, and not SQL 2005.

Couple of more questions:

1. Afe you absolutely sure you are running the statement in the SQL 2005 instance? What is the result from following query:
SELECT @.@.version
2. What tool are you using when you run the CREATE ASSEMBLY statement, Query Analyzer or SQL Server Management Studio? You should be using Management Studio.
3. What database do you try to do this in - is it a new database or?

Niels

|||

Respected Sir,

First of all thanks a lot for replying my mail

Sir, Recently i checked a blog and i going to knew tht there is a compitibility problem in my Sql Server tht u have mension

i have a database name Job which i have made in SQL Server 2005 now while chacking the compitibility using following syntax:

sp_dbcmptlevel Job

it gives me

The current compatibility level is 80.

means tht its a comitibility of SQL Server 2000 and also when i checked the property of my Database then in Option tag i found compatibility Lavel to Microsoft SQL Server 2000(80) and i m not getting here level SQL Server 2005(90)

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

and sir another thing is tht when i m executing the statement

sp_dbcmptlevel Job, 90

then it gives me following error:

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92

Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

thnks a lot and plz plz send me this solution.......

once again thnks

|||

Pratik Kansara wrote:

and after executing the syntax

SELECT @.@.version

i got the message

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and sir I m using Microsoft SQL Server Managment Studio tool for CREATE ASSAMBLY statement

so sir the problem as i think we got is compitibility lavel so sir i have created this database in SQL Server 2005 also

then why i m not getting here compatibility lavel 90 or SQL Server 2005

The problem is NOT compatibility levels, if you from SELECT @.@.version are getting as per above SQL Server 2000 - 8.xx then you are NOT working against SQL 2005, you are working against SQL 2000.

Make sure you are connecting to the SQL 2005 instance from Management Studio, as Management Studio allows you to connect both to SQL 2000 as well as SQL 2005.

Niels
|||

Respected Sir

yes i m connected to SQL Server 2000 from the SQL Server 2005 Managment Studio but sir i have cheked the configration tools and other option but i cant find out how to connect to 2005

So will you plz help me how to connect to SQL Server 2005 using Managment Studio ?

thnnking you so much

|||

Respected Sir,

I have successfully solved this problem, and created a assambly in SQL Server thnkin you so much for helping me...

|||Glad you got it to work!

Niels

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.
> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files.
>> This was basically because we backed the files up to a UNIX system that
>> had
>> a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape
>> backup system and it would seem preferable to append all the 2G .ndf
>> files
>> into one 10G .mdf file on the new system.
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want
>> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf
>> files
>> on the new Sql Server 2005 server.
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
>> Is there some tool or method I can use that will allow me to create a
>> single
>> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
>> appended .ndf Sql Server 2000 data files '
> Once the files are empty you can use ALTER DATABASE to remove the file.
>> Thanks in advance.
>> Barry
>> in Oregon
> HTH
> John|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files. This was basically because we backed the files up to a UNIX system
>> that had a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape backup system and it would seem preferable to append all the 2G .ndf
>> files into one 10G .mdf file on the new system.
> There isn't any pressing need to do that.
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want to move and recreate the original 2G .mdf and the 6 additional 2G
>> .ndf files on the new Sql Server 2005 server.
>> Is there some tool or method I can use that will allow me to create a
>> single .mdf on the Sql Server 2005 side that consists of the original
>> .mdf and appended .ndf Sql Server 2000 data files '
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
..mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files ?
Thanks in advance.
Barry
in Oregon
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files ?
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David
|||Hi Barry
"frostbb" wrote:

> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that had
> a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
There may still be advantages to having two files especially if they are on
different sets of discs. If they are the same filegroup the allocation of
space to each file will be balanced. If the files are different filegroups
then you can partition your tables/indexes to separate I/O
> Unfortunately all of the Restore, Move, etc options that I've explored want
> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
> on the new Sql Server 2005 server.
It is probably easier to do this re-configuration on the new server. If they
are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
option. if they are different filegroups in each file, then changing the
clustered index so that it is on the primary filegroup will move it to the
..mdf file. If you have text columns defined to be on the second file group
then you might be able to get away with altering the column to varchar(MAX),
failing that you would need to create a new table (with text in the other
filegroup), move the data, remove the original table and then rename the new
table.

> Is there some tool or method I can use that will allow me to create a single
> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
> appended .ndf Sql Server 2000 data files ?
>
Once the files are empty you can use ALTER DATABASE to remove the file.

> Thanks in advance.
> Barry
> in Oregon
>
HTH
John
|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
> Once the files are empty you can use ALTER DATABASE to remove the file.
> HTH
> John
|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>