Post reply

Unauthorised Restore of BAK Files

  • June 5, 2008 at 3:39 am

    #70504

    Hi,

    We have a Database, which we have tied down with good scurity and stored procedures.

    However, a system administrator can take a back up file and restore the database to a new server and see the data.

    Can we take any action to avoid this happening?

    Thank you in advance for any suggestions.

    Colin

  • June 5, 2008 at 1:25 pm

    #825062

    Make use of PASSWORD clause of BACKUP DATBASE command. See books online for syntax and its usage.

    Manu

  • June 5, 2008 at 1:32 pm

    #825074

    You'll also have to secure the backup file itself. The file is not encrypted, and if the system admin can move the backup file to a local system (desktop) and has access to something like SQL Server 2005 Developer Edition (only costs $50), he could restore the database there and (as SA on the local system) still have access to all the data.

    😎

  • June 6, 2008 at 2:34 am

    #825327

    Hi,

  • June 6, 2008 at 2:38 am

    #825328

    hi,

    Thank you, very helpful. I have created a password protected bak and restored it. However, I need toadd a date to the file name, as you get when using maintenance plans, I have tried the following script, but it does not work.

    USE MASTER

    DECLARE @BackUpPath varchar(100)

    SET @BackUpPath = 'D:\BACKUP\DatabaseName' + convert(varchar(40), GetDate(), 126) + '.bak'

    BACKUP DATABASE DatabaseName TO DISK = @BackUpPath

    WITH PASSWORD = 'password'

    Any thoughts?

    I note the comments about sa being able to restore.

    Colin

  • June 6, 2008 at 7:32 am

    #825427

    Here is some code from my backup process that provides a datetime stamp for my transaction log backup files.

    declare @CurrDate datetime;

    set @CurrDate = getdate();

    select N'_' +

    convert(nvarchar, @CurrDate, 112) +

    N'_' +

    rtrim(replace(convert(nchar(8), getdate(), 108),':',''));

    😎

  • June 6, 2008 at 8:54 am

    #825513

    Hi,

    Thank you very much, I now seem to have something working. Will continue to refine and consider the sa issue.

    Thanks

    Colin

Viewing 7 posts - 1 through 6 (of 6 total)

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