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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
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.
ASKER
Hi Eyal
2) backup (with database users)
You mean full database backup ? Tasks - backup - Full DB backup.?
2) backup (with database users)
You mean full database backup ? Tasks - backup - Full DB backup.?
yes. this is needed to move all the database to another location
ASKER
what about logins ? do ineed to recreate them?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thankd to all I will implemented in my activities
ASKER
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.?