Difference Between De attaching database and full backup in sql server

Hi experts

I need to know what is the difference between deatching database and Full DB Backup in SQL server 2005 or 2008.
I am planning to do migration or upgrading ... I know some of u will advice me to user SQL server upgrade adviser , which already I did, but now before to do in side upgrade
I like to do full export or deataching the database as rolled back backup. so what is the difference? and which on is recomeded?

Haidar1Asked:
Who is Participating?
 
EyalCommented:
if you can do it that would be great  :)

the best practice will be to do a test to see every thing will be passed as expected.
skip the read only mode for the tests you don't want to do that in this stage
0
 
Alexey KomarovChief Project EngineerCommented:
Hi
when you backup database will be online and users can connect
when you detach database and attach to different server you mast create all users in new server

http://msdn.microsoft.com/en-us/library/ms190794.aspx
0
 
Haidar1Author Commented:
Hi Alexey
you mean if I do full backup usrs accounts and logins will be backuped up as well.? In this case

Full Backup will be recomended in my acctivity. please advce.?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Haidar1Author Commented:

Please correct me if I am mistaken
Full Database backup will take all the obejcts owned by the database like
data files. log files, index, procedurs  logins and uses is that correct?

0
 
EyalCommented:
Hi,

If you want your application to stay online as much as possible  and don't want to hassle much
I would recommend you to:  
1) change database to readonly mode so new data can't be applied to it
2) backup (with database users)
3) copy
4) restore in new location
5) change your application to point to the new restored database.

deatching the database will allow you to copy the database files (data files and log files). and as alexeykomarov said it will not move the users because they are located in the master database.
0
 
Haidar1Author Commented:
Hi Eyal

2) backup (with database users)

You mean full database backup ? Tasks - backup - Full DB backup.?




0
 
EyalCommented:
yes. this is needed to move all the database to another location
0
 
Haidar1Author Commented:

what about logins ? do ineed to recreate them?
0
 
Alexey KomarovChief Project EngineerCommented:
Full database backup not include logins
You need transfer logins http://support.microsoft.com/kb/918992 or
 backup and restore master database http://msdn.microsoft.com/en-us/library/ms190679(v=sql.90).aspx
0
 
EyalCommented:
login data is stored in the master so it will not be taken with the backup file

see how to generate the users in the new server
http://msdn.microsoft.com/en-us/library/ee406927%28v=bts.10%29.aspx

pay attention: if you have jobs you should script them as well.
0
 
Haidar1Author Commented:

thanks eyal for your reply But
What if I did full System DB Backup, and full Application DB Backup?
In this way I will take backup for data, logs, procedure, index, login's and users accouts as well
Then I will upgrae from 2005 to 2008 (inside mode)
Then I will restore system DB
Then I will restore applicaotion DB
What you think?
0
 
Haidar1Author Commented:
Thankd to all I will implemented in my activities
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.