How to take Back Up database file

Question

Hi,

   I am using sql server 2008 in my PC,... now i want to format my system  but i dont know how to take back up of database file (means .mdf file and .ldf file) from sql server  for future use.....so, plz can any one guide me how to take back up of database file in the form of .mdf  which is already exists in sql server of my PC.........

 

       waiting for  answer........

    Thanks.

Answer 1

option 1

backup database database_name  to disk ='path'

Option 2

open SQL Server management studion--> right click on the database-->task-> backup  check the image below

give the name of the back file

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

Or open a New query Window and issue BACKUP DATABASE dbname TO DISK ='C:\dbname.bak'

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 3

In my point of view the easiest way is DETACH/ATTCH solution:

1. In SSMS (SQL Server management Studio) right click on your database and select DETACH 
2. copy *.mdf *.ldf files in a safe storage (ex. external HDD). (To find your database files, just right click on your database and click Properties. In database properties window select Files from the left pane and you can see database Path in a grid appears in the right pane.)
3. format your computer
4. install SQL Server again
5. open SSMS and right click on Databases and click ATTACH                                                           

All done.

Cheers

---

MCP, MCTS, MCITP

Answer 4

If SQL server exists along with Database files are online then you can take the Backup of the databases in an appropriate locations safley ensure that

you are not formatting other drive where you have pointing the backup file or better copy that backup file to the other server or other your TP...

you can also do with Dettach method for copying the physical db files as suggested by the soheil.

But since if you are going to format entire thing then again you have to installation from the scratch incase if you need SQL server then do the attach or

restore..

so better take all the informations like sysaltfiles,version,sp_helpdb,scriptpout logins etc.... Incase if SQL server exists in your system

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 5

When formatting,  I would prefer to take os level copy of mdf and ldf file ( resourcedb backup can be done only via os level) for a safety side.

Answer 6

Hi ,

Pls run the script setting your required  input , you can use it to schedule the database backup.

DECLARE VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
/*
Setup Your database name,path for backup files,filename for backup,used for file name
Set @name =
Set @fileName =
Set @path =
Set @fileDate =
*/
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 

---

Ahsan Kabir