MSSQLExpress 2012 backup

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.

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

Thanks,

cja

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

Select allOpen 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.

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

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

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.

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?

Hi,

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

Regards.

Salah

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

Thanks,
cja

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

NOTE: You didn't post any screen shot.

here is the screen shot.
SQL-screenshot.pdf

Ok. Confirms the Simple Recovery Model :)

Vitor

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

thanks,
cja
Task-Scheduler-settings.pdf

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.

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

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.

So if I save the file in the same location that the SQL command runs from it will work?

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.

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'.

Please post the script

here is a screen shot of the query and errors in SQl Management Studio
SQL-Query-Execute-error.pdf

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

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

I got the task to run, but now it opens Management studio and wants me to click on "connect"
any ideas?

thanks,
cja

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

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

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.

I will try to get to this today or tomorrow.

Thanks,
cja

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

You didn't attach nothing.

sorry
sql-login.pdf

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

See attached pdf for the Task Scheduler properties.

thanks,

cja
task-scheduler-properies.pdf

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

thanks,
cja
BackupJob-log.txt

Did you replace Servername by the real Server Name?

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

Yes, so it can find the correct script.

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

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.

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

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

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

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

Thank you very much for helping me with this.

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