?
Solved

Difference Between De attaching database and full backup in sql server

Posted on 2011-09-20
12
Medium Priority
?
239 Views
Last Modified: 2012-06-22
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?

0
Comment
Question by:Haidar1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 5

Assisted Solution

by:Alexey Komarov
Alexey Komarov earned 249 total points
ID: 36571398
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
 

Author Comment

by:Haidar1
ID: 36571417
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
 

Author Comment

by:Haidar1
ID: 36571461

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:Eyal
ID: 36571499
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
 

Author Comment

by:Haidar1
ID: 36571520
Hi Eyal

2) backup (with database users)

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




0
 
LVL 15

Expert Comment

by:Eyal
ID: 36571547
yes. this is needed to move all the database to another location
0
 

Author Comment

by:Haidar1
ID: 36571723

what about logins ? do ineed to recreate them?
0
 
LVL 5

Expert Comment

by:Alexey Komarov
ID: 36571735
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
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 501 total points
ID: 36571760
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
 

Author Comment

by:Haidar1
ID: 36571817

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
 
LVL 15

Accepted Solution

by:
Eyal earned 501 total points
ID: 36571842
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
 

Author Closing Comment

by:Haidar1
ID: 36572579
Thankd to all I will implemented in my activities
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question