Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backu
p
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:

> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though
.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are th
en
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to kee
p
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
>|||Either use the maintenance wizard for this, or you have to do some coding to
create a new backup
files each time you do the backup. Search the archives and you will find suc
h sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?

Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:
> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
> > I am wanting to know some ways to backup my databases. I have 3 I need to
> > backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> > growing. I want to have a backup every night. Will doing just a full backup
> > give me everything I need to restore a database? How would I get it to backup
> > every day but NOT overwrite the previous file, I want to create a new file
> > every night so if for some reason I need to get information off a databse 3
> > months ago I will be able to. What qould be the best way to do this?|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?

Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?
what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:

> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?
|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ... \Backups\DatabaseName\Full\DatabaseNameX
X_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ... \Backups\DatabaseName\TRXN\DatabaseName_
Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, TomHi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_MMDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ