John
Let's start with what you are doing with batch files...
You are creating another point of failure. All the DBAs I know (I happen to work with an ex-Microsoft DBA) use maintenance plans or at least SQL jobs to run backups. Let the engine do the work for you so you aren't relying on a computer's scheduled tasks to run backups.
Jobs have a distinct advantage over batch files in that they can fire off an email to the person in charge should the job fail. And you will have a job history so you can watch for anything that might need adjusting.
I hope I've convinced you to use maintenance plans.
Log backups...If you are using Full mode as opposed to Simple mode for your database (check th eDB properties) you need to back up the log. That not only keeps the log trimmed but it also gives you a way to restore more data than if you do a Differential backup once a day. With hourly log backups, you will lose only one hour or less of transactions should your database need restoring.
And log backups generally run really fast and don't interfer with data entry so your users won't see a slowdown.
But if you are using the Simple mode you can't backup the log. Which one you use depends on how critical the data is. If it's only a reporting db that gets its data from a central database Simple would be just fine.
System database backups are what will save your bacon should the server's hard drive crash. You can't restore a database without the metadata stored in the system databases. These are the Master db, Model db and MSDB db. Usually, a full backup once a week is sufficient.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "john_coote" <john_coote@...> wrote:
>
> Bill thanks, you've pointed me to the solution
> "First, SQL scripts don't run themselves. "
> On the original server where everything was working smoothly I was firing the script in a batch file with the line:
> sqlcmd -i "C:\Backup\FullBU.sql"
>
> Once i created a corresponding batch file on the new PC and fired the script with the preceding "sqlcmd -i ..." it all works fine.
>
>
> Thanks so much for the pointer.
>
> I can now go ahead and create batch files to cover full weekly and daily differential backups.
>
> I've never done a log backup, can you explain a bit more about this?
>
> Also, I've never done a system database backup, could you explain this also
>
>
>
> Many thanks to Bill and John V for your help and input
>
> Cheers
> John C
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > John
> >
> >
> >
> > Sorry, I didn't see your original post.
> >
> >
> >
> > First, SQL scripts don't run themselves. Even stored procedures need to be
> > executed.
> >
> >
> >
> > Here is what I do. I create 3 maintenance plans to do a full back up every
> > week, a diff every day and a log back up every hour (or more often if the
> > database is heavily used.
> >
> >
> >
> > That will create 3 jobs to run them and you don't need to run scripts or
> > anything else.
> >
> >
> >
> > Don't forget to back up the system databases as well.
> >
> >
> >
> > Regards,
> >
> > Bill
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of john_coote
> > Sent: Wednesday, June 29, 2011 4:47 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Repost: Problem backing up SQL Server database using
> > backup.sql file
> >
> >
> >
> >
> >
> > Dear all,
> > I'm reposting this as it seemed to become associated with replies which were not
> > on the topic.
> > If this is not the best place to ask, can anyone direct me to an appropriate
> > forum for SQL Server questions?
> >
> > Many Thanks,
> > John
> >
> > I'm having some trouble running a backup running a backup using a query
> > contained withn a .sql file.
> > I need to resolve this issue so I can automate backups for my client.
> > Any advice would be gladly appreciated.
> > PC is Windows 7 Professional, 64 Bit
> > SQL Server Express Edition with Advanced Services (2008 R2)
> >
> > I have two scripts (below) saved as FULLBU.sql and DIFFBU.sql. Neither are
> > working how I would expect. scripts which are identical except for database name
> > and file location are working perfectly on another server.
> >
> > <<<FULL DATABASE BACKUP>>>
> > BACKUP DATABASE [ASEEDA] TO DISK = N'C:\Program Files (x86)\Microsoft SQL
> > Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ASEEDA.bak' WITH NOFORMAT, NOINIT,
> > NAME = N'ASEEDA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> > GO
> >
> > AND
> >
> > <<<DIFFERENTIAL DATABASE BACKUP>>>
> > BACKUP DATABASE [ASEEDA] TO DISK = N'C:\Program Files (x86)\Microsoft SQL
> > Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ASEEDA.bak' WITH DIFFERENTIAL ,
> > NOFORMAT, NOINIT, NAME = N'ASEEDA-Differential Database Backup', SKIP,
> > NOREWIND, NOUNLOAD, STATS = 10
> > GO
> >
> > When I execute either file with SQL Server Management Studio closed; no backup
> > happens, SQL Server Management Studio opens, but does not connect to any
> > objects.
> >
> > When I execute either file with SQL Server Management Studio open; the text of
> > the script file opens in the Query window but does not execute. If I click on
> > Execute, the backup runs as expected. This makes me think that there's nothing
> > wrong with the query, but it's somehow failing to connect. I'm stumped.
> >
> > From SQL Server Configuration Manager, the running services are;
> > SQL Server and
> > SQL Server Browser
> > (on the other computer where these .sql files do execute correctly there is one
> > other service running, "SQL Full-text Filter Daemon Launcher"
> >
> > This is the information from SQL Server Management Studio - About:
> >
> > Microsoft SQL Server Management Studio 10.50.1600.1
> > Microsoft Data Access Components (MDAC) 6.1.7601.17514
> > Microsoft MSXML 3.0 4.0 5.0 6.0
> > Microsoft Internet Explorer 8.0.7601.17514
> > Microsoft .NET Framework 2.0.50727.5446
> > Operating System 6.1.7601
> >
> > Thanks
> > John
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Tidak ada komentar:
Posting Komentar