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..

Backing up Transaction log

Is there a way to backup a Transaction log without actually saving with the
backup utility. All I really want to do is dump the log. Thanks.Tom,
Try NO_LOG or TRUNCATE_ONLY. For more information see the Truncating the
Transaction Log topic in the SQL Server BOL.
HTH
Jerry
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>|||Tom Reis wrote:
> Is there a way to backup a Transaction log without actually saving
> with the backup utility. All I really want to do is dump the log.
> Thanks.
Use the with truncate_only option. The log is truncated and no backup
actually occurs. You'll need to perform a full database backup to get
back on track.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If that is the case why are you in FULL recovery mode to begin with? As
soon as you manually truncate the log you loose the ability to use the log
for recovery purposes. So if you are not planning on issuing regular log
backups you should set the recovery mode to Simple and it will automatically
truncate the log when it gets to 70% full.
--
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>

Backing up Transaction log

Is there a way to backup a Transaction log without actually saving with the
backup utility. All I really want to do is dump the log. Thanks.
Tom,
Try NO_LOG or TRUNCATE_ONLY. For more information see the Truncating the
Transaction Log topic in the SQL Server BOL.
HTH
Jerry
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>
|||Tom Reis wrote:
> Is there a way to backup a Transaction log without actually saving
> with the backup utility. All I really want to do is dump the log.
> Thanks.
Use the with truncate_only option. The log is truncated and no backup
actually occurs. You'll need to perform a full database backup to get
back on track.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If that is the case why are you in FULL recovery mode to begin with? As
soon as you manually truncate the log you loose the ability to use the log
for recovery purposes. So if you are not planning on issuing regular log
backups you should set the recovery mode to Simple and it will automatically
truncate the log when it gets to 70% full.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>

Backing up Transaction log

Is there a way to backup a Transaction log without actually saving with the
backup utility. All I really want to do is dump the log. Thanks.Tom,
Try NO_LOG or TRUNCATE_ONLY. For more information see the Truncating the
Transaction Log topic in the SQL Server BOL.
HTH
Jerry
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>|||Tom Reis wrote:
> Is there a way to backup a Transaction log without actually saving
> with the backup utility. All I really want to do is dump the log.
> Thanks.
Use the with truncate_only option. The log is truncated and no backup
actually occurs. You'll need to perform a full database backup to get
back on track.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If that is the case why are you in FULL recovery mode to begin with? As
soon as you manually truncate the log you loose the ability to use the log
for recovery purposes. So if you are not planning on issuing regular log
backups you should set the recovery mode to Simple and it will automatically
truncate the log when it gets to 70% full.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:ud6$kBtuFHA.3124@.TK2MSFTNGP10.phx.gbl...
> Is there a way to backup a Transaction log without actually saving with
> the
> backup utility. All I really want to do is dump the log. Thanks.
>