How to copy a windows file using sql server

Posted on 2009-04-16
Last Modified: 2012-05-06
i am using Quest litespeed to backup the databases in sql server to local drive.Is it
possible to copy the recent backup file from local drive to any network share drive using sql server.I tried with
xp_cmdshell command, but i am not sure how can i pick the latest file.

Question by:2575069
    LVL 31

    Expert Comment

    you can create backup file with datetime stamp while copying it, you can check that datetime and can decide, what to copy
    LVL 31

    Accepted Solution

    see, in below script , I am deleting 24 hours old backup, with the same way, you can copy latest backup.

    BTW, I used to create backfile with specified structure. like:

    backupfilename=@DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';

    DECLARE @BackupFolder2DaysBack AS varchar(250); 
    DECLARE @BackupFolder1DayBack AS varchar(250);
    DECLARE @Path AS varchar(250);
    DECLARE @CurDT AS datetime;
    DECLARE @PrevDT AS datetime;
    DECLARE @DBName AS varchar(250);
    SET @Path = 'E:\BACKUP\';
    SET @CurDT = DateAdd(Day,-1,getdate());
    SET @PrevDT = DateAdd(Day,-2,getdate());
    -- Loop through databases
    DECLARE delExpDatabases CURSOR
     select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','test1','test2','ASPState') 
    OPEN delExpDatabases
    FETCH NEXT FROM delExpDatabases INTO @DBName
     SET @BackupFolder2DaysBack = @Path + @DBName + '\' + CONVERT(varchar(10), @PrevDT, 110);
     SET @BackupFolder1DayBack = @Path + @DBName + '\' + CONVERT(varchar(10), @CurDT, 110); 
     DECLARE @cmdStr AS varchar(250);
     SET @cmdStr = 'rmdir /Q /S '+@BackupFolder2DaysBack
     --print @cmdStr
     EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
     SET @cmdStr = 'ERASE /Q /S '+@BackupFolder1DayBack+'\*.dif '+@BackupFolder1DayBack+'\*.trn' 
     --print @cmdStr
     EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
       FETCH NEXT FROM delExpDatabases INTO @DBName
    CLOSE delExpDatabases
    DEALLOCATE delExpDatabases

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now