Restore MSSQL 2000 backup into 2008 R2?
-
Would I be able to restore a backup made in SQL Server 2000 into SQL Server 2008 R2? Any extra steps I need to do to achieve this, or is it a normal restore?
Thanks.
-
Works well. there are not any extra steps, but it is important to remember that you cannot take the 2008 database back to 2000.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan -
yes you can ,change the compatibility when it finish.
-
Thanks for the quick replies
Hadrian (7/11/2012)
yes you can ,change the compatibility when it finish.Is this the way to do it, or should I use a stored procedure?
ALTER DATABASE mydb SET COMPATIBILITY_LEVEL = 100
-
kprmcl (7/11/2012)
Thanks for the quick repliesHadrian (7/11/2012)
yes you can ,change the compatibility when it finish.Is this the way to do it, or should I use a stored procedure?
ALTER DATABASE mydb SET COMPATIBILITY_LEVEL = 100
There are a lot of things you should do after upgrading from 2000 to 2008 R2. I would recommend the following, in this order:
1. Update usage.
DBCC UPDATEUSAGE(YourDatabaseName);2. Update compat mode to 100 & PAGE_VERIFY to CHECKSUM
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 100, PAGE_VERIFY CHECKSUM;3. Set the database owner to something sensible. I default to using sa for all my DBs unless more granular security reqs exist.
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa;4. Run CHECKDB WITH DATA_PURITY
USE master;DBCC CHECKDB('YourDatabaseName') WITH TABLERESULTS, NO_INFOMSGS, DATA_PURITY;5. Rebuild all indexes & column statistics
USE YourDatabaseName;EXEC sys.sp_MSforeachtable@command1 = N'ALTER INDEX ALL ON ? REBUILD WITH (SORT_IN_TEMPDB=ON); UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';6. Take a FULL backup
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Hadrian (7/11/2012)
yes you can ,change the compatibility when it finish.That is not true. You can make the database compatible with SQL 2000 datatypes & such, but you cannot change the database back to SQL 2000. Once it is restored into SQL 2008, it is essentially a SQL 2008 database no matter what compatibility level it is set at.
-
yes you can ,change the compatibility when it finish.
Changing the compatability level does not maintain the databases structure as that of a 2000 db. It does allow the db to operate in most cases as that of a 2000 db. If you thought that you could pick up the database and move it back to SS2000, then that will not work.
If the intent is to upgrade the db, then run the health check against it, fix anything identified and then move the db.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan -
Hadrian (7/11/2012)
yes you can ,change the compatibility when it finish.I think Hadrian was answering the initial post, not sjimmo, in which case the response makes sense. Look at the time stamps, very close.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply