Backup SQL Tables

Good morning :)

i have a remote SQL Server tables and a remote Access.mdb file with the same tables in it.
i need to find a way to transfer the data from the SQL tables to the Access tables, with SQL commands, without being dependent in the SQL enterprise manager.

does anybody have a good idea?

thanks a lot,Why do you want to transfer the data from MS SQL to Access ?

Backup sizes

We use LiteSpeed backups and was wondering if there is a way in one of those
msdb backup tables that shows me the size of the compressed backups for each
of my dbs?
If so, whats the query?
All i want to know is name of db and its backup size.Take a look at this script:
http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
Thank you,
Saleem Hakani (World Wide SQL Server Community)
HTTP://WWW.SQLCOMMUNITY.COM
SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of SQL
Fun.
"Hassan" wrote:
> We use LiteSpeed backups and was wondering if there is a way in one of those
> msdb backup tables that shows me the size of the compressed backups for each
> of my dbs?
> If so, whats the query?
> All i want to know is name of db and its backup size.
>
>|||Saleem this script shows me the actual database size , but I am using
LiteSpeed backup and when i compare the output of your results to the size
on disk, they are different. Your output gives me the size assuming I do a
native backup and not SQL.
How can i get the sizes of the backups that I am using and in this case
LiteSpeed ? are there special LiteSpeed tables ?
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:3AE9390E-886F-4CAB-8E3B-F06E0BDB0801@.microsoft.com...
> Take a look at this script:
> http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
> Thank you,
> Saleem Hakani (World Wide SQL Server Community)
> HTTP://WWW.SQLCOMMUNITY.COM
> SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of
> SQL
> Fun.
>
> "Hassan" wrote:
>> We use LiteSpeed backups and was wondering if there is a way in one of
>> those
>> msdb backup tables that shows me the size of the compressed backups for
>> each
>> of my dbs?
>> If so, whats the query?
>> All i want to know is name of db and its backup size.
>>sql

Backup single table

I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
FarrukhIf you will regularly be backing up certain tables, then consider placing
them into their own filegroup and then backing up that filegroup. As for
stored procs, you can use Enterprise Manager to script them. As a best
practice, you should have your scripts in version control outside of your
SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
Farrukh|||I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:
> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>|||There is no script here. Just right-click on the DB, click on All
Tasks->generate SQL script.
As for the filegoups, you are looking at a fair bit of work, since you now
need to migrate your existing tables to this filegroup, and that's a rebuild
of those tables.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:
> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>|||> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
... also, it isn't obvious how to get a backup of a filegroup available, unless you also have backup
of the rest of the database (which makes the filegroup backup kind of ... moot). In short, filegroup
backup should only be done with a very good understanding about backup in SQL Server and what it
does and does not give you. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
> There is no script here. Just right-click on the DB, click on All
> Tasks->generate SQL script.
> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
> I dont get this request daily. Actually this is the first time i get this
> request. I dont have any script. would you like to share ur script? thanks
> "Tom Moreau" wrote:
>> If you will regularly be backing up certain tables, then consider placing
>> them into their own filegroup and then backing up that filegroup. As for
>> stored procs, you can use Enterprise Manager to script them. As a best
>> practice, you should have your scripts in version control outside of your
>> SQL Server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
>> I would like to take the backup of only 15 tables out of 800 tables and 8
>> stored procedures out of 90. The database is very huge and might take few
>> hours to take full database backup
>> is there anyway I can take backup only selected tables and stored
>> procedures? I have SQL Server 2000 running.
>> thanks
>> Farrukh
>>
>|||In a case like this, BCP MIGHT be a workable solution.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D39370AC-E225-4DAB-97C7-753FA5D4B378@.microsoft.com...
>> As for the filegoups, you are looking at a fair bit of work, since you
>> now
>> need to migrate your existing tables to this filegroup, and that's a
>> rebuild
>> of those tables.
> ... also, it isn't obvious how to get a backup of a filegroup available,
> unless you also have backup of the rest of the database (which makes the
> filegroup backup kind of ... moot). In short, filegroup backup should only
> be done with a very good understanding about backup in SQL Server and what
> it does and does not give you. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
>> There is no script here. Just right-click on the DB, click on All
>> Tasks->generate SQL script.
>> As for the filegoups, you are looking at a fair bit of work, since you
>> now
>> need to migrate your existing tables to this filegroup, and that's a
>> rebuild
>> of those tables.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
>> I dont get this request daily. Actually this is the first time i get this
>> request. I dont have any script. would you like to share ur script?
>> thanks
>> "Tom Moreau" wrote:
>> If you will regularly be backing up certain tables, then consider
>> placing
>> them into their own filegroup and then backing up that filegroup. As
>> for
>> stored procs, you can use Enterprise Manager to script them. As a best
>> practice, you should have your scripts in version control outside of
>> your
>> SQL Server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
>> I would like to take the backup of only 15 tables out of 800 tables and
>> 8
>> stored procedures out of 90. The database is very huge and might take
>> few
>> hours to take full database backup
>> is there anyway I can take backup only selected tables and stored
>> procedures? I have SQL Server 2000 running.
>> thanks
>> Farrukh
>>
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup single table

I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
Farrukh
If you will regularly be backing up certain tables, then consider placing
them into their own filegroup and then backing up that filegroup. As for
stored procs, you can use Enterprise Manager to script them. As a best
practice, you should have your scripts in version control outside of your
SQL Server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
Farrukh
|||I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:

> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>
|||There is no script here. Just right-click on the DB, click on All
Tasks->generate SQL script.
As for the filegoups, you are looking at a fair bit of work, since you now
need to migrate your existing tables to this filegroup, and that's a rebuild
of those tables.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:

> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>
|||> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
... also, it isn't obvious how to get a backup of a filegroup available, unless you also have backup
of the rest of the database (which makes the filegroup backup kind of ... moot). In short, filegroup
backup should only be done with a very good understanding about backup in SQL Server and what it
does and does not give you. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
> There is no script here. Just right-click on the DB, click on All
> Tasks->generate SQL script.
> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
> I dont get this request daily. Actually this is the first time i get this
> request. I dont have any script. would you like to share ur script? thanks
> "Tom Moreau" wrote:
>
>
|||In a case like this, BCP MIGHT be a workable solution.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D39370AC-E225-4DAB-97C7-753FA5D4B378@.microsoft.com...
> ... also, it isn't obvious how to get a backup of a filegroup available,
> unless you also have backup of the rest of the database (which makes the
> filegroup backup kind of ... moot). In short, filegroup backup should only
> be done with a very good understanding about backup in SQL Server and what
> it does and does not give you. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
Keith
You can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:

>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is there
>a way to restore at the table level?
>
>Thanks,
>Keith
|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:

> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is there
> a way to restore at the table level?
>
> Thanks,
> Keith
>

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
KeithYou can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:

>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is ther
e
>a way to restore at the table level?
>
>Thanks,
>Keith|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:

> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is the
re
> a way to restore at the table level?
>
> Thanks,
> Keith
>sql

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
KeithYou can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:
>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is there
>a way to restore at the table level?
>
>Thanks,
>Keith|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:
> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is there
> a way to restore at the table level?
>
> Thanks,
> Keith
>

backup question

Using SS2000 SP4. Is there any way to back up to a .bak file just selected
tables from a database?
Thanks,
--
Dan D.No. If you have multiple filegroups and you segregate the tables you want
into a specific file group you can backup just that filegroup.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> tables from a database?
> Thanks,
> --
> Dan D.|||I don't have multiple filegroups. Thanks.
--
Dan D.
"Andrew J. Kelly" wrote:
> No. If you have multiple filegroups and you segregate the tables you want
> into a specific file group you can backup just that filegroup.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> > Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> > tables from a database?
> >
> > Thanks,
> > --
> > Dan D.
>|||Sorry - there is not.
One strategy that you might wish to employ is to put these selected tables
in their own filegroup. You can then do a filegroup backup of just that one
group. Another option - you could still separate out your tables into their
own filegroup and stop SQL Server, copy the files associated with that
filegroup, and restart. This is not the best way to help availability. ;-)
Another option would be to replicate those tables to another DB and backup
that DB.
It depends on which approach will work best for you.
Rick Heiges
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> tables from a database?
> Thanks,
> --
> Dan D.|||Thanks for the suggestions Rick.
--
Dan D.
"Rick Heiges" wrote:
> Sorry - there is not.
> One strategy that you might wish to employ is to put these selected tables
> in their own filegroup. You can then do a filegroup backup of just that one
> group. Another option - you could still separate out your tables into their
> own filegroup and stop SQL Server, copy the files associated with that
> filegroup, and restart. This is not the best way to help availability. ;-)
> Another option would be to replicate those tables to another DB and backup
> that DB.
> It depends on which approach will work best for you.
> Rick Heiges
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:954CD167-72FA-49EB-BCC8-6EAFFEF1EA0F@.microsoft.com...
> > Using SS2000 SP4. Is there any way to back up to a .bak file just selected
> > tables from a database?
> >
> > Thanks,
> > --
> > Dan D.
>
>

Backup only one table

Hi
Does anyone know how to backup just one table (or selected tables) in
SQL2000?
Any help will be appreciated.
Thanks
CraigYou can't do that using BACKUP/RESTORE (well, you can put them on their own filegroups, but your
restore options are severely limited). How about using BCP/DTS etc ?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Craig Bryden" <craig@.ch.co.za> wrote in message news:Ozo3oFuaDHA.3444@.tk2msftngp13.phx.gbl...
> Hi
> Does anyone know how to backup just one table (or selected tables) in
> SQL2000?
> Any help will be appreciated.
> Thanks
> Craig
>

Backup of tables

Hi Guys,

I would like to take a backup of tables and further use them for manipulation purpose.

(e.g.)

Select * into arc_employee_07_07_2005 from employee

Where 07_07_2005 is formatted from getdate().

How can i achieve this formate.

Thanks in advance.

Look at the day, month, year or datepart function. You can use those with string functions to generate the leading 0 also like:

select right('00' + cast(day(current_timestamp) as varchar)), 2)|||Hi JayaChandran

i have just modified ur query to something like this.

select * into Arc_Employee_+(select cast(day(current_timestamp) as varchar)+'_'+
cast(month(current_timestamp) as varchar)+'_'+cast(year(current_timestamp) as varchar))
from Employee

Can you modify the above query. so that the table name should be having a suffix of date. (eg) Ar_TableName_Date.

Thanks in Advance|||You cannot specify an expression as table name for the INTO clause. You have to generate the name and then use dynamic SQL to execute the SELECT INTO statement. If you do not want to use dynamic sql, another option is to do following:

select * into _temp_arc_employee from Employee
exec sp_rename '_temp_arc_employee', 'new name'

This will work only if this is the only connection performing this operation otherwise you will get error due to same name being used for the SELECT...INTO table name from different connections.

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
Angela
There is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela
|||You export them. Using DTS, BCP or some other export method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela
|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel

>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>
>.
>

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
AngelaThere is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||You export them. Using DTS, BCP or some other export method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel

>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>
>.
>

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
AngelaThere is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||You export them. Using DTS, BCP or some other export method.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel
>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>> I am a serious newbie. How do you backup only certain
>> tables in a database? Thank you.
>> Angela
>
>.
>

Backup my deleted records

Hi all,

I have a db with 15 tables and I want to keep records that have been deleted.

Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?

what are the advantages and disatvantages of the named possibilities.

If someone knows anything else please help me out.

Thnx in advanceBecause the structure of the 15 tables will be different per table, you cannot dump them into one table. What you could do is create a new table per existing table and fill the new table with a trigger. Disadvantage: takes time and your database size will grow. Advantage: you can keep your records and have a good overview. Second possibility is not to actually delete the records, but to use a column to indicate whether a record is deleted or not. Disadvantage: another column, possibly problems with unique constraints, optimizers use incorrect data. Advantage: less administration as the first solution.|||thnx for your reply,

I 'll think I'll go with the suggestion to create a table per each existing table.|||This is a common concept...not only deletes but updates...

Script the base...add 3 additional columns and add a trigger to the base...something like:

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END|||Another way of doing this is.

Add a column called Deteted to all tables (it will have value of 0 by default)
Whenever u delete a record, just ser deleted to 1.
Then after a few months, when you want to archieve the db, copy all those records with deleted=1 to your archive tables.

This is a change that has to be done at the application level.
If your application is already built, then you can handle this issue only at the database level, thus you will have to go for triggers.

Regards

Benny

Backup MS SQL Server using ASP.NET

Hi!

For the moment I’m hooked up with this hosting company that refuses to give me any backup files of my Microsoft SQL Server database/tables. I’ve even asked if the can do it just once but they still won’t. I don’t know what they are doing because they won’t even let me use Enterprise Manager so, from what I know, can’t use the scripts that I’ve found that use stored procedures.

Which is the easiest way to do a full backup just using the built in functionality of ASP.NET 1.3 and with limited permission on the SQL server? The goal is to move everything to a new company.

A friend recommended this way:

Create a class that holds both the table structure and a dataset for the data.
Each instance of these class is then stored in a collection that we serialize and store on the web server.
When this is done we reverse the process at the new hosting company.

Would this work / be the easiest way?

ThanksYou can take help of SQLDMO in this case, refer http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-Using-VBNET/ and http://codingforums.com/archive/index.php?t-25387.html for information.

HTH|||Thanks!
I've got the same advice on another forum, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66036 (remove if it's not ok to link), who also suggested using DMO.|||Hey...I think that was me. :)

How you doing Satya?|||Hi Derrick
Thanks, I'm fine.. hope you had a good holiday as I don't see on the forums since a month (I think).
Its good to catch other forums too ... different from routine :)


How you doing Satya?

backup LSN help

I've been working on some code to be able to reverse catalog a set of
backups into the msdb backup* tables, which was easy, but now I'm struggling
with the necessary code to create the restore commands for an arbitrary
point in time.
My question is what is the appropriate logic or psuedo code for walking a
tree backup resources to get to a point in time.
I believe the following to be appropriate logic, but would like
verification:
full backup id = (max (checkpointlsn) where finishtime<[point in time]) and
type = 1
differential backup id = (max(checkpointlsn) where backuplsn = full backup
checkpointlsn and type = 5 and finishtime<[point in time])
But I am still struggling with the T-log resources and how to put it all
together (like for example, if you simply cannot get to a given point in
time given a set of files, and only wanting to restore the necessary the
last t-log where the lsn hasn't changed in 10 backup files)
Help!?
Bump
<news@.news.com> wrote in message
news:%23l52asjXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I've been working on some code to be able to reverse catalog a set of
> backups into the msdb backup* tables, which was easy, but now I'm
struggling
> with the necessary code to create the restore commands for an arbitrary
> point in time.
> My question is what is the appropriate logic or psuedo code for walking a
> tree backup resources to get to a point in time.
> I believe the following to be appropriate logic, but would like
> verification:
> full backup id = (max (checkpointlsn) where finishtime<[point in time])
and
> type = 1
> differential backup id = (max(checkpointlsn) where backuplsn = full backup
> checkpointlsn and type = 5 and finishtime<[point in time])
> But I am still struggling with the T-log resources and how to put it all
> together (like for example, if you simply cannot get to a given point in
> time given a set of files, and only wanting to restore the necessary the
> last t-log where the lsn hasn't changed in 10 backup files)
> Help!?
>
>
>
>
>

backup LSN help

I've been working on some code to be able to reverse catalog a set of
backups into the msdb backup* tables, which was easy, but now I'm struggling
with the necessary code to create the restore commands for an arbitrary
point in time.
My question is what is the appropriate logic or psuedo code for walking a
tree backup resources to get to a point in time.
I believe the following to be appropriate logic, but would like
verification:
full backup id = (max (checkpointlsn) where finishtime<[point in time]) and
type = 1
differential backup id = (max(checkpointlsn) where backuplsn = full backup
checkpointlsn and type = 5 and finishtime<[point in time])
But I am still struggling with the T-log resources and how to put it all
together (like for example, if you simply cannot get to a given point in
time given a set of files, and only wanting to restore the necessary the
last t-log where the lsn hasn't changed in 10 backup files)
Help!?Bump
<news@.news.com> wrote in message
news:%23l52asjXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I've been working on some code to be able to reverse catalog a set of
> backups into the msdb backup* tables, which was easy, but now I'm
struggling
> with the necessary code to create the restore commands for an arbitrary
> point in time.
> My question is what is the appropriate logic or psuedo code for walking a
> tree backup resources to get to a point in time.
> I believe the following to be appropriate logic, but would like
> verification:
> full backup id = (max (checkpointlsn) where finishtime<[point in time])
and
> type = 1
> differential backup id = (max(checkpointlsn) where backuplsn = full backup
> checkpointlsn and type = 5 and finishtime<[point in time])
> But I am still struggling with the T-log resources and how to put it all
> together (like for example, if you simply cannot get to a given point in
> time given a set of files, and only wanting to restore the necessary the
> last t-log where the lsn hasn't changed in 10 backup files)
> Help!?
>
>
>
>
>

backup LSN help

I've been working on some code to be able to reverse catalog a set of
backups into the msdb backup* tables, which was easy, but now I'm struggling
with the necessary code to create the restore commands for an arbitrary
point in time.
My question is what is the appropriate logic or psuedo code for walking a
tree backup resources to get to a point in time.
I believe the following to be appropriate logic, but would like
verification:
full backup id = (max (checkpointlsn) where finishtime<[point in time])
and
type = 1
differential backup id = (max(checkpointlsn) where backuplsn = full backup
checkpointlsn and type = 5 and finishtime<[point in time])
But I am still struggling with the T-log resources and how to put it all
together (like for example, if you simply cannot get to a given point in
time given a set of files, and only wanting to restore the necessary the
last t-log where the lsn hasn't changed in 10 backup files)
Help!?Bump
<news@.news.com> wrote in message
news:%23l52asjXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I've been working on some code to be able to reverse catalog a set of
> backups into the msdb backup* tables, which was easy, but now I'm
struggling
> with the necessary code to create the restore commands for an arbitrary
> point in time.
> My question is what is the appropriate logic or psuedo code for walking a
> tree backup resources to get to a point in time.
> I believe the following to be appropriate logic, but would like
> verification:
> full backup id = (max (checkpointlsn) where finishtime<[point in time])[/vbcol
]
and[vbcol=seagreen]
> type = 1
> differential backup id = (max(checkpointlsn) where backuplsn = full backup
> checkpointlsn and type = 5 and finishtime<[point in time])
> But I am still struggling with the T-log resources and how to put it all
> together (like for example, if you simply cannot get to a given point in
> time given a set of files, and only wanting to restore the necessary the
> last t-log where the lsn hasn't changed in 10 backup files)
> Help!?
>
>
>
>
>