How to Automate SQL Server Express backups
Vložit
- čas přidán 11. 09. 2024
- Microsoft SQL Server Express editions doesn't have any schedule either jobs for maintenance plans because the SQL Server Agent component is not included in these editions. So, you have to take a different approach to backup your databases when you use these Express editions.
In this video tutorial you are going to learn how to Automate SQL Server express backups using the sql server stored procedure, task scheduler and batch file.
Download: www.itcoh.com/...
Source: support.microso...
Hey Sandro, That's wonderful. Thanks for watching the video, keep watching and keep leaning :D!
This tutorial is very clear and helpful, there are configuration files that you set and shared, success is always for you
Hey Anish, thanks for watching the video See Ya :)
Hey Marie, yes this also works on SQL express 2008 and Windows Server 2008 R2. Let me now if you have any problem See Ya :)
Hi koshaugh, thanks for watching the video See Ya :)
Hey Gowda, thanks for watching the video.Well I am sure if you will search on Google you can find so many tutorials on how to create a tables and database in sql server 2012 :)
COOL ... awesome it work !! Thank u very much !!
before I saw this video I always failed to make a batch file....thanks again buddy
Hey Moe, Yes this works on Windows Xp and SQL express 2008 if you have any problem if you have any problem See Ya :)
Hey Mag, Thanks for watching the video did you seen the full video. The script automatically deletes the old backup once it reaches the defined date. please let me know if you have any problem See Ya :)
Just press Ctr + Shift + F after selecting the parameters for your back up in Management Studio and save the script to a sql file in your documents folder. Then run sqlcmd from the Windows Scheduler and call your sql file. It's that simple.
Hey sachin how can i change the location of my back up file. i dont want to store the backed up file in c drive.
running well... really thank you, Sachin
well done video. Wish it was better quality HD video to see better but I think you covered it all.
Thanks!
Hey Felix, good news your problem is resolved.
The problem was with the 12AM-backup and 12AM-backup file.
Default instance is: . or computer name
Named Instance is: .\SQLEXPRESS or computer name\SQLEXPRESS
FOR YOU:- sqlcmd -S . -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
FOR ME :- sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
See Ya enjoy :)
Thank you Sachin, well explained.
Thanks. Excellent tutorial. Helped me a lot.
cool water you're welcome :)
Gracias,
Esta muy bueno el tutorial. lo logre implementar en mi trabajo.
Hey Felix, thanks for watching our videos well I don't know why its not working on your system I am using this everywhere without any problem. I can check it for you if you give me access to your system using teamviewer. See Ya :)
HI there,
= If You Like This Video, Give It a Thumbs Up :)
= Subscribe to receive email updates when we post new videos ;) ,
= Share it with your technical IT friends "_" ,
= if your facing? any technical problem or have any suggestion post your comment here or catch me on google+ or Gtalk :D!
Very nice and valuable video.. Thanks!!
your welcome
Really Helpful video nice job.
Sachin :) This helped for me. Big Thanks
Hi Sachin, It is a clear demo. Also I am glad, that scripts works for me. I even tried to take the differential backup instead of daily taking the complete backup with a small change of 'D' instead of 'F' in the 12 Am batch file. But I am getting a new backup file with less size. What I am looking for is a way to merge the differences with existing backup. Is it possible with any script tweaks.
Well i have never done this but i think you can do this by creating a script from sql server shrink and then using a T- SQL command. :)
Thanks, very helpful
hai sachin i followed your video, the script run successfully but backup folder is empty pls help me
Great video. Thank you!
Hi Sachin Sammy,
Can we change the back up method from "append" to "overwrite"? Please let me know the place to set it up? Many thanks!
what if I login to sql server using sql login credential & not using windows login ? is there any issue with that? tnx for this tutorial.
it worked for my SERVER that is not part of the Domain, but did not work for MY SERVER that is part of the domain, task scheduler does not giving error, but not backing up, any help is appreciated. please.
Hey Solomon, Thanks for watching the video, this error comes when don't change the computer name properly in the DEL12PM.xml file before importing it into task scheduler. I will recommend you to bubble check the DEL12PM.xml for the changes you made in the computer name field. if you still having problem you give me the access to your system using teamviewer and I will solved it for you. My Skype id: Sachin.Samy See Ya :)
Thank you for your help I done all but when I try to import the task to the task scheduler it gives me error " an error has occurred for task Del12pm. error message: the specified account name is not valid
Hi Sachin, Your video really helped me, is there any way how to shrink automate log databse in sql server express? do you know?
Hi sachin I am having some issues with creating the backup, can you please help me out
very clear. tks
Matthew Lee Thanks :)
Can we run this on Windows xp?I have xp sp3 and sql express 2008
Excelente muchas gracias.
+naturalforever thanks for watching don't forget to like and subscribe
nice job Samy. Any thoughts on modifying the batch file for delete only those files beyond the latest, say, 5 most recent?
Hey Richard, check this out but use this it at your own risk. stackoverflow.com/questions/13367746/batch-file-that-keeps-the-7-latest-files-in-a-folder
Geeeenioooous Bhai.....
+Prakash Dhimmer Thanks!
Very helpful :)
your welcome
Working on sqlexpress server 2012 but not working kon sqlexpress server 2008R2
Then how to get the email alerts for backup failure or success
hey sachin thanks for the video shared. i am unble to import xml file in scheduler. plz help. error is -- account name is invalid Help pliss
Thanks Samy, nice video, i try this on SQL Server 2012 installed on Win 7, but not functioning is there any solution pls advise, thanks again
very helpful :D
Hi Sachin,
first of all I would like to thank you for such a nice tutorial; appreciate your effort
I am on test server which is windows 7 with sql server 2012; can you please confirm if this back up step would work with this configuration ?
if it should work with this then my question is as below
I exactly followed your steps
to test back up I've changed my pc's time to 12 am/ 12 pm but I cant see back up
I tried to run scheduler without changing time as well it didn't work so changed time
is there is anything I am missing?
thannk you so much for your time.
An techv it looks like that you're doing something wrong.
see what is the error you are getting on the SQL script, just add ( pause ) at the end of the SQL script,
Example:-
===================>
sqlcmd -S .\SQLEXPRESS -U sa -P PA$$W0RD -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
pause
===================>
let me know if you have any problem you can also give me remote access of your system using teamviewer I can check what is the issue your having.
skype my skype id Sachin.Samy :)
*****
I sent you skype request;
yesterday I imported task from right hand side "Import task" today I followed exact way the you did that is right click on task manager and import task
now Its showing some different error " specified account name is not valid" which is strange for me
Hi Sachin, great video and work on this. I'm using Express 2014 and am having trouble with this, do you know if it works on the 2014 edition?
Michael Michelakis yes this also works on 2014 express
***** if your still having issue can you give me the teamviewer so that i can check and troubleshoot the issue. you can contact me on skype my skype id Sachin.Samy :)
Hi, i follow your steps but when i try to run them manually nothings happens, any idea of what is happens? im using SQL 2008R2
Thanks
+Luis Amador looks like a complicated issue you can take our professional help costing $50 USD contact me on Skype ID: Sachin.Samy
Hello Sachin,
i got a error the format of the task is not valid the following error was reported (1,56) plz help
sure I can help you contact me on my Skype ID: Sachin.Samy
Hi Sachin i have successfully did automate to SQL Express 2005 using this process. But this is not working to SQL Express 2014 ? Please advise
From Peter
Hey man! Did you find out the problem? Im currently on the same
Hi, is this will work on SQL Express 2014 ? where can i download the zip
files ?
From the below link you can download the the file
www.itcoh.com/p/download.html
Yes it should work on 2014 edition
hi can u tell me how to create tables and database in sql server2012 i am a beginner..plz help me..
Hi Sachin, very nice demo. I have another question is how to change to network driver? I modified @backuplocation to W: or \\fileserver\back\ w/o luck. Please help. thanks in advance.
+oliver lu check out this msdn article msdn.microsoft.com/en-us/library/ms179313.aspx
Hlo sir i have question regarding ssl certificate,i want to host my site i.e https for practice purpose,where can i find the demo version of certificate,sir please provide me the link if it is possible..
sujan dhakal for demo purposes you can use self signed ssl certificate check out our this video czcams.com/video/BYwOMuBDhPU/video.html
Hi do we really need to use the XML file or we can just create a basic scheduled task? Will it make a difference?
Hey felix, yes you can use basic scheduled task there is no difference. let me know if you have any problem :)
***** for some the delete batch file script work but not the backup batch :( any other suggestions?
Well you have to check the path of the folder in the delete batch file. if you have changed the path of the backup location you have to put the same path here. let me know if you have any problem :)
very useful, need help how to change location of back up please?
Help me, Error:
The Executer permission was denied on the object sp_BackupDatabases database 'master' .dbo
check that you have full permission to take backups
Hi,
I have do the same thing whatever you suggested but when i run the scheduler manually not any thing happening. I saw when you run the scheduler manually then something run in CMD. but same thing not on my server.
pankaj kumar right click on edit the batch file (12AM-backup) check your computer's name and SQl server instance name. In the batch file the computer name is represented by a single dot and the Sequel server instance name is SQLExpress
*****
It showing in batchfile is below.
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
Is it correct? or pls give me ur contact no.
+Sachin Samy Same thing is happening to me... I click run and nothing happens... I am on a Win 10 machine.. would that change anything?
Hi i cannot get the bat file to execute properly - i don't see the DOS screen popup and no backup files are generated. Logged on as an admin running test:
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
I verified everything on your video - let me know what you think
Changed above to this:
sqlcmd -S servername\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
Hey Seth, well this looks correct you might have some other problem. Do you have multiple instances of SQL running on the same server ? Check the SQLCMD is working when you run it on the Command Prompt.
You also try the this:-
cmd
cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqlcmd -S servername\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
If you still have a problem you give remote access using TeamViewer so that i can check the problem for you My Skype ID: Sachin.Samy
See ya Bye :)
***** The folder c:\program files\microsoft sql server has 3 directories in it. 80,90,100 - 80 has the tools in it. I do have sqlcmd running when i execute the command by itself it gives me another line with 1> so i know it works. When i run the sqlcmd -S servername\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
This just gives me a new line - nothing backed up, no dos screens
I browsed the web some more and found the sql/query change in pipes but no change after restarting services
Hey Seth, Can you give remote access using TeamViewer so that i can check the problem for you My Skype ID: Sachin.Samy .
awesome
Hello,
thank you for this cool tutorial. I want to save the backup on a network location. Is there a way to do so ?
hey Ben, thanks for watching the video. see this if you want to take backup on a network location support.microsoft.com/kb/207187
ouukkii bro thank you very much :)
and its SQL server 2008 standard edition
Can i use this backup features on server 2008rs with sql 2014 express?
yes you can use it on server 2008r2
Sachin, i have installed on a server 2012 r2 with sql 2012 express, script seems to run fine, but zero files turn up in backup folder. i seer others with same problem, is this a simple fix i can do without you logging in remotely as server is a production server and not mine, owner will not allow remote access :(
Scott Inwood
Most probably the instance name of the sequel server is different in your server.
Instead of the SQLEXPRESS you have to type of server instance name over there.
See what is the error you are getting on the SQL script, just add ( pause ) at the end of the SQL script,
Example:-
===================>
sqlcmd -S .\SQLEXPRESS -U sa -P PA$$W0RD -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups-All\Backup\12AM\', @backupType='F'"
pause
===================>
If you find these tutorials useful
Please, consider making a small donation. Even a small amount of money can help us continuing our work Thanks!
www.itzoz.com/p/donation.html
***** Which file do i change the instance name in, mine is LFSRV\SQLLF my server name is LFSRV.
I have changed the XML files Author and UserId to include LFSRV\Administrator. Batch file is unchanged. Script is unchanged and executes successfully. Bottom line under script reads Connected|LFSRV\SQLLF (11.0 SP2)|LFSRV\Administrator|Masters.
The error when i run script is along the lines of..
Sqlcmd: Error: MS SQL Server Native Client 11.0 : SQL Server Network Interfaces:Error locating Server/Instance Specified [xFFFFFFFF]
Login timeout expired, Check instance name etc... If i get this working i will be happy to make an donation.
Scott Inwood Sachin, I just worked out, changed the Batch File from .\SQLEXPRESS to .\MYINSTANCENAME and all working,
Will send you a donation now,
Regards
Scott
Scott Inwood That's Wonderful Thanks :)
Hello brother please teach how to sql mirroring ??
helpful
Thank you watching the video
Good Tutorial, poor sound and lack of resolution, but i managed to follow it none the less.
I initially had a problem with this not working, in that the batch file would appear to run but there would be no backup's in the folder.
I discovered my SQL instance was not .\SQLEXPRESS, in my case I was using the default MSSQLSERVER as the instance.
As this is the default instance you do not need to use the instance name, but rather use the machine name instead. this is the code I used, in my case the machine name was ATSERVER:-
sqlcmd -S ATSERVER -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\12AM\', @backupType='F'"
I also changed the backup location to the D:\ drive. Hope this helps anyone else facing this issue.
Thanks man, faced the same issue and your advice helped.........Thanks again
Hi Sachin,
Thanks for sharing this video, its very useful. I am getting error message while importing DEL12PM and DEL12AM to task scheuler. {erro message: The format of the task is not valid. The following error was reported (2,8); version1.3}
Hari Kishore Dirisala have you properly added the computer and username in the xml file ?
***** Yes i did, Tried couple of times but same message.
hari kishore Dirisala Can you give me the teamviewer so that i can check and troubleshoot the issue. you can contact me on skype my skype id Sachin.Samy :)
very nice video but instead of writing scripts how to back up automatically(monthly,yearly) using server agent jobs
I can help you in this issue you can contact me on my Skype ID sachin.samy
can u give me ur email ID or mobile no (it is asking in skype) but sorry..
hey sachin thanks for the video shared. i am unble to import xml file in scheduler. plz help. error is -- account name is invalid
my friend, I apologize, you arranged it, I also have the same error, please help
You guys have to import it as an a local admin, cannot be a domain account. Let me know if you still have this problemo..
Hi samy, Is it work for ms sql because you have explained about ms sql express. so could you please help me on this.
Karthik Tumalapally yes you can also use this for ms sql server, the configuration process is same as shown in the video, you just have to use the default instance name instead of the named instance. let me know if you have any problem!
i have done according to your guidelines,but its not taking the backup giving error like this
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFF
FF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
C:\Backups-All\Automate\Batch-File>
i have given instance name in respective file but its giving above error, please help me on this.
Karthik Tumalapally Can you give me the teamviewer so that i can check and troubleshoot the issue. you can contact me on skype. My Skype id Sachin.Samy :)
*****
At what time you will be available.....
Karthik Tumalapally i am available at 9:30pm India Time Zone (UTC+05:30)
Hi Sachin, thanks for your upload. its wonderful. but am facing some challenges in applying the procedure. Please can you help?
Bless Jasseh what is the issue you're having
someone can reply to this message with che query code?
Download from here
www.itzoz.com/p/download.html?m=1
the video is helpful but quality is poor
Hey Imad, what is the error your getting ? I can't give you any solution until I don't know what is the error your getting. if you can give me the access to your system using teamviewer I will check it out and solved it for you. My Skype id: Sachin.Samy See Ya :)
Hey Felix, no problem at all my email is petercrys1@gmail.com