SQL Server backup best practices

Last Post 09 Nov 2012 08:30 AM by smosalak. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Andrew
New Member
New Member

--
07 Oct 2012 06:38 AM
Hello guys,
I've spent a lot of time to choose a good solution for reliable backups on SQL server, unfortunately I was often many problems (for example due a SQL Agent service broken).

Obviously the best choice is a good software with dedicated agent (same as Symantec backup or like it), but when isn't possible purchase these software?
I would like to know your feedback or tips for make a best backup policy on SQL Server

thanks very much
bye
Andrea
rm
New Member
New Member

--
07 Oct 2012 10:36 AM
What kind of problem you have in sql agent? We backup hundreds of servers in sql agent and work fine.
gunneyk
New Member
New Member

--
07 Oct 2012 03:12 PM
I agree with rm. If you can't rely on SQL Agent then you have some fundamental problems.
peter
New Member
New Member

--
23 Oct 2012 02:40 AM
You may refer these link to solve your query regarding SQL server for fundamental problems

http://msdn.microsoft.com/en-us/lib....105).aspx

and if your problem is still there u can try SQL BACK-UP related tools, there are many tools available in market
trial version of these tools are free of cost if you not able to get them i will suggest you for the same



DanGeek
New Member
New Member

--
23 Oct 2012 03:29 AM
You may want to go easy and harder ways. Harder one is to use scheduled batch script. Easy is to use software, like mentioned before.
Edit and then schedule this script, so you can backup all existing databases:
 
    @ECHO OFF
    SETLOCAL

    REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
    FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

    REM Build a list of databases to backup
    SET DBList=%SystemDrive%SQLDBList.txt
    SqlCmd -E -S YourSqlServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,'model’,'msdb’,'tempdb’)” > “%DBList%”

    REM Backup each database, prepending the date to the filename
    FOR /F “tokens=*” %%I IN (%DBList%) DO (
    ECHO Backing up database: %%I
    SqlCmd -E -S YourSqlServer -Q “BACKUP DATABASE [%%I] TO Disk=’F:Backup%NowDate%_%%I.bak’”
    ECHO.
    )

    REM Clean up the temp file
    IF EXIST “%DBList%” DEL /F /Q “%DBList%”

    ENDLOCAL


Note that you can mount remote location with
net use 
command to have a persistent drive letter.


Easy way is to use software that will automate backup process and add some features. The only software I used was Backup SQL Server Solution from EaseUs and Handy Backup Small Server. Last one is cheaper and easier to use (you can download fully functional trial and decide what plugins do you need).

I use batch script for my server and it is enough for me, but sometimes I wish my backups protected with encryption.
peter
New Member
New Member

--
25 Oct 2012 09:32 PM
If u want to avoid coding n all u may use Lepide SQL Storage Manager for monitoring your sql server performance
and for SQL server backup and restore u may use this link ,i think this is best suited for your query

http://www.restoresqldatabase.org/[b] [/b]
DanGeek
New Member
New Member

--
26 Oct 2012 12:13 AM
Maybe better Handy Backup that is cheaper and more functional?
smosalak
New Member
New Member

--
09 Nov 2012 08:30 AM
Hi Andrew, in the past ten years working with SQL Server never had an issue with SQL Server Agent not running a job. Can you please explain in detail when you had an issue with SQL Server Agent. If the SQL Server Agent is not running the only alternative would be to schedule jobs thru Windows Scheduler.


Acceptable Use Policy
---