Post reply

Create a copy of database with a new name

  • February 26, 2014 at 8:57 pm

    #304527

    Comments posted to this topic are about the item Create a copy of database with a new name

    Regards,
    Mitesh OSwal
    +918698619998

  • February 27, 2014 at 4:16 am

    #1692603

    Many thanks! I absolutely loved this. I need to make a copy of my current dev db so many times. This will be a great time saver. I did make a few minor tweaks to the declare section, which I think make it more readable/usable (but I know this can always be a bit subjective). 🙂

    DECLARE @FirstDBName NVARCHAR(100) = 'Mitesh01'

    DECLARE @NewDBName NVARCHAR(100) = 'Mitesh02'

    DECLARE @BackupLocation NVARCHAR(100) = 'C:\Backup\'

    DECLARE@fileName NVARCHAR(1000)

    SELECT @fileName = @BackupLocation+@FirstDBName+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),SYSDATETIMEOFFSET(),120),'-',''),':',''),' ','')+'.bak'

  • February 27, 2014 at 1:47 pm

    #1692875

    I like what you have done although I have not tried running the script yet.

    Ideally I would like to copy the database to another server, not onto the same server (normally to be used for reporting purposes).

    In my (limited) experience I have had to run one job to do the backup, then a seperate scheduled task that (via batch job) copies the backup file to remote server, then a seperate agent task scheduled to run about an hour later that restores the file on that server.

    If there is a way to run just one script, that does the backup, then restores the file on the remote server, that would be even better.

    T

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

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