Backup data from MSSQL

Is there any way to backup(like method or command to run) data that were
entered into the database? Like saving all the data into another file(.eg
..txt), so that i'm able to clear certain tables in the database in a weekly
or monthly basis. As my system handles lots of data a day, so i need to
backup and clear the database to ensure that the system had enough space and
able to run fast enough. I know that there are few types of backup available
in the books i read up like 'Database backup', 'Differential database
backup', 'File and/or File group backup' and 'Transaction log backup', but i
cannot find any methods on how to use them. And i also didn't find the full
explaination for these backup. Which of these are better? Or are there any
better backup available?
Thanx..
Hi,
There are 2 options:-
1. Use BACKUP DATABASE command to backup your entire database This includes
all tables, procedures, views, indexes ,etc....(all objects
2. Use DTS or BCP OUT commands to export the contents of a table into a TXT
file. Later if need you can use DTS or BCP IN to import
the data back
BACKUP DATABASE
Backups the entire database into a .BAK file. After that you can delete the
huge transaction table with old data. If you require the data later you
could
use RESTORE DATABASe command to restore the database.
Execute the below command from Query analyzer to backup:-
BACKUP DATABASE <dbname> to DISK='D:\backup\dbname.BAK' with INIT -- With
INIT will overwrite the old backup file every time
COmmand to RESTORE
RESTORE DATABASE <dbname> from DISK='d:\backup\dbname.bak' -- More more
options see SQL Server books online
Thanks
Hari
SQL Server MVP
"yingying" <yingying@.discussions.microsoft.com> wrote in message
news:18D9692D-9F32-4187-AF78-63AD5D963EBD@.microsoft.com...
> Is there any way to backup(like method or command to run) data that were
> entered into the database? Like saving all the data into another file(.eg
> .txt), so that i'm able to clear certain tables in the database in a
> weekly
> or monthly basis. As my system handles lots of data a day, so i need to
> backup and clear the database to ensure that the system had enough space
> and
> able to run fast enough. I know that there are few types of backup
> available
> in the books i read up like 'Database backup', 'Differential database
> backup', 'File and/or File group backup' and 'Transaction log backup', but
> i
> cannot find any methods on how to use them. And i also didn't find the
> full
> explaination for these backup. Which of these are better? Or are there any
> better backup available?
> Thanx..

Backup data from MSSQL

Is there any way to backup(like method or command to run) data that were
entered into the database' Like saving all the data into another file(.eg
.txt), so that i'm able to clear certain tables in the database in a weekly
or monthly basis. As my system handles lots of data a day, so i need to
backup and clear the database to ensure that the system had enough space and
able to run fast enough. I know that there are few types of backup available
in the books i read up like 'Database backup', 'Differential database
backup', 'File and/or File group backup' and 'Transaction log backup', but i
cannot find any methods on how to use them. And i also didn't find the full
explaination for these backup. Which of these are better? Or are there any
better backup available?
Thanx..Hi,
There are 2 options:-
1. Use BACKUP DATABASE command to backup your entire database This includes
all tables, procedures, views, indexes ,etc....(all objects
2. Use DTS or BCP OUT commands to export the contents of a table into a TXT
file. Later if need you can use DTS or BCP IN to import
the data back
BACKUP DATABASE
--
Backups the entire database into a .BAK file. After that you can delete the
huge transaction table with old data. If you require the data later you
could
use RESTORE DATABASe command to restore the database.
Execute the below command from Query analyzer to backup:-
BACKUP DATABASE <dbname> to DISK='D:\backup\dbname.BAK' with INIT -- With
INIT will overwrite the old backup file every time
COmmand to RESTORE
RESTORE DATABASE <dbname> from DISK='d:\backup\dbname.bak' -- More more
options see SQL Server books online
Thanks
Hari
SQL Server MVP
"yingying" <yingying@.discussions.microsoft.com> wrote in message
news:18D9692D-9F32-4187-AF78-63AD5D963EBD@.microsoft.com...
> Is there any way to backup(like method or command to run) data that were
> entered into the database' Like saving all the data into another file(.eg
> .txt), so that i'm able to clear certain tables in the database in a
> weekly
> or monthly basis. As my system handles lots of data a day, so i need to
> backup and clear the database to ensure that the system had enough space
> and
> able to run fast enough. I know that there are few types of backup
> available
> in the books i read up like 'Database backup', 'Differential database
> backup', 'File and/or File group backup' and 'Transaction log backup', but
> i
> cannot find any methods on how to use them. And i also didn't find the
> full
> explaination for these backup. Which of these are better? Or are there any
> better backup available?
> Thanx..

Backup data from MSSQL

Is there any way to backup(like method or command to run) data that were
entered into the database' Like saving all the data into another file(.eg
.txt), so that i'm able to clear certain tables in the database in a weekly
or monthly basis. As my system handles lots of data a day, so i need to
backup and clear the database to ensure that the system had enough space and
able to run fast enough. I know that there are few types of backup available
in the books i read up like 'Database backup', 'Differential database
backup', 'File and/or File group backup' and 'Transaction log backup', but i
cannot find any methods on how to use them. And i also didn't find the full
explaination for these backup. Which of these are better? Or are there any
better backup available?
Thanx..Hi,
There are 2 options:-
1. Use BACKUP DATABASE command to backup your entire database This includes
all tables, procedures, views, indexes ,etc....(all objects
2. Use DTS or BCP OUT commands to export the contents of a table into a TXT
file. Later if need you can use DTS or BCP IN to import
the data back
BACKUP DATABASE
--
Backups the entire database into a .BAK file. After that you can delete the
huge transaction table with old data. If you require the data later you
could
use RESTORE DATABASe command to restore the database.
Execute the below command from Query analyzer to backup:-
BACKUP DATABASE <dbname> to DISK='D:\backup\dbname.BAK' with INIT -- With
INIT will overwrite the old backup file every time
COmmand to RESTORE
RESTORE DATABASE <dbname> from DISK='d:\backup\dbname.bak' -- More more
options see SQL Server books online
Thanks
Hari
SQL Server MVP
"yingying" <yingying@.discussions.microsoft.com> wrote in message
news:18D9692D-9F32-4187-AF78-63AD5D963EBD@.microsoft.com...
> Is there any way to backup(like method or command to run) data that were
> entered into the database' Like saving all the data into another file(.eg
> .txt), so that i'm able to clear certain tables in the database in a
> weekly
> or monthly basis. As my system handles lots of data a day, so i need to
> backup and clear the database to ensure that the system had enough space
> and
> able to run fast enough. I know that there are few types of backup
> available
> in the books i read up like 'Database backup', 'Differential database
> backup', 'File and/or File group backup' and 'Transaction log backup', but
> i
> cannot find any methods on how to use them. And i also didn't find the
> full
> explaination for these backup. Which of these are better? Or are there any
> better backup available?
> Thanx..

Backup data from database.

Hi all experts,
I would like to know if there are any method to generate an SQL
insert statements which contain all the data inside one of my database?
I do not want to backup the mdf files. Thanks alot.
Thanks
Rena.You can use bcp.exe or DTS to export just the data. If you really want
Insert statements, Narayana created a procedure -
http://vyaskn.tripod.com/code/generate_inserts.txt.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rena" <rena@.hotmail.com> wrote in message
news:%23yvXBvHFEHA.3132@.TK2MSFTNGP12.phx.gbl...
> Hi all experts,
> I would like to know if there are any method to generate an SQL
> insert statements which contain all the data inside one of my database?
> I do not want to backup the mdf files. Thanks alot.
> Thanks
> Rena.
>|||... and a few other options:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage
news:eHYiUBKFEHA.2416@.TK2MSFTNGP12.phx.gbl...
> You can use bcp.exe or DTS to export just the data. If you really want
> Insert statements, Narayana created a procedure -
> http://vyaskn.tripod.com/code/generate_inserts.txt.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "rena" <rena@.hotmail.com> wrote in message
> news:%23yvXBvHFEHA.3132@.TK2MSFTNGP12.phx.gbl...
>

Backup and Restore

I need to backup 10 GB of data on the west coast and send over a dedicated T1
to restore on the east coast nightly. What is the best method?
Thanks.
LaEsmeraldaThis is a multi-part message in MIME format.
--040607020506090605030503
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I would think (assuming your bandwidth is about 1.5Mbps):
1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
password protection (so the data doesn't get stolen in transmission)
2) compress backup file
3) send compressed file to East Coast (FTP? NTFS file copy? what kind
of tunnel do you have?)
4) uncompress file (on local disk on East Coast)
5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
backup password
Also, do you need all 10G to go every night? How much of the data is
_changed_ on a daily basis? What about a full backup (10GB presumably)
weekly & logs nightly? Assuming the modification activity against the
DB is not excessive, the logs should be considerably smaller than the
full DB backup. You might even consider a full DB less regularly (once
a month maybe?) with differentials weekly & logs nightly. Just use a
little imagination.
--
*mike hodgson*
http://sqlnerd.blogspot.com
PS. You're not a Victor Hugo fan are you?
LaEsmeralda wrote:
>I need to backup 10 GB of data on the west coast and send over a dedicated T1
>to restore on the east coast nightly. What is the best method?
>Thanks.
>LaEsmeralda
>
--040607020506090605030503
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I would think (assuming your bandwidth is about 1.5Mbps):<br>
 1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
password protection (so the data doesn't get stolen in transmission)<br>
 2) compress backup file<br>
 3) send compressed file to East Coast (FTP? NTFS file copy? what kind
of tunnel do you have?)<br>
 4) uncompress file (on local disk on East Coast)<br>
 5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
backup password<br>
<br>
Also, do you need all 10G to go every night? How much of the data is <u>changed</u>
on a daily basis? What about a full backup (10GB presumably) weekly
& logs nightly? Assuming the modification activity against the DB
is not excessive, the logs should be considerably smaller than the full
DB backup. You might even consider a full DB less regularly (once a
month maybe?) with differentials weekly & logs nightly. Just use a
little imagination.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<tt>PS. You're not a Victor Hugo fan are you?<br>
<br>
</tt><br>
LaEsmeralda wrote:
<blockquote cite="midE60C6CA9-B618-4B8F-A8D7-AFCA11D77946@.microsoft.com"
type="cite">
<pre wrap="">I need to backup 10 GB of data on the west coast and send over a dedicated T1
to restore on the east coast nightly. What is the best method?
Thanks.
LaEsmeralda
</pre>
</blockquote>
</body>
</html>
--040607020506090605030503--|||Your are absolutley right Mike,
Do full backup once as Mike suggested till step 5.
After that do a log dump on hourly basis or so and ship that to east coast
load it there. The log size wont be that huge. The DB i monitor have 60GB of
data. We do the above method and its running smoothly for the past 3 years.
Thanks,
Sree
"Mike Hodgson" wrote:
> I would think (assuming your bandwidth is about 1.5Mbps):
> 1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
> password protection (so the data doesn't get stolen in transmission)
> 2) compress backup file
> 3) send compressed file to East Coast (FTP? NTFS file copy? what kind
> of tunnel do you have?)
> 4) uncompress file (on local disk on East Coast)
> 5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
> backup password
> Also, do you need all 10G to go every night? How much of the data is
> _changed_ on a daily basis? What about a full backup (10GB presumably)
> weekly & logs nightly? Assuming the modification activity against the
> DB is not excessive, the logs should be considerably smaller than the
> full DB backup. You might even consider a full DB less regularly (once
> a month maybe?) with differentials weekly & logs nightly. Just use a
> little imagination.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
> PS. You're not a Victor Hugo fan are you?
>
> LaEsmeralda wrote:
> >I need to backup 10 GB of data on the west coast and send over a dedicated T1
> >to restore on the east coast nightly. What is the best method?
> >Thanks.
> >LaEsmeralda
> >
> >
>

Backup and restore

What's the best method to back up windows 2003 with 50 GB data on to an
external USB hard drive. The external hard drive size is 250 GB. Can we do
FULL back up and differential back up on to the same drive? The server with
RAID configuration has SQL server 2000 on it for now. I will be loading
exchange server on to it in few days. The client is ok with loosing weeks
worth of data (ofcourse RAID is there) .
GHOST ... etc., what type of back up and restore is best in my situation.
The second question is, on the above server the active directory is not set
up. This machine is part of a domain server running on Linux. All users
are authenicated on linux server. Can I install Windows Exchange server
2003 on to it? Any complications or pre reqs to do so.
Thanks in Advance
BVRHi
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uZ5us4FWDHA.1640@.TK2MSFTNGP10.phx.gbl...
> What's the best method to back up windows 2003 with 50 GB data on to an
> external USB hard drive. The external hard drive size is 250 GB. Can we
do
> FULL back up and differential back up on to the same drive? The server
with
Yes you can , I suggest you get some good backup software like arcserver.
While the Windows backup will work better recovery software is needed.
Yes you can do complete and differential backups on the same drive
> RAID configuration has SQL server 2000 on it for now.
You should backup all databases before the windows backup runs, transaction
logs should be backed up every hour
>I will be loading
> exchange server on to it in few days. The client is ok with loosing weeks
> worth of data (ofcourse RAID is there) .
Can I have this client they seem very easy to please. I hope the client is
OK in losing all perfromance out of the SQL box! SQL and exchanged are both
hogs and shouldn't rn on the same machine. Next you are gong to tell me you
run File/Print sharing and IIS as well!!
> GHOST ... etc., what type of back up and restore is best in my
situation.
Archserver has a very good restore option, from CD or disk
> The second question is, on the above server the active directory is not
set
> up. This machine is part of a domain server running on Linux. All users
> are authenicated on linux server. Can I install Windows Exchange server
> 2003 on to it? Any complications or pre reqs to do so.
I wouldn't have thought SQL Server runs of Linux are you using an emulation
package, if so you would already have perfromance problems :)
> Thanks in Advance
> BVR
Suggestions, Get a big tape drive, while slower than DISK more flexible and
cheaper (TAPES vs DISK) you also get more backup/recover options and at the
end of the day what's the use of backing up if you can't restore.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools