Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Difference Between De attaching database and full backup in sql server

Posted on 2011-09-20
12
Medium Priority
?
241 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

604 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