Copy Database - Same SQL Server

Hello.
I am fairly new to SQL Server. I have a hosted sql server with unlimited licensing and database has been created on it from outsourced vendor.
I need to create a duplicate database, on same server, different directory, with a different name for development and testing.
I have been told to simply to a backup and restore with MOVE, however in my research I have come across things to be aware of and even errors. I am a newbie dba for small company and I do not want to take chances ruining their current database.
Does anyone have any directions-specific advice?
Your help will be greatly appreciated!Issues you were warned about are real, but harmless in your case. Backup/restore will work for you just fine. Just make sure to change the name of the database prior to clicking on Restore. As a precaution, also make sure that "Force restore over existing database" is NOT selected.|||You can also:

1. detach the database
2. copy the database to the new location
3. attach both databases|||The time you really need to be carefull with the Backup/Restore method is when you are restoring to a different server, where user logins may not match those in your database. This should not be an issue in your case.

By the way, why do you need to have the database in a different directory?|||...wait a minute, there's (at least) one more thing you need to be aware of:

If any of the code in your database reference objects using a fully qualified name that includes that name of the database along with the name of the object, this code will execute against the objects in your original database rather than the differently-named copy. For instance, lets say your databases are called DBORIGINAL and DBCOPY, this code:

select * from YOURTABLE

will run fine in your copied database. But this code:

select * from DBORIGINAL..YOURTABLE
-or-
select * from DBORIGINAL.OWNER.YOURTABLE

will continue to select from the original database, not your copy.

You should check your stored procedures, views, and functions to look for cases like this.|||The application being tested will have to be looked over before you get too far. If the application uses ODBC, and you can not change the DSN in the application's connection strings, then you can not test (except on production). Also, if the connection strings specify a database, then you are back in the above situation. Lastly (and here is my favorite), does the user that is being used have a default database assigned? Likely you will not be able to change that on production.
Ideally, a second DB server (or a named instance) would be better for testing purposes.|||Good point blindman - that goes for any object that uses fully qualified names. Even something as simple as copying/scripting an object from one database to another.|||I used the Backup/Restore method to duplicate a database with different database name for our clients' testing. There are following things you need to do:

1) Like blindman said, you must make sure to change the original database name into the copied database name in your scripts if there is the original database name in your scripts.

2) Create new user login for the copied database.

3) Create the ODBC for your connection using a new DSN

4) Change you connection string in your code to the copied database.|||Good practice that I acquired was to preceed your application with logon screen that captures the essentials of the environment that the application will execute in. This way you avoid the pain of having hard-coded connection strings to be changed every time you move your database to a different server or having to rename your database all together.|||The more experience you get, the more parameters you use!|||In ASP, you can use Application variable to store the connection string. So you only need to change one file which is global.asa.|||I use conn.inc.asp with a

Function oc()
End Function

Place that in top of ya asp pages and only have to change 1 file.|||Different ways can get the same target, but the important thing is to use a better way.|||ok, i've had to do this today for development work.

tried thru Trans-SQL didn't get very far, used Enterprise Manager...

In enterprise manager, create new database on desired server, "Tel_Copy", original "Tel".

Now right-click on "Tel-Copy", select options, set to restricted/single-user access, click ok to close dialog.

Now right-click on "Tel-Copy", select Restore Database.

Make sure "Tel-Copy" is what it says in Restore Database As...

Now Choose backup device, from file, add old backup file. now goto next tab, select "force restore over existing database".

Now in the list below, you'll see Tel_Data -> C:\tel_data.mdf, and Tel_Log -> C:\tel_log.ldf

change c:\tel_data.mdf to c:\tel_copy_data.mdf
(remember to not change the logical dataname here)

change c:\tel_log.mdf to c:\tel_copy_log.mdf
(remember to not change the logical dataname here)

hit restore and the Tel backup will be restored as Tel-Copy

worked here.