backup and restore database

You should be able to backup the database through normal means in Enterprise manager.

When restoring,

Enter the new database name in the "Restore as database" field

choose to restore "From Device" only after you enter the new database name. (If these are done in this order, SQL server will default this new name in the new mdl file name)

Select device/Add/Browse to you file in the file name field

Ok/OK   click OK till your back at the first window.

If you backup has several sets you can choose to view contents and select the set to restore, click ok.

Does this work or give you an error.

I have a database and want to make a copy of it.
Thought backup and restore is the best way to go,can I get the detailed steps for that please.
I was trying the Enterprise Manager it gave me a error ,can someone help me with that sa soon as possible.


EXECUTE sp_addumpdevice 'disk', 'pw_test_BackUP',
DISK ='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.dat'
When I tried this it gave me an error,syntax error near DISK


Appreciated.
Vinai

EM->right click DB -> All tasks -> Backup db ->se backupwizard-> clean destination if any->
add destination -> type file name like c:\mydb.bak ->
backup

Restore-> right click DB -> All tasks -> restore db -> type new DB name -> select from device->select device ->disc->
find the backup file-> ok

when I do it thru the EM , it says new device ,I put pw_testdevice and then it does not backup.
CAN YOU LIST THE STEPS FOR BACK UP FIRST, PLEASE.

THANK YOU

Backup to file, not to a device

Just open up query analyzer

BACKUP yourdatabasenamehere to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak'

This is what I did:

BACKUP pw_test to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak'

This is the error I got.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'pw_test'.

Thank you for your help.

oops....BACKUP DATABASE.....

BACKUP DATABASE pw_test to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak'

Thank you, that works but now how do I restore it on the same server with a different name.

RESTORE DATABASE newname here from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak' WITH RECOVERY,MOVE 'thenameofyour_datafilehere' to 'd:\folder\newname.mdf' , MOVE 'thenameofryour_logfilehere' to 'd:\folder\newname.ldf'


Use the MOVE option.  You give it the name of your original data file (don't include the .MDF extension) and the new folder to put it in.  the second move statement is the name of your existing log file (without the .ldf extension) and the new folder/name to assign to it.

If you right click on the original database and look at the properties (in enterprise manager), it will show you the names.

Brett

RESTORE Database pw_test2  from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak'
With Recovery, move 'pw_test_data' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\pw_test2.mdf', move 'pw_test_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\pw_test2_log.ldf'



The above assumes the original file names weren't changed.

I do not have to recover the database ,make a copy of it ,so I thought that backup and restore is the easiest way.

Also to rename will this work:
sp_renamedb'pw_test1','pw_dev'

Thank you
Vinai

So what was your last post about????  Are you saying that your original question need closed?

Also I do not want to lose the original database yet.

All I want to do is make a copy of pw_test ,rename the copy to pw_dev.

Thank you
Vinai

This is how my original started ,it is the same question.

Same suggestion as above--backup restore....you have been given several examples.  the RECOVERY option on the RESTORE statement simply tells sql server to make the database operational (ie, you don't have any more log files to restore).  So I'm not sure what your post "I do not have to recover the database" means....

After I run the restore stmt I have to run the recover stmt for the database to be operational.

Thank you for your help again.

Vinai

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'pw_test_data' is not part of database 'pw_test2'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


When I ran the above restore command this is the error I got.

The above was an example (like I said, the example assumed your original file names weren't changed)--you need to change PW_TEST_DATA to the actual name of your original file.

I know I had accepted the answer to this post, byt I still have a question.
This is how I am restoring the database, making a copy of pw_test with a new name pw_dev.
When I say with recovery move the original files to the new database, does it move my original files.
My original database needs to stay as it is .It ms my concern.

BACKUP DATABASE pw_test to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test2backup.bak'


RESTORE Database pw_dev  from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pw_test1backup.bak'
With Recovery, move 'pw_test.mdf' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\pw_test2.mdf',
move 'pw_test_log.ldf' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\pw_test2_log.ldf'

I appreciate your help.

vinai

the With recovery tells SQL Server to recover the last log file and make the database operational.

The MOVE TO  tells sql server to "rename" the backup data files to a new name.....

You can read about all this in books online..............