copy a database within SQL Server Express?
I would like to make a copy of a database I have but keep it on the same server as a test database. However, everything I have found is to use the copy database wizard (I am using MS SQL Server Express).
The instructions always say: In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
I don’t have the Copy Database option. I am running as an admin, so no clue why it is missing for me – is it something I have to install separately? I can’t do the Detach/Attach since it is copying to the same server. I tried detaching, copying the MDF/LDF, renaming, attaching but as you can imagine that messed a ton up 🙂 I am not great with SQL to do it all programatically. Is there a tool out there I could use?
10 Solutions collect form web for “copy a database within SQL Server Express?”
In SSMS 2008 you can do this:
Create a backup of the database you want to copy
In SSMS, right-click ‘Databases’ and select ‘Restore Database’
Select the database you wish to copy from the ‘From database’ drop-down list in the ‘Source for restore’ section
Enter the name of the new database in the ‘To database’ field in the ‘Destination for Restore’ section – this cannot be the name of an existing database.
You’re done! 🙂
In SQL Server Express 2012 you can do following steps:
- Create a backup of the database you want to copy
- right-click “Databases” and select “Restore Files and Filegroups”
- Enter the name of the new database in the “To database” field.
- Select “From device” and then select the file that you backuped in the first step
- click “OK”
this will “clone” the Database with the correct table settings such as the “default value” and “auto increase” etc.
SQL Express database has an export button, I just exported the database to a new database on the same server, it is copying the database. Just right-click on the database name.
Take these steps to make a copy of the database in SQL Express
- Stop SQL
- Copy the mdf, ldf and any other file for the db to a NEW location (make sure you get the log file)
- Change the name of each copied file
- Start SQL
- Right-click Database in SSMQ and select attach
- Make sure you change the name in the column “Attach As”
- Update the file location in the lower pane of “Database Details” to the location of your copied files (especially that log file)
I was able to copy a database on my SQL Express system with this method
Just be aware if you are using SQL Server Express 2012 of going to the option Files and make sure that the destination files (Restore As column) are different from the original files *.mdf and *.log
( I tried to put an image but need 10 reputation :p)
I do not believe the Express version of the manager will have the copy database feature. Have you considered copying via the backup and restore method?
The solution is definitely to create a backup and restore it, but ensure that you’re restored copy is pointing to different .mdf and .ldf files.
Here’s how to check that using SSMS 2014 and a SQL Server 12 installation: http://screencast.com/t/7L29BEJcr9e, assuming you’re creating and restoring backups on your local disk.
- Create a backup of your existing database
- Right click the database, and choose “back up…” under “tasks.”
- (If you leave the location as the default, you don’t have to hunt for the back up in the next step when you restore.)
- Restore your backup to a NEW database:
- Right click on databases, choose “restore database”
- Select “device”
- Click the ellipsis button (“…”) to open the “Selct Backup devices” dialog.
- Choose “File” as the backup media type and click the “add” button
- Select the backup you just made, click ok (twice)
- Now, back in the “restore database” dialog, type a new name for your destination database
- Click “files” under “select a page” and make sure that “restore as” is pointing to .mdf and .ldf file names that do not already exist
- Click ok!
I found the problem! Click on Databases, restore, then do the following:
After choosing from where to restore, and writing destination db name, go to files [annotation 1 on picture] and change the very right column files names to different than original [annotation 2 on picture] then it works 🙂
Try making a backup of your database, and restoring it into a brand new database.
- Create new DB.
- Make a full backup of your original.
- Right click on your new DB, hit restore.
- Navigate to your .BAK, and ensure the .mdf and .ldf match the new.
I think you could try import data to a new database.
- Create an empty database in your local sql server
- Right click on the new database -> tasks -> import data
- In the SQL Server Import and Export Wizard, select product env’s servername as data source. And select your new database as the destination data.