sql-server - with - sql server copy database to another server
How can I clone an SQL Server database on the same server in SQL Server 2008 Express? (9)
Install Microsoft SQL Management Studio, you can download it for free from Microsoft website:
Microsoft SQL Management Studio 2008 is part of SQL Server 2008 Express with Advanced Services
Click download button and check ENU\x64\SQLManagementStudio_x64_ENU.exe
Click download button and check MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe
Open Microsoft SQL Management Studio
- Backup original database to file (db -> Task -> Backup).
- Create empty database with new name (clone).
- Click to clone database and open restore dialog (see image)
- Select Device and add the bakcup file from step 1.
- Change destination to test database
- Change location of database files, it must be different from the original. You can type directly into text box, just add postfix. (NOTE: Order is important. Select checkbox, then change the filenames.)
- Check WITH REPLACE and WITH KEEP_REPLICATION
I have an MS SQL Server 2008 Express system which contains a database that I would like to 'copy and rename' (for testing purposes) but I am unaware of a simple way to achieve this.
I notice that in the R2 version of SQL Server there is a copy database wizard, but sadly I can't upgrade.
The database in question is around a gig. I attempted to restore a backup of the database I want to copy into a new database, but with no luck.
Another way that does the trick by using import/export wizard, choose the source is your server with the source database, and then in the destination choose the same server with the destination database (you need to create empty database first), then hit finish
It will create all tables and transfer all the data into the new database,
In SQL Server 2008 R2, back-up the database as a file into a folder. Then chose the restore option that appears in the "Database" folder. In the wizard enter the new name that you want in the target database. And choose restore frrom file and use the file you just created. I jsut did it and it was very fast (my DB was small, but still) Pablo.
It turns out that I had attempted to restore from a backup incorrectly.
Initially I created a new database and then attempted to restore the backup here. What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.
So, in short, restoring from a backup did the trick.
Thanks for all the feedback and suggestions guys
Right-click the database to clone, click
Copy Database.... Follow the wizard and you're done.
Script based on Joe answer (detach, copy files, attach both).
- Run Managment Studio as Administrator account.
It's not necessary, but maybe access denied error on executing.
- Configure sql server for execute xp_cmdshel
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
- Run script, but type your db names in
USE master; GO DECLARE @dbName NVARCHAR(255) = 'Products' DECLARE @copyDBName NVARCHAR(255) = 'Products_branch' -- get DB files CREATE TABLE ##DBFileNames([FileName] NVARCHAR(255)) EXEC(' INSERT INTO ##DBFileNames([FileName]) SELECT [filename] FROM ' + @dbName + '.sys.sysfiles') -- drop connections EXEC('ALTER DATABASE ' + @dbName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE') EXEC('ALTER DATABASE ' + @dbName + ' SET SINGLE_USER') -- detach EXEC('EXEC sp_detach_db @dbname = ''' + @dbName + '''') -- copy files DECLARE @filename NVARCHAR(255), @path NVARCHAR(255), @ext NVARCHAR(255), @copyFileName NVARCHAR(255), @command NVARCHAR(MAX) = '' DECLARE @oldAttachCommand NVARCHAR(MAX) = 'CREATE DATABASE ' + @dbName + ' ON ', @newAttachCommand NVARCHAR(MAX) = 'CREATE DATABASE ' + @copyDBName + ' ON ' DECLARE curs CURSOR FOR SELECT [filename] FROM ##DBFileNames OPEN curs FETCH NEXT FROM curs INTO @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @path = REVERSE(RIGHT(REVERSE(@filename),(LEN(@filename)-CHARINDEX('\', REVERSE(@filename),1))+1)) SET @ext = RIGHT(@filename,4) SET @copyFileName = @path + @copyDBName + @ext SET @command = 'EXEC master..xp_cmdshell ''COPY "' + @filename + '" "' + @copyFileName + '"''' PRINT @command EXEC(@command); SET @oldAttachCommand = @oldAttachCommand + '(FILENAME = "' + @filename + '"),' SET @newAttachCommand = @newAttachCommand + '(FILENAME = "' + @copyFileName + '"),' FETCH NEXT FROM curs INTO @filename END CLOSE curs DEALLOCATE curs -- attach SET @oldAttachCommand = LEFT(@oldAttachCommand, LEN(@oldAttachCommand) - 1) + ' FOR ATTACH' SET @newAttachCommand = LEFT(@newAttachCommand, LEN(@newAttachCommand) - 1) + ' FOR ATTACH' -- attach old db PRINT @oldAttachCommand EXEC(@oldAttachCommand) -- attach copy db PRINT @newAttachCommand EXEC(@newAttachCommand) DROP TABLE ##DBFileNames
This is the script I use. A bit tricky but it works. Tested on SQL Server 2012.
DECLARE @backupPath nvarchar(400); DECLARE @sourceDb nvarchar(50); DECLARE @sourceDb_log nvarchar(50); DECLARE @destDb nvarchar(50); DECLARE @destMdf nvarchar(100); DECLARE @destLdf nvarchar(100); DECLARE @sqlServerDbFolder nvarchar(100); SET @sourceDb = 'db1' SET @sourceDb_log = @sourceDb + '_log' SET @backupPath = 'E:\tmp\' + sourceDb + '.bak' --ATTENTION: file must already exist and SQL Server must have access to it SET @sqlServerDbFolder = 'E:\DB SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\' SET @destDb = 'db2' SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf' SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf' BACKUP DATABASE @sourceDb TO DISK = @backupPath RESTORE DATABASE @destDb FROM DISK = @backupPath WITH REPLACE, MOVE @sourceDb TO @destMdf, MOVE @sourceDb_log TO @destLdf
Using MS SQL Server 2012, you need to perform 3 basic steps:
.sqlfile containing only the structure of the source DB
- right click on the source DB and then Tasks then Generate Scripts
- follow the wizard and save the
Second, replace the source DB with the destination one in the
- Right click on the destination file, select New Query and Ctrl-H or (Edit - Find and replace - Quick replace)
Finally, populate with data
- Right click on the destination DB, then select Tasks and Import Data
- Data source drop down set to ".net framework data provider for SQL server" + set the connection string text field under DATA ex:
Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15
- do the same with the destination
- check the table you want to transfer or check box besides "source: ..." to check all of them
You are done.
You could try to detach the database, copy the files to new names at a command prompt, then attach both DBs.
USE master; GO EXEC sp_detach_db @dbname = N'OriginalDB'; GO
At Command prompt (I've simplified the file paths for the sake of this example):
copy c:\OriginalDB.mdf c:\NewDB.mdf copy c:\OriginalDB.ldf c:\NewDB.ldf
In SQL again:
USE master; GO CREATE DATABASE OriginalDB ON (FILENAME = 'C:\OriginalDB.mdf'), (FILENAME = 'C:\OriginalDB.ldf') FOR ATTACH; GO CREATE DATABASE NewDB ON (FILENAME = 'C:\NewDB.mdf'), (FILENAME = 'C:\NewDB.ldf') FOR ATTACH; GO