vertigogroup
asked on
MS SQL 2005 Backup Script / Question
We are currently using Symantec BackupExec with the SQL option to backup our files and SQL servers. I am not very happy with Symantec as it always seems to have issues. I am looking for a free solution that will work in 2 parts.We currently use 2 NAS devices that are swapped out every Monday with one taken offsite.
Part 1: I know how to create a SQL full backup and scheduling it out through the GUI. However, this requires me to always monitor the backup folder as it will continue to grow. I would like a backup script to backup all databases on a nightly basis (full or not) to a directory one the server that will auto purge after one week.
Part 2: I would then like to drop Symantec entirely to backup the system state and all files on the server, including the SQL backup files from Part 1 above.
What is the best method for doing this and what is the best free software? Also, what would the script be to run the SQL part described in Part 1?
Many Thanks,
Ryan
Part 1: I know how to create a SQL full backup and scheduling it out through the GUI. However, this requires me to always monitor the backup folder as it will continue to grow. I would like a backup script to backup all databases on a nightly basis (full or not) to a directory one the server that will auto purge after one week.
Part 2: I would then like to drop Symantec entirely to backup the system state and all files on the server, including the SQL backup files from Part 1 above.
What is the best method for doing this and what is the best free software? Also, what would the script be to run the SQL part described in Part 1?
Many Thanks,
Ryan
chapmandew
So I understand, you want something to just auto-delete the old backup files?
ASKER
Yes, just the old SQL backup files as to prevent it eating up all the space on the hard drive.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK. I have setup the script via the console so that it meets all my needs. However, every evening when it is supposed to run, it prompts for a user name and password. How do I include this into the script? Thanks, Ryan
You don't include that in the script. That is something you'll need to setup when you configure the windows scheduled job.
ASKER
I have set this in the scheduled jobs. When run, it opens SQL Studio and waits for me to authenticate before running the script in SQL. I have it set to windows authentication.
hmm...something is off. Did you use sqlcmd?
ASKER
I used Microsoft SQL Server Management Studio Express to create a backup script that I then set to run every evening using the task scheduler. Below is a sample script I used:
BACKUP DATABASE [BCD] TO DISK = N'\\servername\SQLBackup\d
GO
Thanks,
Ryan
BACKUP DATABASE [BCD] TO DISK = N'\\servername\SQLBackup\d
GO
Thanks,
Ryan