How can I back up and copy my SQL server 2005 DB without ghosting the hard drive

[Pelle]

I am know to sql server and would now like to learn how I can take a back up copy of the database. As far as I know sql server is not based on a single database file like Access.

Hope someone can provide me with some info about how to copy the database.

[russell]

BACKUP DATABASE databaseName
TO Disk = 'c:\backups\databaseName.bak'


Change path and databaseName to actual in your circumstance.

Check BOL for options and more syntax.

Also, read up on recovery models, as this will affect your backup plan.

[Pelle]

Thank you for your help and sorry for being such a novice user.

Where (in what application) should I write the code that you provided (if in sql2005, could you please point me to the right spot)?

[aussie girl]

you can detach the database, copy it to another location then attach it again..

[russell]

use SQL Server Management Studio.

[Pelle]

I will use SQL Management studio with pleasure.

I just need to know where (exactly if possible) I am supposed to enter the code that Russell provided.

Regarding the "detach method". Where and how is that done? I didn't know that SQL 2005 was "file based" so I can copy it to another location...

[russell]

open management studio. ( do u have it installed? )
connect to database engine
expand databases
expand system databases
right-click master
click new query

enter code for backup

if u rather use detach/attach see sp_detachdb in BOL (Books On Line).

are u moving/copying, or just making a backup? if moving, sp_detach is way to go.

yes, sql server is file based, but it performs as much as possible in memory. a typical default installation creates 2 files -- a data file and a log file. can have more if configfured as such. note that u cant just copy the files like a document of an access db

[aussie girl]

To detach / attach databases
http://www.databasedesign-resource.com/moving-the-database.html

[russell]

should use sp_attach_db and sp_detach_db , not GUI. also, very important to get a full backup of db BEFORE detaching...in case things go wrong...

if u are permanantly moving files, then aussie girl is right u should detach/attach.

if u just want a backup, use backup

[aussie girl]

should use sp_attach_db and sp_detach_db , not GUI. also, very important to get a full backup of db BEFORE detaching...in case things go wrong...

if u are permanantly moving files, then aussie girl is right u should detach/attach.

if u just want a backup, use backup

why not GUI?

[Pelle]

Thanks to both of you for your replies.

I will have a look in management studio and see what I find.

[chrismartz]

why not GUI?Using t-sql gives you more options than if you use the GUI of sql server. The GUI will work but it doesn't always use the shortest method of doing something. For example (this is correct @ least in sql server 2000 but I doubt microsoft has changed its method much), if you are adding a column to a table that has alot of columns in it, the GUI will create a new table, create a column, copy the columns from the current table with the data, delete the current table, and then finally rename the table the old tables name. Using the t-sql, you do not need to eat up resources because you can just create the column in one step.

Everything can be done in many different ways but I agree with Russel about not using the GUI for most things. Just our opinions though. Its purely up to the DBA.

[aussie girl]

Using t-sql gives you more options than if you use the GUI of sql server. The GUI will work but it doesn't always use the shortest method of doing something. For example (this is correct @ least in sql server 2000 but I doubt microsoft has changed its method much), if you are adding a column to a table that has alot of columns in it, the GUI will create a new table, create a column, copy the columns from the current table with the data, delete the current table, and then finally rename the table the old tables name. Using the t-sql, you do not need to eat up resources because you can just create the column in one step.

Everything can be done in many different ways but I agree with Russel about not using the GUI for most things. Just our opinions though. Its purely up to the DBA.

I was asking purely about a detach / attach database situation not about entering data or creating tables and I do agree with you

[russell]

EM has a nasty habit of hanging from time to time. do NOT want this when performing delicate operations like sp_detach_db.

also isqlw (query analyzer) offers much more flexibility and control over most operations than does EM (though this isn't one of 'em). I use EM for almost nothing.