Re: [Bacula-users] Best way to perform a backup for Sql Server 2012
On Mar 5, 2013, at 10:19 AM, John Drescher wrote: On Tue, Mar 5, 2013 at 9:59 AM, Carlo Filippetto carlo.filippe...@gmail.com wrote: Hi all, as I write in the subject I need to backup Sql Server 2012, you have any idea on how to optimize the process and have a consistend DB I thought to make a dump script and backup it, but how? Any other ideas? Thank you. I would use the T-SQL backup command to backup the sql database to a file then have bacula backup that file. I have tried scripting dumps however it seems that the T-SQL BACKUP / RESTORE works better than the management studio scripting to .sql ( at least in my testing). What he said. I'm a big fan of dumping to text, backing up that text. To be sure, copy that text to another server, then load that DB up and see how it goes. I do that every day for every database I backup. -- Dan Langille - http://langille.org -- Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester Wave(TM): Endpoint Security, Q1 2013 and remains a good choice in the endpoint security space. For insight on selecting the right partner to tackle endpoint security challenges, access the full report. http://p.sf.net/sfu/symantec-dev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Best way to perform a backup for Sql Server 2012
I would use the T-SQL backup command to backup the sql database to a file then have bacula backup that file. I have tried scripting dumps however it seems that the T-SQL BACKUP / RESTORE works better than the management studio scripting to .sql ( at least in my testing). What he said. I'm a big fan of dumping to text, backing up that text. To be sure, copy that text to another server, then load that DB up and see how it goes. I do that every day for every database I backup. What I do is: . Full backup once a week . Diff backup once a day . Log backups hourly (done completely separately of backup) The full and diff backups are done via an SQL script that enumerates all databases and backs them all up. I don't have a way of excluding backups at this time. After each full backup runs: Restore a copy of master, model, and msdb to an alternate location then detach The last step is done because you need master, model, and msdb in place to bootstrap mssql so having these present is really useful. I can post scripts but would need to redact them first :) James -- Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester Wave(TM): Endpoint Security, Q1 2013 and remains a good choice in the endpoint security space. For insight on selecting the right partner to tackle endpoint security challenges, access the full report. http://p.sf.net/sfu/symantec-dev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Best way to perform a backup for Sql Server 2012
Also one very important thing to not about mssql server if you start doing diff and inc backups - diff and inc backups are based on the most recent normal backup taken. If someone does a backup external to your automated Bacula backups, future diff and inc backups are based from that one, not the one Bacula did, and if that backup gets put somewhere that Bacula doesn't back up, or deleted, you will be unable to use your future diff and inc backups to restore from. If you want to do a backup outside of Bacula, use the WITH COPY_ONLY option to ensure that the backup sequence isn't interrupted. James -Original Message- From: James Harper [mailto:james.har...@bendigoit.com.au] Sent: Thursday, 7 March 2013 5:54 PM To: Dan Langille; Carlo Filippetto Cc: bacula-users Subject: Re: [Bacula-users] Best way to perform a backup for Sql Server 2012 I would use the T-SQL backup command to backup the sql database to a file then have bacula backup that file. I have tried scripting dumps however it seems that the T-SQL BACKUP / RESTORE works better than the management studio scripting to .sql ( at least in my testing). What he said. I'm a big fan of dumping to text, backing up that text. To be sure, copy that text to another server, then load that DB up and see how it goes. I do that every day for every database I backup. What I do is: . Full backup once a week . Diff backup once a day . Log backups hourly (done completely separately of backup) The full and diff backups are done via an SQL script that enumerates all databases and backs them all up. I don't have a way of excluding backups at this time. After each full backup runs: Restore a copy of master, model, and msdb to an alternate location then detach The last step is done because you need master, model, and msdb in place to bootstrap mssql so having these present is really useful. I can post scripts but would need to redact them first :) James -- Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester Wave(TM): Endpoint Security, Q1 2013 and remains a good choice in the endpoint security space. For insight on selecting the right partner to tackle endpoint security challenges, access the full report. http://p.sf.net/sfu/symantec-dev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester Wave(TM): Endpoint Security, Q1 2013 and remains a good choice in the endpoint security space. For insight on selecting the right partner to tackle endpoint security challenges, access the full report. http://p.sf.net/sfu/symantec-dev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Best way to perform a backup for Sql Server 2012
Hi all, as I write in the subject I need to backup Sql Server 2012, you have any idea on how to optimize the process and have a consistend DB I thought to make a dump script and backup it, but how? Any other ideas? Thank you. --- Carlo -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_feb ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Best way to perform a backup for Sql Server 2012
On Tue, Mar 5, 2013 at 9:59 AM, Carlo Filippetto carlo.filippe...@gmail.com wrote: Hi all, as I write in the subject I need to backup Sql Server 2012, you have any idea on how to optimize the process and have a consistend DB I thought to make a dump script and backup it, but how? Any other ideas? Thank you. I would use the T-SQL backup command to backup the sql database to a file then have bacula backup that file. I have tried scripting dumps however it seems that the T-SQL BACKUP / RESTORE works better than the management studio scripting to .sql ( at least in my testing). John -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_feb ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Best way to perform a backup for Sql Server 2012
On Tue, 5 Mar 2013 15:59:58 +0100 Carlo Filippetto carlo.filippe...@gmail.com wrote: as I write in the subject I need to backup Sql Server 2012, you have any idea on how to optimize the process and have a consistend DB I thought to make a dump script and backup it, but how? Any other ideas? Microsoft SQL Server has the VSS writer so you just have to enable VSS for your backup job and then copy the database files (not transaction log files though!) as is -- when the Bacula file daemon will enable VSS on the requested volume(s), MSSQL server will flush any pending changes to its database files and then the file daemon will copy them from the shadow partition while they have consistent state. You can see that VSS writer in the output of the vssadmin list writers command run from the command prompt. Another approach is to script running backups of the database files, perform this script in the [Client] Run Before Job, and then back up the resulting backup files. -- Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_d2d_feb ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
