May 18, 2004 at 10:08 pm
|
I just have .mdf and .ldf files with me. Can i restore data onto sql7 using those files. How do i do it?? |
May 19, 2004 at 12:32 am
- take a look at sp_attatch_db in BOL.
- you cannot attatch sql2k mdf/ldf to sql7 servers.
- You'll also have to sync db-users after you've attatched.
use [User-db]
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
--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.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid') -- enkel aanvullen indien je een ID niet wenst de synchroniseren
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomain**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomain**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account '
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
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 in comment !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
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
May 19, 2004 at 12:35 am
Hello Asha,
There is a possibility of restoring the files which you are having right now:
1. If the files are from SQL Server version 7 and want to restore on to a SQL Server 7 only.
You will not be able to restore the files from a SQL Server 2000 box to a SQL Server 7 box.
Copy the following syntax into Query Analyzer and change the coloured text accordingly reflecting the database name and the path where should they recide.
EXEC sp_attach_db @dbname = N'your database name',
@filename1 = N'path\your database name.mdf',
@filename2 = N'path\your database name_log.ldf'
Lucky
May 19, 2004 at 3:53 am
I suggest unless you know they come from another source like an Access database or SQL 2000 and you have 7 that you just try to attach. It will give you a nice error message if it has issues.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply