December 7, 2005 at 12:47 pm
Hi
I have recently undertaken a project at my local NHS Trust hospital covering 3 sites. The application in question was developed using Microsoft access project and located at one site. The data held on a sql server 2000 located and administered by the IT department at another location.
As this was my first project it worked fine no problems what so ever, apart from the backup.
In my office, with a default installation of sql server 2000, my stored procedure below
Create Procedure dbo.sp_SQLDataBackup
AS
DECLARE @day_of_week VARCHAR(15),
@hr VARCHAR(2),
@min VARCHAR(2),
@db_location_string VARCHAR(128),
@database_name VARCHAR(128)
SET @day_of_week=DATENAME(dw, GETDATE())
SET @hr=DATENAME(hh, GETDATE())
SET @min-2=DATENAME(mi, GETDATE())
SET @database_name='cbit_data'
SET @db_location_string='C:\Cbit_Client' + @database_name + '_' + @day_of_week + @hr + @min-2 + '.bak'
BACKUP DATABASE @database_name TO DISK = @db_location_string
GO
This works fine to create the backup file. But when I run it on client site, I get no response, no activity or no files produced.
Can anyone explain why this is, or if they can come up with a better solution.
At the moment, at the client site i'm having having to export all sql data tables to Microsoft Access (mdb) and to import them back into my sql server is becoming a pain. Also any changes to stored procedures or views made on client site i'm having to either remember what i've done or write it out. Thus my local copy of the client sql data, views, stored procedures and that of the client are never syncronised.
Can anyone help to ease the backup problems.
Thanks to all in advance
December 8, 2005 at 2:47 pm
I've got a couple of things here:
1. It looks like you are attempting to place the backup files in the root of C:\? If so, I would suggest you move them elsewhere. Many database servers have small capacity C:\ drives as they are only meant to store the OS. For starters, I would change your script to write the backups to a different drive letter. This may be a permissions issue. The account you are using to perform the backups may not have access to write to the root of C:\.
2. Have you looked at the SQL Server or Windows application logs to see if the failed backups are leaving you any log entries? What happens if you take a manual backup through Enterprise Manager?
3. Have you thought of using Maintenace plans for your client databases?
December 9, 2005 at 10:24 am
another thing..."at the client site i'm having having to export all sql data tables to Microsoft Access (mdb) and to import them back into my sql server is becoming a pain"...why not just a backup file of client database restored over your development one ?!?! And if you're concerned about the restore wiping out your development changes, then have 2 DBs on your dev. server - one to restore client DB on and one for dev. purposes...that way you can import only the changes you want to test from your restored clientDB onto your devDB....!!!!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply