April 26, 2010 at 8:29 am
Hi All,
Please suggest me if am doing wrong.
Am using below Query in SQL JOB to automate the backup to network path. Its working successfully.
The thing is it executes 1t 01:00 AM every night and restores on DEV server daily. Working fine.
Question: One day backup is 10.98GB , second day file size - 21.4 GB, third day file size is 44.33GB.
Strange !!! why the file size gradually increasing. What SQL Server is doing here? Its doing backup with same name loading all days files into single file name?
The file name is ASMBD.bak. Donno why the file size is increasing day to day. Is there anyway to overcome this. I need exact file size everyday.
Please help me with scripts which backups evaryday and before backing up it deletes the older file.
Or
Suggest me the best way to do..
------------------------------------------------------------------------------
BACKUP DATABASE [DatabasetoBackup] TO DISK = N'\\AGDEVSQL1\Backups\ASMBD.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
----------------------------------------------------------------------------
Cheers,
- Win.
" Have a great day "
April 26, 2010 at 8:39 am
- Win. (4/26/2010)
Hi All,Please suggest me if am doing wrong.
Am using below Query in SQL JOB to automate the backup to network path. Its working successfully.
The thing is it executes 1t 01:00 AM every night and restores on DEV server daily. Working fine.
Question: One day backup is 10.98GB , second day file size - 21.4 GB, third day file size is 44.33GB.
Strange !!! why the file size gradually increasing. What SQL Server is doing here? Its doing backup with same name loading all days files into single file name?
The file name is ASMBD.bak. Donno why the file size is increasing day to day. Is there anyway to overcome this. I need exact file size everyday.
Please help me with scripts which backups evaryday and before backing up it deletes the older file.
Or
Suggest me the best way to do..
------------------------------------------------------------------------------
BACKUP DATABASE [DatabasetoBackup] TO DISK = N'\\AGDEVSQL1\Backups\ASMBD.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
----------------------------------------------------------------------------
Your backup is appending each subsequent backup to the same file. If the file is being backed up elsewhere after the SQL Server backup (which I recommend), change the NOINIT to INIT in your backup database command. This will allow SQL Server to overwrite the previous backup.
April 26, 2010 at 9:07 am
Thanks Lynn for the fast response.
-----------------------------------------------------------------------------------
change the NOINIT to INIT in your backup database command. This will allow SQL Server to overwrite the previous backup.
------------------------------------------------------------------------------------
BACKUP DATABASE [DatabasetoBackup] TO DISK = N'\\AGDEVSQL1\Backups\ASMBD.bak'
WITH NOFORMAT,
INIT,
NAME = N'Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
The above query will overwrite the everyday backup file right?
Please confirm. I Will test this and update you.
Cheers,
- Win.
" Have a great day "
April 28, 2010 at 3:42 am
Done.
Thanks Lynn.
Cheers,
- Win.
" Have a great day "
April 29, 2010 at 6:00 pm
- Win. (4/28/2010)
Done.Thanks Lynn.
1. Excellent that you took the trouble to post feedback. Everybody will know this solution worked.
2. Why not use the 2005 Maintenance Plan?
It allows the backup operation (specify append/overwrite), cleaning up the previous backups (specify how many days to keep), cleaning up the backup history from the SQL system files, cleaning up the logs created by the plan, emailing to an operator when things go wrong, and running an integrity check on the database.
HTH
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply