CODEWITHSUNDEEP
sql-serverdatabasedatabase-backups
TheLovelySausage
TheLovelySausage

Reputation: 4104

SQL Server 2014 Create a Copy of an Existing Database

I've been trying to make a copy of a SQL Server Database (2014) and the recommended way to do this using SQL Server Management Studio seems to be the following.

Right Click Database >
    Tasks >
        Generate Scripts >
            Advanced (Schema and Data) (Single file per object)

Then I have to update the references to the database name to use the new database name.

This won't work for me though because some of the object files it outputs can be over a GB which means I can't open the file to change the database name.

Is there another way to create a copy of a database to a new database with a different name?

Upvotes: 0

Views: 407

Answers (2)

GuidoG
GuidoG

Reputation: 12039

I use this script for that.

declare @nameSuffix varchar(50) = 'DB_NEW_NAME'
declare @path varchar(250) = 'C:\Program Files\Microsoft SQL Server\YOUR PATH\MSSQL\'
declare @backupfile varchar(250) = @path + 'Backup\' + @nameSuffix + '.bak'
declare @moveFile varchar(300) = @path + 'DATA\' + @nameSuffix + '.mdf'
declare @moveLogFile varchar(300) = @path + 'DATA\' + @nameSuffix + '_log.ldf'

backup database DB1
to disk = @backupfile
with init, noformat, skip, stats=10

use master

restore database DB_NEW_NAME
from disk = @backupfile 
with replace,
 recovery,
move 'DB1' to @moveFile,
move 'DB1_Log' to @moveLogFile

Upvotes: 1

hermione
hermione

Reputation: 35

you can copy the database by:

right click database to be copied > tasks > copy database

the database wizard will guide you to create your new copy.

more resources here: https://msdn.microsoft.com/en-us/library/ms188664.aspx

Upvotes: 0

Related Questions