SQL DB Backup to External Drive

mlcktmguy
mlcktmguy used Ask the Experts™
on
This is a single user, non networked machine.  SQL Server 2014 Developer, Win 7 Pro

I have an old fashioned .Bat file I run periodically that copies non SQL files to an external drive.

I recently started using SQL Server.

To back up SQL I originally added the directory where the SQL files are located to the .bat file but they won't copy.  I get an 'Access Denied' message and they are bypassed.

I don't know why I would be denied access since this is a single user machine and I am the only user.  I am denied access even if I run the bat file as Adminstrator (Run as Administrator)

The SQL DB was created on another machine, backed up and then restored on this machine.  Perhaps that has something to do with it.

Currently, when I want to back up the SQL DB, I do it manually using the 'Task', 'Backup' option from within the SQL Manager.

Is there a way to run the SQL backup I have set up in the SQL Manager from a bat file?

Are there better ways to accomplish this?

I have been looking into Cloud backups Carbonite, SOS, etc.. but I am hesitant because I'm not sure that they would be able to backup the SQL files either.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Its clearly that you need a backup solution...for these cases i use this
jorge_torizResearch & Development Manager

Commented:
I would create a job in SQL Server Agent that makes the backup to your desired destination.
Have you tried the maintenance plan wizard? You can create backup schedules and such. With your permission issues I am not sure if that will work for you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know why I would be denied access since this is a single user machine and I am the only user.
SQL Server locks the files that it's using to guarantee the data integrity of the databases. Doesn't matter if you're the server administrator or not. It just won't let one touch the files.

As suggested above from the Experts, you should use SQL Server native backup solution otherwise you'll need to stop de SQL Server service to release the lock on the database files so you can copy them. After the files being copied you must restart SQL Server service. This is a solution that I don't recommend because when you stop SQL Server it will kill all running processes so the side effect can be highly dangerous, like losing data.
Best way to create a backup automated solution is to use Ola Hallegren's stored procedures. It will even create the SQL Jobs for you, which you will only have to schedule after.  His solution is free and very well known and appreciated in SQL Server world:

https://ola.hallengren.com/
jorge_torizResearch & Development Manager

Commented:
Well, I think the best solution would be to learn to do Maintenance Plans, its so easy and you don't need third party tools or methods... from a very personal point of view.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Well, I think the best solution would be to learn to do Maintenance Plans, its so easy and you don't need third party tools or methods... from a very personal point of view.
It's ok if used only for backups. For anything else is very bad. Ola's maintenance scripts are top.

Author

Commented:
I intend to try the Ola Hallegren solution but have not had a chance yet.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Should be a quick thing to do. Download the backup script and run it in you MSSQL instance. You'll see in SQL Agent the new backup jobs and just need to schedule them.

Author

Commented:
I don't know that I have SQL Agent.  

Is it included with Ola's download or SQL Developer 2014?  

Or do I have to download and install SQL Agent first?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You have  SQL Server 2014 Developer so it MUST to be installed when you installed SQL Server instance. The SQL Agent service might not be started, though.
In Management Studio at the bottom in the Object Explorer panel you should see the SQL Server Agent node. Expand that to see the jobs:
SQL_Agent.PNG

Author

Commented:
Thanks to all.  I downloaded and installed Ola's product.  Stored Procedures  'DatabaseBackup' and 'IndexOptimize' have been created.

I can execute the created 'DatabaseBackup' and 'IndexOptimize' SP's by right clicking on them within SSMA and selecting the 'Execute Stored Procedure' option.  I then enter the appropriate parameters and the SP executes, creating a backup or performing Index Optimization.

However, I am unclear on the next step, which would be to schedule these jobs so that they are invoked and run without intervention.

How do I accomplish that?
You should have downloaded the whole maintenance script instead of only the particular procedures, you mentioned. The main script will create all the objects you need, including the SQL jobs and only thing left is to schedule them.

You can go to

https://ola.hallengren.com/downloads.html

and click on the link at the very top of the page:

Download MaintenanceSolution.sql.

Open that script and simply execute it. The existing objects will just be updated. After that you will see in the SQL Agent node the new jobs, which you will have to just schedule. Don't worry, even if you install the whole solution nothing will happen until you either manually execute a stored procedure or an SQL job or until you schedule any of the jobs. The jobs are created with no schedules.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
However, I am unclear on the next step, which would be to schedule these jobs so that they are invoked and run without intervention.
Could you see the jobs? If so, just click on them and you'll see the schedule tab.

Author

Commented:
Perfect for what I needed.  Many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial