How to Backup Sql Database to External Drive
- Home
- Databases
- Microsoft SQL Server
We have a SQL Server on a VM, it is Server 2008 R2. And running SQL Server 2008.
The old IT guy here used to just back up the SQL databases and some other data files using the built in Windows Server Backup every night at about 1 am to a USB drive.
No issues with that so far.
Database is about 6 GB's.
My question is, is this enough? Is this a good strategy? I have never had to back up SQL databases before. Usually at my past jobs we had database admins who handled things like that. My new position is at a much smaller company. We don't have dedicated SQL admins, So I am looking for some valuable info into this situation. We currently do not have any NAS/SAN's or any kind of network storage. We have one VM host at the moment with a SQL server, Terminal server, and a couple of other servers on that host. We will soon have one more VM host added next month. Other than that we have the USB hard drive he was using to back up. So with my current set up, I am looking for a good back up plan.
Also if you would like to go into more detail and give me suggestions for the future for backing up SQL databases including offsite and disaster recovery options or methods you prefer I would greatly appreciate it. Thought I'd pick your brains for a while on this.
Denis Kelley wrote:
iamanomad wrote:
I'm not a DBA, but what we've done is use sql server's built in maintenance plans to do a backup (aka dump and don't forget the transaction logs) each night and to also back up to Veeam. that was you have the whole server and the data. It all depends on how critical the data is on how often you need to dump, etc.
Ditto here. Backup databases, backup log files, and have maintenance plans to clean it up. Then pickup the files are part of the regular server backup.
ALL databases - no matter what they are -- should only ever get backed up with their own utilities. There is a lot of magic in the control and datafiles, and it is intricately coordinated. Most backup software is designed to back up and restore files. You are not trying to back up files -- you are backing up databases. Most have a Recovery Manager of some sort, and I strongly suggest you use it. It is designed to protect the database -- not the files.
Dennis is correct -- use the built-in tools to back up your database to disk files. Then, use your regular backup software to back up those files.
19 Replies
What hypervisor is your VM running on? (VMWare, Hyper-V, etc). I like to use Veeam for backups, this may be a good fit for you, or perhaps Unitrends. This allows you to back up everything at the VM level. In addition to this, we also use SQL backup, to back up to a 2nd virtual hard disk. I then use SyncBack to pull these backups to a separate machine as another level of protection. (Then nightly, all these backups are copied offsite to my DR site).
The best way is to set up a maintenance plan to back up the primary database and one for the transaction logs and then back up those files with your preferred backup method. It is far easier to use the SQL server to recover a database and logs than using an agent based backup.
I'm not a DBA, but what we've done is use sql server's built in maintenance plans to do a backup (aka dump and don't forget the transaction logs) each night and to also back up to Veeam. that was you have the whole server and the data. It all depends on how critical the data is on how often you need to dump, etc.
We use VMware, ESXI 5.1.
I have downloaded the free trial of Veeam, can you set schedules with Veeam? And can Veeam back up files, and data, or is it strictly a VM Imaging software?
iamanomad wrote:
I'm not a DBA, but what we've done is use sql server's built in maintenance plans to do a backup (aka dump and don't forget the transaction logs) each night and to also back up to Veeam. that was you have the whole server and the data. It all depends on how critical the data is on how often you need to dump, etc.
Ditto here. Backup databases, backup log files, and have maintenance plans to clean it up. Then pickup the files are part of the regular server backup.
So inside of SQL Studio Manager you can set up back ups on a schedule? Or is it somewhere else?
A simple way to do it is to just download my standard backup scripts. You run one script and it will set up jobs to backup all databases, log files, and delete the old files.
http://www.sqlsoldier.com/wp/sqlserver/day13of31daysofdisasterrecoverystandardbackupscripts
Denis Kelley wrote:
iamanomad wrote:
I'm not a DBA, but what we've done is use sql server's built in maintenance plans to do a backup (aka dump and don't forget the transaction logs) each night and to also back up to Veeam. that was you have the whole server and the data. It all depends on how critical the data is on how often you need to dump, etc.
Ditto here. Backup databases, backup log files, and have maintenance plans to clean it up. Then pickup the files are part of the regular server backup.
ALL databases - no matter what they are -- should only ever get backed up with their own utilities. There is a lot of magic in the control and datafiles, and it is intricately coordinated. Most backup software is designed to back up and restore files. You are not trying to back up files -- you are backing up databases. Most have a Recovery Manager of some sort, and I strongly suggest you use it. It is designed to protect the database -- not the files.
Dennis is correct -- use the built-in tools to back up your database to disk files. Then, use your regular backup software to back up those files.
You can set up the maintenance task with SQL Server, so make sure you're not using SQL Server Express. Otherwise, you'd have to run the backups manually.
Also make sure your SQL Server Agent service is running. This is what I do and never have to worry about database backups. I have the databases backing up to a small separate server, then I have that one backing up to an external USB hard drive sitting on my desk.
This is what I used to set mine up when our DBA quit.
http:/
What and where are the built in tools for Microsoft SQL Server? Any links you can point me to for more info on that?
The tools are in SQL Server Management Studio, assuming you installed that feature.
Open SQL Server Management Studio, and once you connect, expand Management, right-click Maintenance Plan and choose New Maintenance Plan (or the wizard).
Thank you, yes I found it. Have some studying to do as I am not too familiar with SQL Server Management Studio. Thanks.
jrichardsonfdh wrote:
Thank you, yes I found it. Have some studying to do as I am not too familiar with SQL Server Management Studio. Thanks.
They're all about the same. Can't speak for how to do it with SQL, as I'm an Oracle DBA, but what you are shooting for is to have a HOT backup of the database (never has to come down) and a backup of all the transaction logs. If you need to recover, you have a restorable image from last night at 10PM plus a copy of all the transactions since then. You can use these to restore the database to a point in time. Not sure if all releases support it, but it can be done. You may need to set up a script to perform the "manual" version and slap that into a scheduled task; the database release you are using may not have that piece built in.
Just realized that should be "this morning at 1AM" -- Ah, yes... Timing is of the essence!
Have you checked out any cloud hosted solutions such as Cloudessa?
No I have not looked into any cloud solutions yet. I would need them to be hipaa compliant.
I am planning to use your scripts. Still new to all of these. My goal is to put a backup in place first and then indulge in discussion on what is the right method etc. The instructions state that I can run the DeployBackups.sql on the server I need the databases backed up. In the downloaded ZIP, I find DeployStandardBackups.sql I am assuming this is the same file. Also, if I just run the script without modifications, it is meant to backup everything?
Does this contain maintenance aswell? Thank you in advance.
Brand Representative for Microsoft
If you want to get a broader reaching maintenance system set up, I recommend using Minionware's free scripts: http://minionware.net
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.
How to Backup Sql Database to External Drive
Source: https://community.spiceworks.com/topic/386439-best-way-to-back-up-sql-database
0 Response to "How to Backup Sql Database to External Drive"
Post a Comment