Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. YapAre you sure the id which was used to start the job agent had access right to the backup device?|||Yes, I usse the same id with the one that I used at a remote PC.
The id I used was 'sa' and I connect to the master database to run the 'BACKUP' statement.
Showing posts with label basic. Show all posts
Showing posts with label basic. Show all posts
Tuesday, March 27, 2012
Backup fail
Hi All,
I have a basic DB backup question:
I make DB backups every day at night using maintenance plan, where all DB
are saved. In 1st step the database backup is made, in 2nd step the
transaction log is saved. Sometimes, usually once a week (e.g. on Monday
1.00 am), one of the database backups failed with "... failed because DB log
is full" error. Now I make a manual backup, which is normally proceeded.
Then, the next backups work normally approx. 1 week. Situation repeates, but
not exactly every week.
After the log backup, I think it will truncate to small size. It is not
true, usual size of log is about 130 MB (database size is 80 MB). But after
a backup failes, the log size is about 270 MB.
In backup parameters the log truncate option is included, but in maintenance
plan this option is missing. So if I'll make backups manually, no problems
will occur. But I want work automatically.
What am I do to correct backup job?
Thank you in advance for your help
Vlastik
Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
generally do db backup once a day and log backup one per hour.
Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a basic DB backup question:
> I make DB backups every day at night using maintenance plan, where all DB
> are saved. In 1st step the database backup is made, in 2nd step the
> transaction log is saved. Sometimes, usually once a week (e.g. on Monday
> 1.00 am), one of the database backups failed with "... failed because DB log
> is full" error. Now I make a manual backup, which is normally proceeded.
> Then, the next backups work normally approx. 1 week. Situation repeates, but
> not exactly every week.
> After the log backup, I think it will truncate to small size. It is not
> true, usual size of log is about 130 MB (database size is 80 MB). But after
> a backup failes, the log size is about 270 MB.
> In backup parameters the log truncate option is included, but in maintenance
> plan this option is missing. So if I'll make backups manually, no problems
> will occur. But I want work automatically.
> What am I do to correct backup job?
> Thank you in advance for your help
> Vlastik
>
>
|||Hi, Tibor,
thank you for your response, it helps me to understand some functions of SQL 2000. Of course I've made both data and log backups daily, but something in them doesn't work.
Atfer your response, I checked the settings of my DBs and all settings are O.K. including autoshrink. I've only added a scheduled shrink daily after backup, because an automatic shrink didn't work and I don't know why. For example today, after backups and autoshrink, the database and log size were 112/149 MB. I made manual shrink and sizes changed to 84/41 MB. It's really crazy.
I checked the plan history in [msdb], but I'm not pretty enough to understand it. Database shrink was done every day. Here is a selection for database 'Kredit', which is the biggest:
database_name ;activity ;succeeded ;end_time ;error_number
Kredit ;Backup database ;False ;26.4.2004 1:00:09 ;9002
Kredit ;Backup transaction log ;True ;26.4.2004 1:31:00 ;0
Kredit ;Verify Backup ;True ;26.4.2004 1:31:22 ;0
Kredit ;Rebuild Indexes ;True ;26.4.2004 2:01:43 ;0
Kredit ;Shrink Database ;True ;26.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;27.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;27.4.2004 1:30:49 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:31:07 ;0
Kredit ;Rebuild Indexes ;True ;27.4.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;27.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;28.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;28.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;28.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;28.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;29.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;29.4.2004 1:30:43 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:30:59 ;0
Kredit ;Rebuild Indexes ;True ;29.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;29.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;30.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;30.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;30.4.2004 2:01:48 ;0
Kredit ;Shrink Database ;True ;30.4.2004 2:02:04 ;0
Kredit ;Backup database ;False ;3.5.2004 1:00:11 ;9002
Kredit ;Backup transaction log ;True ;3.5.2004 1:31:01 ;0
Kredit ;Verify Backup ;True ;3.5.2004 1:31:24 ;0
Kredit ;Rebuild Indexes ;True ;3.5.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;3.5.2004 2:01:58 ;0
Note, than there is no reason for failing backup on Mondays, sometimes it fails on another day. From Friday 19:00 to Monday, 5:00, no DB activity is performed.
On the disks, there is enough space for all backup files and a half disk is permanently free.
After these corrections, I hope it'll go better.
Thanks once more, tomorrow I'll write, how it continues.
Best regards, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v diskusnm pspvku news:%23DiHakrMEHA.2592@.tk2msftngp13.phx.gbl...
> Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
> generally do db backup once a day and log backup one per hour.
> Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
> background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
> DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
>
|||Bad results.
Today the backup was proceeded normally, but database wasn't shrunk. After
2nd manual backups and shrinking the "empty" log has 142 MB!
|||Did you check the VLF layout? (See the article I referred to.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...
> Bad results.
> Today the backup was proceeded normally, but database wasn't shrunk. After
> 2nd manual backups and shrinking the "empty" log has 142 MB!
>
>
|||It's incredible! After 3rd manual backup and shrinking the log has
"compressed" to 8MB.
The VLF layout shows now 22 blocks, where 1st, 3rd and 22th are in use. Now
it's O.K., because shrink setting is to leave 10% of free blocks.
Why can't this work automatically and in the first try?
So, I'll try to make backups more often. Perhaps it helps.
Thank you a lot.
Rgds, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
|||So, after some experiments, I've written my own program for DB backup, which
works with all database files, save and shrink them as I need. It works
fine. It's the only thing, what I've had to do before my holidays.
My program makes backup and than shrinks the trnsact. log so many times,
till its size remains constant. May be it is strange, but it works. Because
this backups is done at 0:30, I'm not afraid of complications.
Thanks for your tips, I've read all, but I wasn't satisfied with this.
Best regards
Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
sql
I have a basic DB backup question:
I make DB backups every day at night using maintenance plan, where all DB
are saved. In 1st step the database backup is made, in 2nd step the
transaction log is saved. Sometimes, usually once a week (e.g. on Monday
1.00 am), one of the database backups failed with "... failed because DB log
is full" error. Now I make a manual backup, which is normally proceeded.
Then, the next backups work normally approx. 1 week. Situation repeates, but
not exactly every week.
After the log backup, I think it will truncate to small size. It is not
true, usual size of log is about 130 MB (database size is 80 MB). But after
a backup failes, the log size is about 270 MB.
In backup parameters the log truncate option is included, but in maintenance
plan this option is missing. So if I'll make backups manually, no problems
will occur. But I want work automatically.
What am I do to correct backup job?
Thank you in advance for your help
Vlastik
Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
generally do db backup once a day and log backup one per hour.
Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a basic DB backup question:
> I make DB backups every day at night using maintenance plan, where all DB
> are saved. In 1st step the database backup is made, in 2nd step the
> transaction log is saved. Sometimes, usually once a week (e.g. on Monday
> 1.00 am), one of the database backups failed with "... failed because DB log
> is full" error. Now I make a manual backup, which is normally proceeded.
> Then, the next backups work normally approx. 1 week. Situation repeates, but
> not exactly every week.
> After the log backup, I think it will truncate to small size. It is not
> true, usual size of log is about 130 MB (database size is 80 MB). But after
> a backup failes, the log size is about 270 MB.
> In backup parameters the log truncate option is included, but in maintenance
> plan this option is missing. So if I'll make backups manually, no problems
> will occur. But I want work automatically.
> What am I do to correct backup job?
> Thank you in advance for your help
> Vlastik
>
>
|||Hi, Tibor,
thank you for your response, it helps me to understand some functions of SQL 2000. Of course I've made both data and log backups daily, but something in them doesn't work.
Atfer your response, I checked the settings of my DBs and all settings are O.K. including autoshrink. I've only added a scheduled shrink daily after backup, because an automatic shrink didn't work and I don't know why. For example today, after backups and autoshrink, the database and log size were 112/149 MB. I made manual shrink and sizes changed to 84/41 MB. It's really crazy.
I checked the plan history in [msdb], but I'm not pretty enough to understand it. Database shrink was done every day. Here is a selection for database 'Kredit', which is the biggest:
database_name ;activity ;succeeded ;end_time ;error_number
Kredit ;Backup database ;False ;26.4.2004 1:00:09 ;9002
Kredit ;Backup transaction log ;True ;26.4.2004 1:31:00 ;0
Kredit ;Verify Backup ;True ;26.4.2004 1:31:22 ;0
Kredit ;Rebuild Indexes ;True ;26.4.2004 2:01:43 ;0
Kredit ;Shrink Database ;True ;26.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;27.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;27.4.2004 1:30:49 ;0
Kredit ;Verify Backup ;True ;27.4.2004 1:31:07 ;0
Kredit ;Rebuild Indexes ;True ;27.4.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;27.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;28.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;28.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;28.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;28.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;28.4.2004 2:01:59 ;0
Kredit ;Backup database ;True ;29.4.2004 1:00:35 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:00:45 ;0
Kredit ;Backup transaction log ;True ;29.4.2004 1:30:43 ;0
Kredit ;Verify Backup ;True ;29.4.2004 1:30:59 ;0
Kredit ;Rebuild Indexes ;True ;29.4.2004 2:01:42 ;0
Kredit ;Shrink Database ;True ;29.4.2004 2:01:57 ;0
Kredit ;Backup database ;True ;30.4.2004 1:00:37 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:00:48 ;0
Kredit ;Backup transaction log ;True ;30.4.2004 1:30:45 ;0
Kredit ;Verify Backup ;True ;30.4.2004 1:31:00 ;0
Kredit ;Rebuild Indexes ;True ;30.4.2004 2:01:48 ;0
Kredit ;Shrink Database ;True ;30.4.2004 2:02:04 ;0
Kredit ;Backup database ;False ;3.5.2004 1:00:11 ;9002
Kredit ;Backup transaction log ;True ;3.5.2004 1:31:01 ;0
Kredit ;Verify Backup ;True ;3.5.2004 1:31:24 ;0
Kredit ;Rebuild Indexes ;True ;3.5.2004 2:01:41 ;0
Kredit ;Shrink Database ;True ;3.5.2004 2:01:58 ;0
Note, than there is no reason for failing backup on Mondays, sometimes it fails on another day. From Friday 19:00 to Monday, 5:00, no DB activity is performed.
On the disks, there is enough space for all backup files and a half disk is permanently free.
After these corrections, I hope it'll go better.
Thanks once more, tomorrow I'll write, how it continues.
Best regards, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v diskusnm pspvku news:%23DiHakrMEHA.2592@.tk2msftngp13.phx.gbl...
> Are you saying that you only do log backup once a week? If so, I suggest you do it more frequently. I
> generally do db backup once a day and log backup one per hour.
> Also, log backup only empties the log file, it doesn't shrink the file. If you have autoshrink on, then the
> background process can shrink the file after the log is emptied. Or, your job does an explicit shrink using
> DBCC SHRINKDB or DBCC SHRINKFILE. However, there are disadvantages with shrinking the files, see:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message news:%23N3PuRpMEHA.3940@.tk2msftngp13.phx.gbl...
>
|||Bad results.
Today the backup was proceeded normally, but database wasn't shrunk. After
2nd manual backups and shrinking the "empty" log has 142 MB!
|||Did you check the VLF layout? (See the article I referred to.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"greybeard" <bartos@.spsmvbr.cz> wrote in message news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...
> Bad results.
> Today the backup was proceeded normally, but database wasn't shrunk. After
> 2nd manual backups and shrinking the "empty" log has 142 MB!
>
>
|||It's incredible! After 3rd manual backup and shrinking the log has
"compressed" to 8MB.
The VLF layout shows now 22 blocks, where 1st, 3rd and 22th are in use. Now
it's O.K., because shrink setting is to leave 10% of free blocks.
Why can't this work automatically and in the first try?
So, I'll try to make backups more often. Perhaps it helps.
Thank you a lot.
Rgds, Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
|||So, after some experiments, I've written my own program for DB backup, which
works with all database files, save and shrink them as I need. It works
fine. It's the only thing, what I've had to do before my holidays.
My program makes backup and than shrinks the trnsact. log so many times,
till its size remains constant. May be it is strange, but it works. Because
this backups is done at 0:30, I'm not afraid of complications.
Thanks for your tips, I've read all, but I wasn't satisfied with this.
Best regards
Vlastik
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> pe v
diskusnm pspvku news:%23$MLkU$MEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Did you check the VLF layout? (See the article I referred to.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "greybeard" <bartos@.spsmvbr.cz> wrote in message
news:e7YTqu%23MEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
After
>
sql
Monday, March 19, 2012
Backup Database Maintenance Plan eats up too much disk space
I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
files
than
Run through the maint wiz again and you will find just that option :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
name,
files
than
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
http://groups.google.com/groups?oi=...ublic.sqlserver
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
quote:
> the older backup files. Is there an automated way to have SQL delete
files
quote:
> older than a given number of days - for example, remove all files older
than
quote:
> 3 days.
Run through the maint wiz again and you will find just that option :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
quote:
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
quote:
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
quote:
> older than a given number of days - for example, remove all files older
than
quote:|||D-oh! I feel so stupid...
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
quote:
remove[QUOTE]
> files
> than
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> name,
used[QUOTE]
remove[QUOTE]
> files
> than
this[QUOTE]
'DailyDBBackup.bak'[QUOTE]
>
Backup Database Maintenance Plan eats up too much disk space
I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days.
Run through the maint wiz again and you will find just that option :-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days.
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> > I created a basic DB maintenance plan to routinely backup our database.
> > Every backup job that runs through this plan is created using a unique
> name,
> > putting the date into the back file name, as in
> > DBProduction_db_200401120000.bak.
> >
> > The problem is this creates a new backup file each day - our backups our
> > faily large and after only a few days, the drive space is completely
used
> > up.
> >
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days. When we do a log ship using a maintenance plan, it asks for
this
> > information, but I haven't seen it when just running basic back ups and
> > transaction log back ups.
> >
> > What about changing the default name to something like
'DailyDBBackup.bak'
> > so that it just overwrites previous backup everytime?
> >
> >
> >
>
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days.
Run through the maint wiz again and you will find just that option :-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days.
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> > I created a basic DB maintenance plan to routinely backup our database.
> > Every backup job that runs through this plan is created using a unique
> name,
> > putting the date into the back file name, as in
> > DBProduction_db_200401120000.bak.
> >
> > The problem is this creates a new backup file each day - our backups our
> > faily large and after only a few days, the drive space is completely
used
> > up.
> >
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days. When we do a log ship using a maintenance plan, it asks for
this
> > information, but I haven't seen it when just running basic back ups and
> > transaction log back ups.
> >
> > What about changing the default name to something like
'DailyDBBackup.bak'
> > so that it just overwrites previous backup everytime?
> >
> >
> >
>
Saturday, February 25, 2012
Backup and Restore
Hi All,
I may have a basic question in backup and restore (sql server 2000).
Am taking full backup on a daily basis, differential backup every 4 hours and transaction log backup every 30 mins. Lets assume the series is like this (am placing backup on a different machine);
8:00 AM -- Full backup
12:00 PM -- Differential backup
12:30 PM -- Transaction log backup
And at 12:55 PM the system crashes, after which i will not have access to the online transaction logs.
My question is,
Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for which I haven't taken any backup?
Thanks in advance
GreneIf you don't have access to the transaction log, you won't be able to
perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
log. Restore to the point of failure is therefore not possible. You can only
recover to 12:30 with your last transaction log backup.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Grene" <anonymous@.discussions.microsoft.com> wrote in message
news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> Hi All,
> I may have a basic question in backup and restore (sql server 2000).
> Am taking full backup on a daily basis, differential backup every 4 hours
and transaction log backup every 30 mins. Lets assume the series is like
this (am placing backup on a different machine);
> 8:00 AM -- Full backup
> 12:00 PM -- Differential backup
> 12:30 PM -- Transaction log backup
> And at 12:55 PM the system crashes, after which i will not have access to
the online transaction logs.
> My question is,
> Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
which I haven't taken any backup?
> Thanks in advance
> Grene
>|||It might be worth mentioning that if the SQL Server install is toast, but the ldf file(s) are there,
then the files can be transferred to another machine where SQL Server is installed and the
NO_TRUNCATE backup ban be ran on this machine. There's a KB on this scenario, btw.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:uLgo2KZrDHA.2440@.TK2MSFTNGP10.phx.gbl...
> If you don't have access to the transaction log, you won't be able to
> perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
> log. Restore to the point of failure is therefore not possible. You can only
> recover to 12:30 with your last transaction log backup.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Grene" <anonymous@.discussions.microsoft.com> wrote in message
> news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> > Hi All,
> >
> > I may have a basic question in backup and restore (sql server 2000).
> >
> > Am taking full backup on a daily basis, differential backup every 4 hours
> and transaction log backup every 30 mins. Lets assume the series is like
> this (am placing backup on a different machine);
> > 8:00 AM -- Full backup
> > 12:00 PM -- Differential backup
> > 12:30 PM -- Transaction log backup
> > And at 12:55 PM the system crashes, after which i will not have access to
> the online transaction logs.
> >
> > My question is,
> > Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
> which I haven't taken any backup?
> >
> > Thanks in advance
> > Grene
> >
> >
>
I may have a basic question in backup and restore (sql server 2000).
Am taking full backup on a daily basis, differential backup every 4 hours and transaction log backup every 30 mins. Lets assume the series is like this (am placing backup on a different machine);
8:00 AM -- Full backup
12:00 PM -- Differential backup
12:30 PM -- Transaction log backup
And at 12:55 PM the system crashes, after which i will not have access to the online transaction logs.
My question is,
Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for which I haven't taken any backup?
Thanks in advance
GreneIf you don't have access to the transaction log, you won't be able to
perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
log. Restore to the point of failure is therefore not possible. You can only
recover to 12:30 with your last transaction log backup.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Grene" <anonymous@.discussions.microsoft.com> wrote in message
news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> Hi All,
> I may have a basic question in backup and restore (sql server 2000).
> Am taking full backup on a daily basis, differential backup every 4 hours
and transaction log backup every 30 mins. Lets assume the series is like
this (am placing backup on a different machine);
> 8:00 AM -- Full backup
> 12:00 PM -- Differential backup
> 12:30 PM -- Transaction log backup
> And at 12:55 PM the system crashes, after which i will not have access to
the online transaction logs.
> My question is,
> Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
which I haven't taken any backup?
> Thanks in advance
> Grene
>|||It might be worth mentioning that if the SQL Server install is toast, but the ldf file(s) are there,
then the files can be transferred to another machine where SQL Server is installed and the
NO_TRUNCATE backup ban be ran on this machine. There's a KB on this scenario, btw.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:uLgo2KZrDHA.2440@.TK2MSFTNGP10.phx.gbl...
> If you don't have access to the transaction log, you won't be able to
> perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
> log. Restore to the point of failure is therefore not possible. You can only
> recover to 12:30 with your last transaction log backup.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Grene" <anonymous@.discussions.microsoft.com> wrote in message
> news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> > Hi All,
> >
> > I may have a basic question in backup and restore (sql server 2000).
> >
> > Am taking full backup on a daily basis, differential backup every 4 hours
> and transaction log backup every 30 mins. Lets assume the series is like
> this (am placing backup on a different machine);
> > 8:00 AM -- Full backup
> > 12:00 PM -- Differential backup
> > 12:30 PM -- Transaction log backup
> > And at 12:55 PM the system crashes, after which i will not have access to
> the online transaction logs.
> >
> > My question is,
> > Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
> which I haven't taken any backup?
> >
> > Thanks in advance
> > Grene
> >
> >
>
Friday, February 24, 2012
Backup and Log Strategies
Hi all,
I'm mostly an application developer - so please don't flog me for not
knowing things which are probably very basic. Our machine keeps
running out of space. We have all our data/bak/log files on e: , and
they have some kind of maintenance plan set up. Backups of the data
and log are created one after another and never deleted. In the 4
years we've been running various systems, we've never had to restore to
a previous time - just restore to current state once.
I've also read that the transaction log should automatically shrink
after it is backed up. When I do this manually it doesn't happen - I
have to run 'shrink db' after to make it happen. It doesn't seem to
happen with their scheduled routines either.
Do people write scripts to delete old backups?
Should we be using the overwrite option so we don't get a buildup?
What other tips might you have?
thanks alot
SamMaint Plans creates a new file for every backup. so if you use maint plans, there no "overwrite"
considerations. MPs also have option to remove backups "older than...". Check the plan to see what
it is configured for. MPs has (among other things) one bad behavior, though. If a plan fails, it
stops. And removal of old backup files is among the last things performed. One such frequent reason
if that you have included databases in simple recovery mode for plans that try to do log backups. So
sqlmaint tries a log backups for, for example master, it fails and the execution stops without
removing *any* old backup files.
The ldf file is emptied when you do a log backup, that is not the same as shrinking the file size.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for elaborations on the subject.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samuelgreene@.gmail.com> wrote in message
news:1154533293.924009.30720@.m73g2000cwd.googlegroups.com...
> Hi all,
> I'm mostly an application developer - so please don't flog me for not
> knowing things which are probably very basic. Our machine keeps
> running out of space. We have all our data/bak/log files on e: , and
> they have some kind of maintenance plan set up. Backups of the data
> and log are created one after another and never deleted. In the 4
> years we've been running various systems, we've never had to restore to
> a previous time - just restore to current state once.
> I've also read that the transaction log should automatically shrink
> after it is backed up. When I do this manually it doesn't happen - I
> have to run 'shrink db' after to make it happen. It doesn't seem to
> happen with their scheduled routines either.
> Do people write scripts to delete old backups?
> Should we be using the overwrite option so we don't get a buildup?
> What other tips might you have?
> thanks alot
> Sam
>|||Thank you very much!
I'm mostly an application developer - so please don't flog me for not
knowing things which are probably very basic. Our machine keeps
running out of space. We have all our data/bak/log files on e: , and
they have some kind of maintenance plan set up. Backups of the data
and log are created one after another and never deleted. In the 4
years we've been running various systems, we've never had to restore to
a previous time - just restore to current state once.
I've also read that the transaction log should automatically shrink
after it is backed up. When I do this manually it doesn't happen - I
have to run 'shrink db' after to make it happen. It doesn't seem to
happen with their scheduled routines either.
Do people write scripts to delete old backups?
Should we be using the overwrite option so we don't get a buildup?
What other tips might you have?
thanks alot
SamMaint Plans creates a new file for every backup. so if you use maint plans, there no "overwrite"
considerations. MPs also have option to remove backups "older than...". Check the plan to see what
it is configured for. MPs has (among other things) one bad behavior, though. If a plan fails, it
stops. And removal of old backup files is among the last things performed. One such frequent reason
if that you have included databases in simple recovery mode for plans that try to do log backups. So
sqlmaint tries a log backups for, for example master, it fails and the execution stops without
removing *any* old backup files.
The ldf file is emptied when you do a log backup, that is not the same as shrinking the file size.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for elaborations on the subject.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samuelgreene@.gmail.com> wrote in message
news:1154533293.924009.30720@.m73g2000cwd.googlegroups.com...
> Hi all,
> I'm mostly an application developer - so please don't flog me for not
> knowing things which are probably very basic. Our machine keeps
> running out of space. We have all our data/bak/log files on e: , and
> they have some kind of maintenance plan set up. Backups of the data
> and log are created one after another and never deleted. In the 4
> years we've been running various systems, we've never had to restore to
> a previous time - just restore to current state once.
> I've also read that the transaction log should automatically shrink
> after it is backed up. When I do this manually it doesn't happen - I
> have to run 'shrink db' after to make it happen. It doesn't seem to
> happen with their scheduled routines either.
> Do people write scripts to delete old backups?
> Should we be using the overwrite option so we don't get a buildup?
> What other tips might you have?
> thanks alot
> Sam
>|||Thank you very much!
Backup and Log Strategies
Hi all,
I'm mostly an application developer - so please don't flog me for not
knowing things which are probably very basic. Our machine keeps
running out of space. We have all our data/bak/log files on e: , and
they have some kind of maintenance plan set up. Backups of the data
and log are created one after another and never deleted. In the 4
years we've been running various systems, we've never had to restore to
a previous time - just restore to current state once.
I've also read that the transaction log should automatically shrink
after it is backed up. When I do this manually it doesn't happen - I
have to run 'shrink db' after to make it happen. It doesn't seem to
happen with their scheduled routines either.
Do people write scripts to delete old backups?
Should we be using the overwrite option so we don't get a buildup?
What other tips might you have?
thanks alot
SamMaint Plans creates a new file for every backup. so if you use maint plans,
there no "overwrite"
considerations. MPs also have option to remove backups "older than...". Chec
k the plan to see what
it is configured for. MPs has (among other things) one bad behavior, though.
If a plan fails, it
stops. And removal of old backup files is among the last things performed. O
ne such frequent reason
if that you have included databases in simple recovery mode for plans that t
ry to do log backups. So
sqlmaint tries a log backups for, for example master, it fails and the execu
tion stops without
removing *any* old backup files.
The ldf file is emptied when you do a log backup, that is not the same as sh
rinking the file size.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for elaborations o
n the subject.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samuelgreene@.gmail.com> wrote in message
news:1154533293.924009.30720@.m73g2000cwd.googlegroups.com...
> Hi all,
> I'm mostly an application developer - so please don't flog me for not
> knowing things which are probably very basic. Our machine keeps
> running out of space. We have all our data/bak/log files on e: , and
> they have some kind of maintenance plan set up. Backups of the data
> and log are created one after another and never deleted. In the 4
> years we've been running various systems, we've never had to restore to
> a previous time - just restore to current state once.
> I've also read that the transaction log should automatically shrink
> after it is backed up. When I do this manually it doesn't happen - I
> have to run 'shrink db' after to make it happen. It doesn't seem to
> happen with their scheduled routines either.
> Do people write scripts to delete old backups?
> Should we be using the overwrite option so we don't get a buildup?
> What other tips might you have?
> thanks alot
> Sam
>|||Thank you very much!
I'm mostly an application developer - so please don't flog me for not
knowing things which are probably very basic. Our machine keeps
running out of space. We have all our data/bak/log files on e: , and
they have some kind of maintenance plan set up. Backups of the data
and log are created one after another and never deleted. In the 4
years we've been running various systems, we've never had to restore to
a previous time - just restore to current state once.
I've also read that the transaction log should automatically shrink
after it is backed up. When I do this manually it doesn't happen - I
have to run 'shrink db' after to make it happen. It doesn't seem to
happen with their scheduled routines either.
Do people write scripts to delete old backups?
Should we be using the overwrite option so we don't get a buildup?
What other tips might you have?
thanks alot
SamMaint Plans creates a new file for every backup. so if you use maint plans,
there no "overwrite"
considerations. MPs also have option to remove backups "older than...". Chec
k the plan to see what
it is configured for. MPs has (among other things) one bad behavior, though.
If a plan fails, it
stops. And removal of old backup files is among the last things performed. O
ne such frequent reason
if that you have included databases in simple recovery mode for plans that t
ry to do log backups. So
sqlmaint tries a log backups for, for example master, it fails and the execu
tion stops without
removing *any* old backup files.
The ldf file is emptied when you do a log backup, that is not the same as sh
rinking the file size.
See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for elaborations o
n the subject.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samuelgreene@.gmail.com> wrote in message
news:1154533293.924009.30720@.m73g2000cwd.googlegroups.com...
> Hi all,
> I'm mostly an application developer - so please don't flog me for not
> knowing things which are probably very basic. Our machine keeps
> running out of space. We have all our data/bak/log files on e: , and
> they have some kind of maintenance plan set up. Backups of the data
> and log are created one after another and never deleted. In the 4
> years we've been running various systems, we've never had to restore to
> a previous time - just restore to current state once.
> I've also read that the transaction log should automatically shrink
> after it is backed up. When I do this manually it doesn't happen - I
> have to run 'shrink db' after to make it happen. It doesn't seem to
> happen with their scheduled routines either.
> Do people write scripts to delete old backups?
> Should we be using the overwrite option so we don't get a buildup?
> What other tips might you have?
> thanks alot
> Sam
>|||Thank you very much!
Sunday, February 19, 2012
Backup a Database
I want to be able to Backup a SQL Server 2005 Database from Visual Basic 2005. I'm looking for help here, as I have searched online and no luck.
ThanksEasy way - construct a SQL statement (see BACKUP DATABASE in books online for syntax) and submit that to the SQL Server for execution.
Still easy but I've never used - try using the SMO object model.
Easiest of all - have your server back itself up rather than rely on an external application.|||I want to be able to Backup a SQL Server 2005 Database from Visual Basic 2005. I'm looking for help here, as I have searched online and no luck.
Thanks
Ask your DBA
ThanksEasy way - construct a SQL statement (see BACKUP DATABASE in books online for syntax) and submit that to the SQL Server for execution.
Still easy but I've never used - try using the SMO object model.
Easiest of all - have your server back itself up rather than rely on an external application.|||I want to be able to Backup a SQL Server 2005 Database from Visual Basic 2005. I'm looking for help here, as I have searched online and no luck.
Thanks
Ask your DBA
Subscribe to:
Posts (Atom)