July 6, 2008 at 9:36 pm
Hi,
is it possible to restore ms sql 7 backup to ms sql 2000? or vice versa?
i have ms sql 7 and want to upgrade it to ms sql 2000, what is the more reliable way of upgrading its databases?
should i do the backup n restore
or the attached detached?
or any suggestions?
thanks.
July 7, 2008 at 7:30 am
[font="Verdana"]Hi,
You can upgrade a database from lower version to higher version (i.e SQL 7 to SQL 2000) and its not possible to degrade it (i.e from SQL 2000 to SQL 7) this is applicable for all version of SQL Server.
You can try both the Backup|Restore \ Detach|Attach, both the things will work for upgrading[/font]
Regards..Vidhya Sagar
SQL-Articles
July 7, 2008 at 8:46 am
I'd recommend backup/restore OR make sure you have a copy of the files before attaching. The upgrade is a one way process, so you cannot go back to SQL 7.
At this point, you should really upgrade SQL 7 to 2005 or think about 2008 soon.
July 8, 2008 at 7:55 pm
sorry but i dont think the sql 2005 or 2008 is an option..
some application might not work.. unless developers are willing to modify the aplication..;p
byt, how will i upgrade my Jobs, logins, and dts packages?!?
thanks
July 9, 2008 at 11:03 am
Before ruling out 2005 read up on the compatability level .. when set it determines how SQL statements will or will not function.
Level Server Revision
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
When attaching an existing database from SQL Server 7.0 to SQL Server 2005 you will see that the compatability level is set to 70. Most if not all of your SPs, DTS packages, etc should function properly.
My suggestion to you is detach your existing SQL 7 database,
copy the database and log files to new directory. Of course reattach to the original server to keep them available.
Install SQL Server 2005. Copy the copied files to the data directory for your 2005 server. Run tests to discover if there are any problems. I have done just that and have found one or two at most and they were easy to correct. In your case have the developers test and they will convince themselves that upgrading to 2005 is viable and worth the effort. Once they have accepted 2005 as the Server revision to use then alter the database compatability level to 90 and retest.
There is a great deal of discussion of compatability levels - differences between them both in SQL 2005 BOL as well as other web postings - check them out.
January 6, 2009 at 10:37 pm
I m trying to restore back up of sql 7 in sql 2000.
I get following error when i try with query analyzer:
'It is not recognized resotre option'
what does this mean?
When i try thru enterprize mgr.. ther error is:
'THe back up set hods back up of a database other than 'mydb'.
Restore database is terminating abnormally
Does it mean that the back up file is not proper or corrupted?
Thanks & Regrds,
KT
January 7, 2009 at 12:23 am
Kinnari Thaker (1/6/2009)
I m trying to restore back up of sql 7 in sql 2000.I get following error when i try with query analyzer:
'It is not recognized resotre option'
what does this mean?
When i try thru enterprize mgr.. ther error is:
'THe back up set hods back up of a database other than 'mydb'.
Restore database is terminating abnormally
Does it mean that the back up file is not proper or corrupted?
Thanks & Regrds,
KT
Can you post the actual restore statement you are using ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2009 at 2:06 am
Restore Database
from disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
I also tried the above command with 'with replace'
January 7, 2009 at 2:14 am
Kinnari Thaker (1/7/2009)
Restore Databasefrom disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
I also tried the above command with 'with replace'
It should be :
Restore Database YOURDBNAME
from disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
But you should first figure out which files the db contains, and where you want to place them for the instance where you are restoring on.
This will give you the expected files (and locations)
Restore filelistonly
from disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
The you can adapt your restore statement for correct placement of the db-files.
Restore Database YOURDBNAME
from disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
With move ....
e.g.
WITH MOVE 'YOURDBNAME_Data' TO 'D:\Mssql\Data\YOURDBNAME_Data.MDF ',
MOVE 'YOURDBNAME_Log' TO 'C:\MSSQL\Log\YOURDBNAME_Log.LDF' ,
RECOVERY
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2009 at 2:38 am
--- command
Restore filelistonly
from disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
--- result
Mancandosql_DataC:\MSSQL7\data\Mancandosql.mdf
Mancandosql_LogC:\MSSQL7\data\Mancandosql_Log.LDF
---- command
Restore Database ManCanDoSql
from Disk = 'D:\Dr. Acharya\ManCanDoSql.bak'
with move 'Mancandosql_Data'to 'D:\ManCanDo-Data\Mancandosql_Data.Mdf',
move 'Mancandosql_log' to 'D:\ManCanDo-Data\Mancandosql_Log.Ldf',
Recovery
---- result
Server: Msg 3154, Level 16, State 2, Line 1
The backup set holds a backup of a database other than the existing 'ManCanDoSql' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I want to put data and log file on the specified path so i changed the path.
January 7, 2009 at 3:12 am
You need to dig deeper into the backup file content.
Can you post the results of
restore headeronly
from disk=...
RESTORE VERIFYONLY
from disk=...
restore labelonly
from disk= ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2009 at 1:38 am
Thank you very much for your help...
currently i managed to get MDF and LDF file from the original server and attached database on my server... it is working..
But i would like to try the queries that u have suggested on RnD server just to know what was the problem with the back up file...but i m also busy implementing a new module...
i will get back...
January 8, 2009 at 1:54 am
just a little side note:
If you are using SQLaccounts (non-windows accounts) for you database, you'll have to resync them with you new server.
You can migrate userid's and passwords using this script:
select *
from (
select 'sp_addlogin @loginame = ' + name +
', @passwd = "' + password +
'", @encryptopt = skip_encryption' +
char(10) + 'go' as cmd
from syslogins
) a
where cmd is not null
-- and name in ('test1', 'test2') -- include specific logins only
order by cmd
You can generate the resync for the db-users to the server-users using this script:
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @UserType Char(1)
declare @sql_stmt varchar(500)
declare @ExcludeWindowsAccounts Char(1)
set @ExcludeWindowsAccounts = 'N' -- modify if you do not want to handle the windows accounts !
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.type as UserType
FROM sys.database_principals su
left join sys.sql_logins msu
on upper(su.name) = upper(msu.name)
where su.type in ('S', 'U', 'G')
-- WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA', 'list of names you want to avoid') -- modify as needed
BEGIN
if @Musername is null
begin
if @UserType in ('U','G')
begin
if @ExcludeWindowsAccounts = 'N'
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print 'exec sp_grantlogin N''NtDomein**\' + @username + ''''
print 'exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
set @sql_stmt = '--'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''', @LoginName = NULL, @Password = -- provide password'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** opgelet : exec stmt commented !!! ***'
-- EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply