Solved

Difference Between De attaching database and full backup in sql server

Posted on 2011-09-20
12
234 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 4

Assisted Solution

by:alexeykomarov
alexeykomarov earned 83 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Haidar1
ID: 36571723

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

Expert Comment

by:alexeykomarov
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 167 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 167 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now