February 29, 2008 at 4:23 pm
OK, I am NO WHERE near being any type of SQL guru (obiviously from my question!), but from the looks of this place it looks like a good resource for answers. Are there any real differences between restoring a .bak file (on a new server) in 2000 as opposed to 2005? Do I have to create a new database first in 2000 like I do in 2005? Any differences in the 2 processes would be greatly appreciated! Thanks.
February 29, 2008 at 4:35 pm
congrats on being my 300th post.:D
if you are just restoring a .bak file the syntax is identical in 2000 and 2005 and so the process is identical. (TSQL wise, the gui presentation is different)
BTW there is no need to to create the db first in either version, the restore process creates the database
2005 has some more options in the restore\backup process for the more esoteric stuff, Your best bet is to look up restore database and backup database in books online (BOL)
---------------------------------------------------------------------
March 2, 2008 at 10:41 am
Do I have to create a new database first in 2000 like I do in 2005?
There is no need to create a database before a restore. Restore the database and give it a name. The TSQL syntax is exactly the same, but the user interface is different on 2005.
March 2, 2008 at 11:19 am
Echo the above statements and good luck!! Also congrats!
Don't worry too much about 2005. Most of the stuff that worked in 2000 still works, so you can keep being successful. There are many new things and I'd urge you to slowly dig in over time, but there's lots of backward compatible stuff.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 2, 2008 at 1:08 pm
As above, the restore process will create the database files (if they don't exist).
A caveat: if you are restoring over an existing database (for whatever reason), it is better to leave the existing database there and restore over it rather than dropping it first. It means the restore process will not have to create the physical files, which can take additional time. Not sure on the relative times for creating a blank database with presized files + restore vs letting restore create the files.
For smaller databases is moot point, but for bigger databases can save some time.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
March 2, 2008 at 9:36 pm
And in both environments remember that if you're using SQL authentication for access to the database you may need to use the trusty
sp_change_users_login 'auto_fix', [theLogin]
where [theLogin] is the login/user name that you need to remap. The number of times I've had to convince people of the need for this is amazing. Of course they tend to use the sa account for all access, even in their applications, so they don't see the problem and thus aren't aware of the fact that a username mapping to a login is done via the login's ID on the server rather than the login's name.
March 3, 2008 at 8:29 am
Ian
Good point, that is the one thing I have seen forgotten over and over again. Then I get the question why this login is no longer working. Not sure if that should become an automatic part of the restore process or not.
Marvin Dillard
Senior Consultant
Claraview Inc
March 4, 2008 at 7:03 am
cos in 2005 there is some thing called schema and every user needs one to work in it.
in 2000 there is nothing like this.
so u have to map the user for a schems becouse the restore operation will not do it for u.
by the way u can take the loggins from 2000 to 2005 using a script, i have one for this kind of things if u want i can post it for u.
..>>..
MobashA
March 4, 2008 at 11:51 pm
Hi,
The best think to do is take full backup and restore it on 2005 server in GUI mode and at that time you have to pass the database name to.
after performing the restore oration it might be possible ur facing a problem related to orphan user so that you have to verify it and fix it using the following commands given below.
sp_change_user_logins 'reports' -- shows user details
sp_change_user_logins 'update one', username, loginname -- fixed the orphan user problem.
Thanks
Nitin
March 5, 2008 at 9:42 am
Thank you all for your comments!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply