Post reply

Maintanence Plan creates .BAK in the wrong folder

  • May 28, 2008 at 9:38 pm

    #186157

    Hello,

    The problem is that I have specifed to create the .BAK in c:\DBBackups and not the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup. Why is SQL Server keep putting the .BAK there when I have told it to create the file some where else? Even the T-SQL does not say to place the file in the default location?

    EXECUTE master.dbo.xp_create_subdir N'C:\DBBackup\PBFamily'

    GO

    BACKUP DATABASE [PBFamily] TO DISK = N'C:\DBBackup\PBFamily\PBFamily_backup_200805282036.bak' WITH NOFORMAT, NOINIT, NAME = N'PBFamily_backup_20080528203610', SKIP, REWIND, NOUNLOAD, STATS = 10

    GO

    declare @backupSetId as int

    select @backupSetId = position from msdb..backupset where database_name=N'PBFamily' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'PBFamily' )

    if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''PBFamily'' not found.', 16, 1) end

    RESTORE VERIFYONLY FROM DISK = N'C:\DBBackup\PBFamily\PBFamily_backup_200805282036.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

  • May 28, 2008 at 9:57 pm

    #821568

    Did you try to remove N in front of each pair of single quotes?

  • May 30, 2008 at 10:15 am

    #822446

    Actually, I used the Maintenance Plan Wizard to create the maintenance plan and the T-SQL I posted is what the Wizard generated. So, not sure how the 'N's got there.

  • May 30, 2008 at 11:49 am

    #822481

    N is nothing to do with your backup file directory issue...

    N is used for unicode datatype like Nchar, NVarchar etc...

    First make sure you have the right path in maintenance plan or there might be another plan which is generating the backups in different folder.

    MohammedU
    Microsoft SQL Server MVP

  • May 30, 2008 at 12:38 pm

    #822508

    Well, I got it working. I rebooting the server and tried it again and the .BAK was created in the right path.

    Thank you,

    Paul

  • June 2, 2008 at 11:15 am

    #823126

    Well, If you are changing any sort of things related to DB/Server related changed may need to reboot the server or at least restart of the SQL engine/Agent services.

    Manoj

    MCP, MCTS (GDBA/EDA)

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

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