Backup single table

Discussion:

Backup single table

(too old to reply)

FARRUKH

2008-04-09 17:27:01 UTC

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

Tom Moreau

2008-04-09 17:39:40 UTC

Permalink

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" <***@hotmail.com> wrote in message news:82CA5B73-6DBC-433B-A8F0-***@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

FARRUKH

2008-04-09 18:30:02 UTC

Permalink

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

Post by Tom Moreau
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
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

Tom Moreau

2008-04-09 18:33:46 UTC

Permalink

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" <***@hotmail.com> wrote in message news:055BC3BC-89AE-4424-95B0-***@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

Tibor Karaszi

2008-04-09 19:52:01 UTC

Permalink

Post by Tom Moreau
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

Post by Tom Moreau
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
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

Greg D. Moore (Strider)

2008-04-12 02:09:35 UTC

Permalink

In a case like this, BCP MIGHT be a workable solution.

Post by Tibor Karaszi

Post by Tom Moreau
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

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

tarun mukherjee

2010-11-10 08:43:44 UTC

Permalink

Post by FARRUKH
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

Post by 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

Post by Tibor Karaszi
... 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

Post by Greg D. Moore (Strider)
In a case like this, BCP MIGHT be a workable solution.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Submitted via EggHeadCafe
Fill out blank PDF form online and stream to browser with iTextSharp
http://www.eggheadcafe.com/tutorials/aspnet/a5e1109d-03bc-4e6b-a904-af4835f336cd/fill-out-blank-pdf-form-online-and-stream-to-browser-with-itextsharp.aspx

h***@gmail.com

2014-12-09 19:50:59 UTC

Permalink

Table backup:

To export:

bcp "[MyDatabase].dbo.Customer" out "Customer.bcp" -N -S ServerName -T -E -U"xxxxxx" -P"xxxxxxx"

To import:

bcp "[MyDatabase].dbo.Customer" in "Customer.bcp" -N -S ServerName -T -E -b 10000 -U"xxxxxx" -P"xxxxxxx"