Copy database to another on same server

Question

Hi,

I have a database and want to copy its tables, etc, to another database, so I can use one of these databases as a "staging" DB.

What is the best/most foolproof way to do this?

Thanks

Answer 1

If you want to copy everything, I'd create a full backup of the DB you want to copy: Right click db > Tasks > Backup. Create an new db in SQL server and restore the backup file into it: right click > Tasks> Restore database. This will give you a full copy of the orignal DB.

Answer 2

Doing so, gives this error:

Doing so (that was my first idea) gives this error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'DEV1'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1352.12+((KJ_PreRelease).091030-1757+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

---

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\maximumbhp.mdf' cannot be overwritten. It is being used by database 'maximumbhp'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1352.12+((KJ_PreRelease).091030-1757+)&LinkId=20476

---

BUTTONS:

OK

I would add this to a comment but the comments section is not big enough.