Duplicate SQL Database

I am trying to duplicate a SQL Server Database using Enterprise Manager, so
it can be used for testing.
Can anyone give me any hints or tips on how to do this.
Thankshi ellie,
you can take help of "backup database"/"restore database" commands. backup y
our source
database using "backup database" command and restore it on the other databas
es with "restore
database" command. you can also also use system stored procedures sp_detach_
db and
sp_attach_db. Use sp_detach_db procedure to detach the data/transaction log
files from the
source database (database will not be available.) and attach the required da
tabases using
sp_attach_db stored procedure.
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi Vishal
I have tried to backup the database (which I can do!) but when it comes to
restoring it, I can't restore it as a new database. I can restore it to a
different sql server using the same name (and same backup file location etc)
but struggle to restore to the same server with a different database name.
Do I need to change the logical file name and the physical file name to
match the new database name?
Thanks
Ellie
"Vishal Parkar" <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote in message
news:%23aI$7mYEEHA.3716@.TK2MSFTNGP10.phx.gbl...
> hi ellie,
> you can take help of "backup database"/"restore database" commands. backup
your source
> database using "backup database" command and restore it on the other
databases with "restore
> database" command. you can also also use system stored procedures
sp_detach_db and
> sp_attach_db. Use sp_detach_db procedure to detach the data/transaction
log files from the
> source database (database will not be available.) and attach the required
databases using
> sp_attach_db stored procedure.
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>|||hi ellie,
you will have to consider "with move" option while running "restore database
" command. Refer
to following topic in books online (this illustrates an example as well)
"How to restore a database with a new name (Transact-SQL)"
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi,
Add on Vishals post, perform these steps
1. Execute the below command from Query analyzer
Restore filelistonly from disk='c:\mssql\backup\dbname.bak'
The above command will give you the details of Logical name and Physical
name
2. Execute the below command from Query Analyzer
restore database <new_dbname> from disk='D:\mssql\backup\dbname.bak'
with
move 'logical_dataname' to 'c:\mssql\data\dbname_new.mdf',
move 'logical_logname' to 'c:\mssql\data\dbname_new.ldf',stats=10
The logical_dataname and Logical_logname should be taken based on the output
of Restore filelistonly command (Step 1)
Note:
Replace dbname , directory name, physical file name based on ur requirement.
Thanks
Hari
MCDBA
"Vishal Parkar" <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote in message
news:OmWg54YEEHA.3180@.TK2MSFTNGP12.phx.gbl...
> hi ellie,
> you will have to consider "with move" option while running "restore
database" command. Refer
> to following topic in books online (this illustrates an example as well)
> "How to restore a database with a new name (Transact-SQL)"
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>|||In addition to all the other posts,
Yes you must use a different name for the database and the files ( but they
do not have to match).
Hari showed you how to do this using T-SQL, you may also do this using the
Restore Database dialog box, Simply change the filenames on the right side
of the dialog box..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ellie" <ellieb@.solutionsforaccounting.co.uk> wrote in message
news:u2WEJlYEEHA.2088@.TK2MSFTNGP10.phx.gbl...
> I am trying to duplicate a SQL Server Database using Enterprise Manager,
so
> it can be used for testing.
> Can anyone give me any hints or tips on how to do this.
> Thanks
>sql