MSSQLExpress 2012 backup

I'm running MSSQL Express 2012 on a Server 2012 server and need to backup the database each day.

 I use CA Arcserve for backups which is able to back up the folder listed below which contains the database files.
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

My question is if I need to restore the database, will restoring the folder C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA get the database back?


Thanks,
cja
cja-tech-guyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I wouldn't backup a database like that.
Even in SQL Server Express you don't have the SQL Server Agent you can perform backups with T-SQL:
BACKUP DATABASE databasename TO DISK='drive:\fullpath\filename.bak'
You just need to script the backup command to a .sql file and then schedule it to run with SQLCMD utility in the Windows Schedule Tasks.
cja-tech-guyAuthor Commented:
I do not know how to write SQL scripts.  Please explain how to do this.

Thanks,

cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just copy and replicate the line I posted above for each database that you have in the SQL Server Express instance.
Example for 3 databases:
BACKUP DATABASE databasename1 TO DISK='drive:\fullpath\filename1.bak'
GO
BACKUP DATABASE databasename2 TO DISK='drive:\fullpath\filename2.bak'
GO
BACKUP DATABASE databasename3 TO DISK='drive:\fullpath\filename3.bak'
GO

Open in new window

You can do that in the SQL Server Management Studio by clicking in the "New Query" button and then save the file. After that go to Windows Scheduler Task utility and create a task that will run every night with the following commad:
SQLCMD -S Servername\SQLExpress -E -i BackupScript.sql
NOTE: The user that you set to run the task must have permission to perform database backups.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Salah Eddine ELMRABETTechnical Lead Manager (Owner)Commented:
Hi,

The folder C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA is the folder containing mdf file of your database it will some thing likes DB_Name.mdf if you are using CA Arcserve you will configure the backup path and for restore you will use the CA Arcserve to make the restore.

FOr more details about Backup and Restore options refer to:

Backing Up Microsoft SQL Server Databases

Restoring Microsoft SQL Server Databases

Best Regards.

Salah
cja-tech-guyAuthor Commented:
So If I only need to back up one database called Hunt_Vision then the SQL Script should be as follows?

1:BACKUP DATABASE Hunt_Vision TO DISK='C:\Hunt_Vision-Backup\Hunt_Vision.bak'
2:GO

thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know the CA Arcserve solution but if you can use it to backup the databases instead the database files then would be better so it can assure you that the backed up data is consistent.
Vitor MontalvãoMSSQL Senior EngineerCommented:
BACKUP DATABASE Hunt_Vision TO DISK='C:\Hunt_Vision-Backup\Hunt_Vision.bak'
Yes, that's how should be. By the way, the database is in simple recovery model?
Salah Eddine ELMRABETTechnical Lead Manager (Owner)Commented:
Hi,

do you want to backup the database manually or using the  CA Arcserve

Regards.

Salah
cja-tech-guyAuthor Commented:
Vitor
yes, the database is in "simple" recovery mode.  See attached screen shot of its properties to verify.

Thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, being in Simple Recovery model you won't need to perform Transaction Log backups.

NOTE: You didn't post any screen shot.
cja-tech-guyAuthor Commented:
here is the screen shot.
SQL-screenshot.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Confirms the Simple Recovery Model :)
cja-tech-guyAuthor Commented:
Vitor

Can you tell me if the settings in Task Scheduler are correct.  See attached screen shot.

thanks,
cja
Task-Scheduler-settings.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
On the General tab, should be "Run whether user is logged on or not" so it can run a backup even no one is connected to the server. Otherwise someone need to be there every day at 8:00PM, including weekends :)
In the Settings tab I would stop the task if it's running longer than 12h, so at 8:00AM the job should be stopped if still running, I won't believe that backup would take 12h to run, right?
Also, will test running the task manually to check for errors. Don't forget that the user you set for run the task need to have permission to perform a backup in the SQL Server instance.
cja-tech-guyAuthor Commented:
Vitor
Sorry, but when I go to "save" in SQL Server Management Studio it opens a browsing window.  Where do I save the SQL backup query to?  See attachment for what I see.

thanks,
cja
SQL-Query-Save-Window.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to save the file where you want to. In an existing folder or create a new one where you can store the file. Don't forget to use the same path for the SQLCMD so it can run the script.
cja-tech-guyAuthor Commented:
So if I save the file in the same location that the SQL command runs from it will work?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure. You can do it now for test but I recommend you to save all the scripts in a proper place so if in the future you move the server you don't forget the scripts.
cja-tech-guyAuthor Commented:
Vitor
I saved the query but when I tried to run it from the "execute" button In SQL Management Studio I got the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2'.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post the script
cja-tech-guyAuthor Commented:
here is a screen shot of the query and errors in SQl Management Studio
SQL-Query-Execute-error.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
Remove the line numbers. They aren't necessary in SQL Server.
cja-tech-guyAuthor Commented:
I was able to run it from SQl but not from Task Scheduler.  In Task Scheduler it just says "running" but nothing happened for 15 minutes.  When I run it from management Studio it takes about 2-3 minutes.  Any ideas?

thanks,
cja
cja-tech-guyAuthor Commented:
I got the task to run, but now it opens Management studio and wants me to click on "connect"
any ideas?

thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's strange. Try to add an output parameter for SQL Server send any message to a file:
SQLCMD .... -o C:\temp\BackupJob_log.txt
Next time you run again go and check what's in C:\temp\BackupJob_log.txt
cja-tech-guyAuthor Commented:
Do I add this C:\temp\BackupJob_log.txt at the end of the string below?  

SQLCMD -S Servername\SQLExpress -E -i BackupScript.sql

thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes.
SQLCMD -S Servername\SQLExpress -E -i BackupScript.sql -o C:\temp\BackupJob_log.txt

So all the output will be in the C:\temp\BackupJob_log.txt. You can the analyze the file and check for errors.
cja-tech-guyAuthor Commented:
I will try to get to this today or tomorrow.

Thanks,
cja
cja-tech-guyAuthor Commented:
Vitor

I ran the task and in brings up the SQl Manament Studio login, see attachment. It does not runt he backup and it also did not create a log.

thanks,

cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't attach nothing.
cja-tech-guyAuthor Commented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post how the task it's created. Looks like you're calling SSMS instead of SQLCMD.
cja-tech-guyAuthor Commented:
See attached pdf for the Task Scheduler properties.

thanks,

cja
task-scheduler-properies.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you are calling the default program associated to .sql files and in your case is SSMS.
You really need to put the command in the Program/Script field: SQLCMD
And the in the arguments: -S Servername\SQLExpress -E -i C:\Hunt_Vision-Backup\SQLQuery2.sql -o C:\Hunt_Vision-Backup\BackupJob_log.txt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cja-tech-guyAuthor Commented:
I made the changes and ran the task.  Attached is the log.

thanks,
cja
BackupJob-log.txt
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you replace Servername by the real Server Name?
cja-tech-guyAuthor Commented:
Do I put the path to the SQl command in the Program/Script field in the task?  Currently I have the following:

C:\Hunt_Vision-Backup\SQLQuery2.sql.  This is the folder that the SQL query is in.

thanks,

cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, so it can find the correct script.
cja-tech-guyAuthor Commented:
I still cannot get it to run.  It does not create the log file or the backup.  Attached is a pdf showing all the settings in the various screens of Task Scheduler.  Please let me know if any of them are incorrect.

thank,
cja
Task-Scheduler-screen-shots.pdf
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check the page 2. You still doing the same mistake. I will repeat what I said before:
Yes, you are calling the default program associated to .sql files and in your case is SSMS.
 You really need to put the command in the Program/Script field: SQLCMD
The part of arguments looks OK now but please say what you don't understand.
cja-tech-guyAuthor Commented:
I'm not following what should be in the Program/Script field.  Should it be SQLCMD or C:\Hunt_Vision-Backup\SQLQuery2.sql

I'm not understanding why the task says it is running but nothing happens.

thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should be SQLCMD . C:\Hunt_Vision-Backup\SQLQuery2.sql is already on the parameters field.
cja-tech-guyAuthor Commented:
I think it worked.  The backup file is there and below is what the log generated.  Do you think it worked?


Processed 380408 pages for database 'Hunt_Vision', file 'VisionHuntLive' on file 1.
Processed 6 pages for database 'Hunt_Vision', file 'VisionHuntLive_log' on file 1.
BACKUP DATABASE successfully processed 380414 pages in 57.871 seconds (51.355 MB/sec).


Thanks,
cja
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh, yes. It worked perfectly.
Good job :)
cja-tech-guyAuthor Commented:
Thank you very much for helping me with this.
cja-tech-guyAuthor Commented:
Thank you Vitor, I appreciate your patience with helping me understand how to set this up.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.