Client Data Backup

Question

Client Data Backup

hansraj pankhania

SSC Journeyman

Points: 77
More actions
December 7, 2005 at 12:50 pm

#111414

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 site, I get no response 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 copy of the client sql server and the client are never syncronised.
Can anyone help to ease the backup problems.
Thanks to all in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

Any error messages in the sql server log or NT event log?

File permissions on the root of c:\ - does the account sql server is running under have permission to write to that loacation.

For synchroizing sps etc hava look at something like sqlcompare from redgate, or a version control system where you can store all the sps as seperate files and check in changes as required.

MIke

Anurag Nayar Mr or Mrs. 500 Points: 515 More actions · Answer 2

Instead of the stored proc mention can you try creating a Maintenance Plan for taking backups at a scheduled time and see if it works. This will confirm if there is any problem with stored proc and you may use the maintemance plan also for your requirement and maintenance plan is a better solution since you can add many other maintenance activities in the plan apart from backups.

hansraj pankhania SSC Journeyman Points: 77 More actions · Answer 3

Hi,

Thanks for the replies.

I have been in contact with the IT department who administer the SQL Server holding the database, only to find that my script will not work due to restrictions imposed by the hospital and data protection.

I am liasing with them to find a possible solution to this backup senario.