SQL 2k5-32bit >SQL 2k5-64bit

1 Dec 2008 9:49 PM

John Holt

Many times I have installed SQL to a new server, shutdown the service and
copied over the entire MSSQL\Data folder, master,msdb and all, start the
server and bam!, just sp_dropserver and sp_addserver and change the
servername in the jobs table and everything is on the new server without a
problem.

Anyone out there see any reason that method would not work coping MSQL\Data
folder FROM a 32bit server TO a 64bit server?
Or.
Is there another way to get everything on a 32bit sql server onto a 64bit
sql server? Logins, passwords and all, intact.

Thanks,
John

3 Dec 2008 3:19 PM

Joe

John,
I asked this exact question to a MS SQL Server Architect 2 years ago. He
had a lot to say but this was my take away. If it was his production
environment to support he would not do this.
Please be aware you can script jobs to move from server to server, just make
sure the add server to job step is referencing the correct server. In
addition here is a script to caputure logins and dncrypted passwords. Make
sure your text output is set to the max size so no output is truncated.

select 'if not exists (select * from sys.server_principals where name = '''
+ p.name + ''') ' + char(13) + char(10) + char(9) +
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' +
master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
   'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
   case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
   'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, '
else 'OFF, ' end +
   case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else
'' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' +
p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

I forgoit where I got this but credit should go to some cause I have used it
many times and it works.

Show quoteHide quote

"John Holt" wrote:

> Many times I have installed SQL to a new server, shutdown the service and
> copied over the entire MSSQL\Data folder, master,msdb and all, start the
> server and bam!, just sp_dropserver and sp_addserver and change the
> servername in the jobs table and everything is on the new server without a
> problem.
>
> Anyone out there see any reason that method would not work coping MSQL\Data
> folder FROM a 32bit server TO a 64bit server?
> Or.
> Is there another way to get everything on a 32bit sql server onto a 64bit
> sql server? Logins, passwords and all, intact.
>
> Thanks,
> John
>
>

3 Dec 2008 4:27 PM

John Holt

Joe,
Thanks for the reply and the code. :)
I'll verify it function on a junk sql server.

Show quoteHide quote

"Joe" <J**@discussions.microsoft.com> wrote in message
news:7E09979D-3719-44B3-A6F8-1EA039E5FB96@microsoft.com...
> John,
> I asked this exact question to a MS SQL Server Architect 2 years ago. He
> had a lot to say but this was my take away. If it was his production
> environment to support he would not do this.
> Please be aware you can script jobs to move from server to server, just
> make
> sure the add server to job step is referencing the correct server. In
> addition here is a script to caputure logins and dncrypted passwords.
> Make
> sure your text output is set to the max size so no output is truncated.
>
> select 'if not exists (select * from sys.server_principals where name =
> '''
> + p.name + ''') ' + char(13) + char(10) + char(9) +
> 'create login [' + p.name + '] ' +
> case when p.type in('U','G') then 'from windows ' else '' end +
> 'with ' +
> case when p.type = 'S' then 'password = ' +
> master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
>   'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = '
> +
>   case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
>   'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, '
> else 'OFF, ' end +
>   case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else
> '' end
> else '' end +
> 'default_database = ' + p.default_database_name +
> case when len(p.default_language_name) > 0 then ', default_language = ' +
> p.default_language_name else '' end
> from sys.server_principals p
> left join sys.sql_logins l
> on p.principal_id = l.principal_id
> left join sys.credentials c
> on l.credential_id = c.credential_id
> where p.type in('S','U','G')
> and p.name <> 'sa'
>
> I forgoit where I got this but credit should go to some cause I have used
> it
> many times and it works.
>
>
>
> "John Holt" wrote:
>
>> Many times I have installed SQL to a new server, shutdown the service and
>> copied over the entire MSSQL\Data folder, master,msdb and all, start the
>> server and bam!, just sp_dropserver and sp_addserver and change the
>> servername in the jobs table and everything is on the new server without
>> a
>> problem.
>>
>> Anyone out there see any reason that method would not work coping
>> MSQL\Data
>> folder FROM a 32bit server TO a 64bit server?
>> Or.
>> Is there another way to get everything on a 32bit sql server onto a 64bit
>> sql server? Logins, passwords and all, intact.
>>
>> Thanks,
>> John
>>
>>

4 Dec 2008 8:44 AM

Uri Dimant

John
Why do not you want to install SQL Server 64 bit and restore all databases
to from SQL Server 32 bit (or even attach/detach)??

Show quoteHide quote

"John Holt" <jo***@regionv.k12.mn.us> wrote in message
news:uxxy46$UJHA.4508@TK2MSFTNGP05.phx.gbl...
> Many times I have installed SQL to a new server, shutdown the service and
> copied over the entire MSSQL\Data folder, master,msdb and all, start the
> server and bam!, just sp_dropserver and sp_addserver and change the
> servername in the jobs table and everything is on the new server without a
> problem.
>
> Anyone out there see any reason that method would not work coping
> MSQL\Data folder FROM a 32bit server TO a 64bit server?
> Or.
> Is there another way to get everything on a 32bit sql server onto a 64bit
> sql server? Logins, passwords and all, intact.
>
> Thanks,
> John

4 Dec 2008 3:55 PM

John Holt

Uri,
I replace the SQL servers every few years and it's kinda due. Never hurts
to get a little more power, speed... I thought this time I would go with
64bit and up the ram to at least 16gb. With 150 databases on A server with
4gb ram, it gets kinda noticable when they all start doing payroll runs or
running big activity report. Usually adding ram does make things a bit
better but with 32bit, 4gb is about it. Besides, not going to be anymore
32bit Windows anyway, may as well get on with it. :)

Show quoteHide quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:%23XtIkxeVJHA.3964@TK2MSFTNGP03.phx.gbl...
> John
> Why do not you want to install SQL Server 64 bit and restore all databases
> to from SQL Server 32 bit (or even attach/detach)??
>
>
> "John Holt" <jo***@regionv.k12.mn.us> wrote in message
> news:uxxy46$UJHA.4508@TK2MSFTNGP05.phx.gbl...
>> Many times I have installed SQL to a new server, shutdown the service and
>> copied over the entire MSSQL\Data folder, master,msdb and all, start the
>> server and bam!, just sp_dropserver and sp_addserver and change the
>> servername in the jobs table and everything is on the new server without
>> a problem.
>>
>> Anyone out there see any reason that method would not work coping
>> MSQL\Data folder FROM a 32bit server TO a 64bit server?
>> Or.
>> Is there another way to get everything on a 32bit sql server onto a 64bit
>> sql server? Logins, passwords and all, intact.
>>
>> Thanks,
>> John
>
>