We help IT Professionals succeed at work.

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
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I do not know how to write SQL scripts.  Please explain how to do this.

Thanks,

cja
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
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

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

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

Thanks,
cja
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok, being in Simple Recovery model you won't need to perform Transaction Log backups.

NOTE: You didn't post any screen shot.

Author

Commented:
here is the screen shot.
SQL-screenshot.pdf
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok. Confirms the Simple Recovery Model :)

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

Commented:
So if I save the file in the same location that the SQL command runs from it will work?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Please post the script

Author

Commented:
here is a screen shot of the query and errors in SQl Management Studio
SQL-Query-Execute-error.pdf
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Remove the line numbers. They aren't necessary in SQL Server.

Author

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

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I will try to get to this today or tomorrow.

Thanks,
cja

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
You didn't attach nothing.

Author

Commented:
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Please post how the task it's created. Looks like you're calling SSMS instead of SQLCMD.

Author

Commented:
See attached pdf for the Task Scheduler properties.

thanks,

cja
task-scheduler-properies.pdf
IT Engineer
Distinguished Expert 2017
Commented:
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

Author

Commented:
I made the changes and ran the task.  Attached is the log.

thanks,
cja
BackupJob-log.txt
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did you replace Servername by the real Server Name?

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, so it can find the correct script.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Should be SQLCMD . C:\Hunt_Vision-Backup\SQLQuery2.sql is already on the parameters field.

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Oh, yes. It worked perfectly.
Good job :)

Author

Commented:
Thank you very much for helping me with this.

Author

Commented:
Thank you Vitor, I appreciate your patience with helping me understand how to set this up.