Create a copy of database with a new name

• Mitesh Oswal

  SSCrazy

  Points: 2798

  More actions

  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

• tskelley

  SSCommitted

  Points: 1870

  More actions

  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'

• terry.home

  Ten Centuries

  Points: 1004

  More actions

  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