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