SQL backup script optimization
Posted on 2011-10-29
A few months ago I began to execute a daily task that will backup my SQL databases to another disk on the machine, which is then copied off-site. I am now re-configuring my backup solution/routine and have noticed that the original backup script for the database is creating a 300GB file for a particular database but when I run the script and save it to a different location it is only 2GB. I am assuming that my script is set up to continue to append to the existing file.
That sql command is:
BACKUP DATABASE [master] TO [E Drive - master] WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
My primary goal is have a way to restore the database in the event of a disaster or failure. My secondary goal is to keep it as simple as possible (currently it is a scheduled task that calls SQLCMD and passes an SQL file with the command pasted above.
What can be done to keep the exported/backed up database a true size of the real database? It makes sense that the true size of the databases is closer to the 2GB than the 350GB as the partion the database is on is less than 100GB.
Thanks in advance!