Sql-server – Make a copy of database with SQL Server Management Studio Express 2005

database-administrationsql server

I have a database in MS SQL Sever 2005 Express. I need to make a duplicate of it to test some upgrade scripts. I've tried to backup and restore to a new database, and it fails because it can tell the databases are different. I don't see a way to script out the entire database, otherwise I'd try that. What is the best way to make a copy of an existing database on the same machine.

EDIT: I need all data and schema.

Best Answer

Backup and restore is the quickest way to do it. With RESTORE sql command you specify the name of new database:

RESTORE DATABASE db_new_name
  FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'
  WITH MOVE 'old_db_Data' TO 'c:\MSSQL\DATA\new_db.mdf',
       MOVE 'old_db_Log' TO 'c:\MSSQL\DATA\new_db.ldf',
  RECOVERY

The sql command above can help you:

RESTORE FILELISTONLY FROM DISK = 'C:\MSSQL\BACKUP\old_db.BAK'
Related Topic