I am planning to backup my database files from network drive to one other. Here is my requirement:
- All database should be backed up excluding certain file names starting with msdb, model etc.
- this backup should be held every day at 1 : 00 AM ?
- Remove 2 weeks older files.
I am using SQL server 2012.
Since you said “msdb”, I’ll just assume you’re using Microsoft SQL. Knowing the version would be somewhat helpful.
Do this with either a Windows Scheduled Task or a SQL Maintenance Plan. The latter will be simple and graphical. A SQL Maintenance Plan will be easier because you can simply specify “all user databases”, which excludes the system databases.
The former will involve writing a short BAT file that will have something like
SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak’”
Source : http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/
You’ll need one line for every database. You’d have to work in old-file pruning yourself, perhaps using something like
Now, I hope you’re not here to ask us to write this whole thing for you. If you give it a try and are stuck on something, then post that as a new question. With more details, preferably.