asked on SQL DB Backup to External Drive
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.
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.
Microsoft SQL ServerStorage Software
Last Comment
mlcktmguy
Its clearly that you need a backup solution...for these cases i use this
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
ASKER
I intend to try the Ola Hallegren solution but have not had a chance yet.
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.
ASKER
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?
Is it included with Ola's download or SQL Developer 2014?
Or do I have to download and install SQL Agent first?
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.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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?
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?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Perfect for what I needed. Many thanks