March 5, 2010 at 6:15 am
One of our clients wishes to move their database from our servers to their own hosting solution.
Is it possible via Management Studio 2005 to use the database copy wizard to manage the migration or does that only work for servers on the same network?
If it is not possible could anyone advise me as to the easiest/most foolproof method of handling this transfer.
I'm effectively a SQL/Management studio novice.
TIA
March 5, 2010 at 11:37 am
The best method is to make a full backup of your database. Use SQL Server compression if you have 2008. Send the file to a fileshare on the hosted network and restore the database from the backup file on the hosted SQL Server.
Regards,
Toby
March 5, 2010 at 11:54 am
script all logins
stop the sql server
copy all database files to the new location
attach all new databases in the new location
run 'login script' to restore them
March 5, 2010 at 12:08 pm
Good catch about the logins. Here is a script for the logins and the server level permissions:
Declare @login sysname
,@Password_Hash Varbinary(200)
,@Grant sysname
,@Permission sysname
,@Role sysname
Declare Logins cursor static for
/*select Name
from sys.Server_Principals
where type_desc in ('Windows_Login','Windows_Group')
and Name not in ('BUILTIN\Administrators','NT AUTHORITY\SYSTEM','DATACORE_KC\SQLClusterSvcsAccess'
)
and name not like '%$mssqlserver%'
and name not like 'NT AUTHORITY%'*/
SELECT SUSER_SNAME(sid) account
FROM [YourDatabaseName].sys.database_principals -----------------<-------------change this
Where SUSER_SNAME(sid) IS NOT NULL
Open Logins
While 0=0
Begin
Fetch Next
From Logins
into @login
If @@Fetch_Status <> 0
Break
Print 'If Not Exists(Select * from sys.Server_Principals Where Name = '''+ @login + ''')'
Print 'Create Login [' + @login + '] From Windows'
Declare Server_Permissions cursor static for
Select spe.state_desc, permission_name
From sys.Server_principals spr
join sys.Server_permissions spe
on spr.principal_id = spe.Grantee_principal_id
Where spr.Name = @login
Open Server_Permissions
While 0=0
Begin
Fetch Next
From Server_Permissions
into @Grant ,@Permission
If @@Fetch_Status <> 0
Break
Print @Grant + ' ' + @Permission + ' to [' + @login + ']'
End
Close Server_Permissions
Deallocate Server_Permissions
Declare Roles cursor Static For
Select roles.Name
From Sys.Server_Principals sp
join sys.server_role_members srm
on sp.Principal_ID = srm.Member_Principal_ID
join sys.Server_Principals roles
on srm.role_principal_id = roles.Principal_ID
Where sp.Name = @login
Open Roles
While 0=0
Begin
Fetch Next
From Roles
into @Role
If @@Fetch_Status <> 0
Break
Print 'Exec sp_addsrvrolemember ''' + @login + ''',''' + @Role + ''''
End
Close Roles
Deallocate Roles
End
Close Logins
Deallocate Logins
Declare SQLLogins Cursor Static For
Select Name,Password_Hash
From sys.SQL_Logins
Where Name <> 'sa'
Open SQLLogins
While 0 = 0
Begin
Fetch Next
From SQLLogins
into @login
,@Password_Hash
If @@Fetch_Status <> 0
Break
Print 'If Not Exists(Select * from sys.SQL_Logins Where Name = ''' + @login + ''')'
Print 'Create Login [' + @login + '] with password = '
Print @Password_Hash
Print 'Hashed'
Declare Server_Permissions cursor static for
Select spe.state_desc, permission_name
From sys.Server_principals spr
join sys.Server_permissions spe
on spr.principal_id = spe.Grantee_principal_id
Where spr.Name = @login
Open Server_Permissions
While 0=0
Begin
Fetch Next
From Server_Permissions
into @Grant ,@Permission
If @@Fetch_Status <> 0
Break
Print @Grant + ' ' + @Permission + ' to [' + @login + ']'
End
Close Server_Permissions
Deallocate Server_Permissions
Declare Roles cursor Static For
Select roles.Name
From Sys.Server_Principals sp
join sys.server_role_members srm
on sp.Principal_ID = srm.Member_Principal_ID
join sys.Server_Principals roles
on srm.role_principal_id = roles.Principal_ID
Where sp.Name = @login
Open Roles
While 0=0
Begin
Fetch Next
From Roles
into @Role
If @@Fetch_Status <> 0
Break
Print 'Exec sp_addsrvrolemember ''' + @login + ''',''' + @Role + ''''
End
Close Roles
Deallocate Roles
End
Close SQLLogins
Deallocate SQLLogins
March 5, 2010 at 12:33 pm
you can also use this great microsoft article and scripts for login transfer:
http://support.microsoft.com/default.aspx/kb/918992
how many databases are there to transfer?
March 9, 2010 at 6:06 am
Thanks for all the replies and help.
This is obviously going to more hassle than I thought 🙂
It's only 1 database - but it has over 60 tables (don't ask, I'm just the monkey) and is massive.
I'm sure that much hair pulling and blue language will ensue.
May 13, 2010 at 7:57 am
Ok,
Thanks for the help offered todate but this looks like it's going to be more difficult than I thought.
I have backed up and restored the database onto the new server. However, I am unable to get any of the scripts offered above or from the MS site running.
This is because (I believe) the original server from which we we are migrating is running:
Windows Server 2003 + Management Console 2.0
SQL Enterprise Manager V8
If anyone has any idea how I can transfer the logins/authorisations/permissions etc from this old beast to the SQL Server 2005 of the new hosts I will be hugely appreciative as I really have absolutely no idea.
Thanks again.
May 13, 2010 at 8:02 am
install sql entrpz mngr V9
run sql 2005 install cd, and choose tools only
can be done on your workstation
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply