basic question for sp

Hi,
I need to write a sp. In the sp first I do a select statement (select cycle
from table where ... ) it only returns a single value. and the rest of the s
p
will do different select based the value. how can assign the result to a
varable? ThanksJen,
Try:
DECLARE @.MYVAR INT --OR CHAR OR VARCHAR OR...DEPENDING ON VALUE
SELECT @.MYVAR = COL FROM...
HTH
Jerry
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:CB644854-6C94-48BC-8E45-9DC9A959F0C5@.microsoft.com...
> Hi,
> I need to write a sp. In the sp first I do a select statement (select
> cycle
> from table where ... ) it only returns a single value. and the rest of the
> sp
> will do different select based the value. how can assign the result to a
> varable? Thanks|||Something along the lines of:
SET @.var = ( SELECT ... ) ;
You will have to make sure the select statement returns a single value or
you will get an error.
Anith|||DECLARE @.Variable <type>
SELECT @.Variable = cycle FROM table WHERE ...
John Scragg
"Jen" wrote:

> Hi,
> I need to write a sp. In the sp first I do a select statement (select cycl
e
> from table where ... ) it only returns a single value. and the rest of the
sp
> will do different select based the value. how can assign the result to a
> varable? Thankssql

Balancing data between files

We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.

First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.

Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
..
..
..
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!
I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>
|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

I have created a backup schedule for all databases on a
SQL server. I have Created a Backup device for each db.
and a single stored procedure that is called with database
name, device & retain days passed as parameters. All
Devices are network locations. I didn't want these backups
to swallow the entire disk space so i set the retain days
to 14. After 2 weeks i hoped that each backup would have
been overwriten. Well that was my logic. 2 weeks are up
and the files created are still growing. After some
investigation it is apparent that the expiry dates have
been passed but the backups sets have not been
overwritten. So much for my plan. I have now re-read BOL
and realised that i have got my wires crossed, the entire
media is overwriten when the expiry dates of all backups
within have been reached. To me this is topsy turvy, why
would i wish to overwrite an entire backup file, maybe if
i had taken a back up of the backup then i would wish to
delete it. Have i yet again misunderstood BOL. What i want
to do is maintain a dynamic history of backups. I back up
my database to a device, this backup lasts for 2 weeks and
then is overwriten. so the actual physical file is never
deleted, only the contents within when the expiry date is
reached.
Help!!!!!!!Expiredays and retaindays are only there to not allow you to overwrite using
the INIT before a certain day. If you aren't using INIT or if you are using
NOINIT, it will always be append. And, it is all or nothing.
If you want generation handling, either use the Maint Wizard, a 3:rd party
like www.dbmaint.com or some TSQL programming to handle this (using more
than one backup device).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
> I have created a backup schedule for all databases on a
> SQL server. I have Created a Backup device for each db.
> and a single stored procedure that is called with database
> name, device & retain days passed as parameters. All
> Devices are network locations. I didn't want these backups
> to swallow the entire disk space so i set the retain days
> to 14. After 2 weeks i hoped that each backup would have
> been overwriten. Well that was my logic. 2 weeks are up
> and the files created are still growing. After some
> investigation it is apparent that the expiry dates have
> been passed but the backups sets have not been
> overwritten. So much for my plan. I have now re-read BOL
> and realised that i have got my wires crossed, the entire
> media is overwriten when the expiry dates of all backups
> within have been reached. To me this is topsy turvy, why
> would i wish to overwrite an entire backup file, maybe if
> i had taken a back up of the backup then i would wish to
> delete it. Have i yet again misunderstood BOL. What i want
> to do is maintain a dynamic history of backups. I back up
> my database to a device, this backup lasts for 2 weeks and
> then is overwriten. so the actual physical file is never
> deleted, only the contents within when the expiry date is
> reached.
> Help!!!!!!!
>|||Thanks Tibor
Are there any sys SP's or XP's that can be used to edit
backup files? Maybe i could remove expired files..
I have created a stored procedure that looks at a backup
device and tells me which full, DIff and TL backups need
to be applied to restore to a specified point in time. It
looks as if this will nor work if i have to create new
devices..rats...
I pull my hair out some times with the illogical-ness of
SQL server

>--Original Message--
>Expiredays and retaindays are only there to not allow you
to overwrite using
>the INIT before a certain day. If you aren't using INIT
or if you are using
>NOINIT, it will always be append. And, it is all or
nothing.
>If you want generation handling, either use the Maint
Wizard, a 3:rd party
>like www.dbmaint.com or some TSQL programming to handle
this (using more
>than one backup device).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a5ce01c40a8e$5c4c13e0$a601280a@.phx.gbl...
database
backups
days
entire
why
if
want
up
and
is
>
>.
>|||There are no tool with which you can remove selective backups inside a
backup file, I'm afraid.
One alternative is to do append, say over one day (assume db backup once per
day and log backup once per hour). Then after one day, you rename the Active
to give it a timestamp (so you have generations) and then do INIT to the
active one. This is how we did it in Db Maint up until the current version,
where we decided to not do append anymore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
> Thanks Tibor
> Are there any sys SP's or XP's that can be used to edit
> backup files? Maybe i could remove expired files..
> I have created a stored procedure that looks at a backup
> device and tells me which full, DIff and TL backups need
> to be applied to restore to a specified point in time. It
> looks as if this will nor work if i have to create new
> devices..rats...
> I pull my hair out some times with the illogical-ness of
> SQL server
>
> to overwrite using
> or if you are using
> nothing.
> Wizard, a 3:rd party
> this (using more
> message
> database
> backups
> days
> entire
> why
> if
> want
> up
> and
> is|||Thanks tibor, thats great. Part of my backup script now
contains code to create a backup device every time it is
run that is named depending on a variable passed.
Alternating every week the physical file names change and
the retain days are set to 7. So as you advised i create a
file and add my backups. After a week i swich to a second
file and use this for a week. After another 7 days i
switch back to the original file that is now ready tbe
overwriten.
My SP that advises me of what backup files to aply now
works in pretty much the same way. It creates a device
based on the parameters and returns the backup history. it
then re-creates the device with the second file name and
apends this to the first run.
Thanks so much, you have been a great help..

>--Original Message--
>There are no tool with which you can remove selective
backups inside a
>backup file, I'm afraid.
>One alternative is to do append, say over one day (assume
db backup once per
>day and log backup once per hour). Then after one day,
you rename the Active
>to give it a timestamp (so you have generations) and then
do INIT to the
>active one. This is how we did it in Db Maint up until
the current version,
>where we decided to not do append anymore.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d7ca01c40a92$7eb09060$a101280a@.phx.gbl...
It
you
on a
db.
have
are up
have
BOL
backups
maybe
wish to
back
weeks
never
date
>
>.
>|||I'm glad I could help, mat. Just one thing: As far as I can see, retains
days doesn't really buy you anything. I only mention this so you don't read
anything into this parameter which isn't there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:d55d01c40aad$fb535920$a501280a@.phx.gbl...
> Thanks tibor, thats great. Part of my backup script now
> contains code to create a backup device every time it is
> run that is named depending on a variable passed.
> Alternating every week the physical file names change and
> the retain days are set to 7. So as you advised i create a
> file and add my backups. After a week i swich to a second
> file and use this for a week. After another 7 days i
> switch back to the original file that is now ready tbe
> overwriten.
> My SP that advises me of what backup files to aply now
> works in pretty much the same way. It creates a device
> based on the parameters and returns the backup history. it
> then re-creates the device with the second file name and
> apends this to the first run.
> Thanks so much, you have been a great help..
>
> backups inside a
> db backup once per
> you rename the Active
> do INIT to the
> the current version,
> message
> It
> you
> on a
> db.
> have
> are up
> have
> BOL
> backups
> maybe
> wish to
> back
> weeks
> never
> date

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup
vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then ap
ply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain o
f log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...seagreen">
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a singl
e
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be store
d
> in a seperate file. For example today for our server we would have 26 fil
es
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one fil
e
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

Hello,
Our backups are failing as we have connections to them,
and the error is that the database must be in single mode.
Our recovery plan is set to full, so I was wondering what
the best practise is i.e could we set the database to
single mode killing off all connections, use some SQL
commands to kill off connection, or change the recovery
model to something else.
Any help would be appreciated.
Thanks
PeterHello, Peter!
Is this through a maintenance plan ?
SQL Server backups can be taken with users still on the system. A Database
cannot be restored with users in the database. Is this error being
generated by SQL Server trying to put the DB into Single User mode to fix
minor errors ?
You can quite easily test the BACKUP ni isolation by going to QA and issuing
the statement there.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Our backups are failing as we have connections to them,
: and the error is that the database must be in single mode.
: Our recovery plan is set to full, so I was wondering what
: the best practise is i.e could we set the database to
: single mode killing off all connections, use some SQL
: commands to kill off connection, or change the recovery
: model to something else.
: Any help would be appreciated.
-- Microsoft CDO for Windows 2000|||Thank you, I will change my options.
Peter
>--Original Message--
>Yes, I agree with Allan in that this is due to the fact
you have the option
>checked to do an integrity check before the backups and
that is what needs
>to be in single user mode. This is a bad option anyway
because people
>usually have the integrity jobs running from the other
page of the MP
>dialog. So now it does DBCC's 2 or 3 times.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in
message
>news:enfKY0UQDHA.3016@.TK2MSFTNGP10.phx.gbl...
>> Hello, Peter!
>> Is this through a maintenance plan ?
>> SQL Server backups can be taken with users still on the
system. A
>Database
>> cannot be restored with users in the database. Is this
error being
>> generated by SQL Server trying to put the DB into
Single User mode to fix
>> minor errors ?
>>
>> You can quite easily test the BACKUP ni isolation by
going to QA and
>issuing
>> the statement there.
>>
>>
>> --
>> Allan Mitchell (Microsoft SQL Server MVP)
>> MCSE,MCDBA
>> www.SQLDTS.com
>> I support PASS - the definitive, global community
>> for SQL Server professionals - http://www.sqlpass.org
>> : Our backups are failing as we have connections to
them,
>> : and the error is that the database must be in single
mode.
>> : Our recovery plan is set to full, so I was wondering
what
>> : the best practise is i.e could we set the database to
>> : single mode killing off all connections, use some SQL
>> : commands to kill off connection, or change the
recovery
>> : model to something else.
>> : Any help would be appreciated.
>> -- Microsoft CDO for Windows 2000
>>
>
>.
>

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?Unfortunatly there is no automatic way of doing this.
Instead I would use the export / import data from
Enterprice Manager.
Sumply right click your database, select all taskes and
there is an option for Export / Import data, from here its
fairly self explanitory.
Peter
"The best argument against democracy is a five-minute
conversation with the average voter."
Winston Churchill
>--Original Message--
>How can I perform a backup of a single table so that I
>can then restore the table to another server with a new
>table name?
>.
>|||Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:
> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
> > How can I perform a backup of a single table so that I
> > can then restore the table to another server with a new
> > table name?
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
>> Just be aware that the backup cannot be used alone, it has to be restored
> into the database from
>> where the backup was performed.
>> Also, the backup cannot be used to "go back in time" for that part of the
> database. All transaction
>> log backups taken since need to be applied until the database is usable.
>> Typically, when I see a request for table backup, filegroup backup is
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?
Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:

> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>
|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...[vbcol=seagreen]
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> into the database from
> database. All transaction
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>