Could someone point me in the direction of a step-by-step tutorial for
replicating a SQL Server 2000 database as an MSDE database in a seperate
machine, with all tables (including data), users/security, etc.
Currently I'm taking a backup (I can't script the data from Ent. Mgr) and
using oSQL to restore the database to an MSDE install on a machine.
It's done in two steps - installing MSDE and restoring the backup. However,
after the backup has been restored (the user restores correctly) - I am
unable to connect to the database as the default user that I created in the
backup. If I remove the user and re-add him via Ent. Mgr then I can
connect... does anyone know why this is?
First Step - Install MSDE script:
^^^^^^^^^^^^^^^^^^^^^^^^
Set objShell = CreateObject("WScript.Shell")
Set fso = createobject("scripting.filesystemobject")
Set WshNetwork = WScript.CreateObject("WScript.Network")
objShell.Exec(fso.getabsolutepathname(".") & "\setup.exe BLANKSAPWD=1
SAPWD="""" SECURITYMODE=SQL TARGETDIR=""C:\Program Files\Microsoft SQL
Server\Mssql\Binn\"" DATADIR=""C:\Program Files\Microsoft SQL
Server\Mssql\Data\"" instancename=""" & WshNetwork.ComputerName &
"\DatabaseName""")
Second Step - Restore the database backup:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Set objShell = CreateObject("WScript.Shell")
set fso = createobject("scripting.filesystemobject")
Set WshNetwork = WScript.CreateObject("WScript.Network")
currentpath = fso.getabsolutepathname(".")
' Start the msde service
objShell.Exec("net start MSSQL$" & WshNetwork.ComputerName &
"\DatabaseName")
' Make local data and backup directories
'objShell.Exec("md c:\SQL_Databases\DATA")
'objShell.Exec("md c:\SQL_Databases\BACKUP")
' Copy this Repository.bak to the SQL_Databases DatabaseName.bak
fso.CopyFile currentpath &
"\Repository.bak","c:\SQL_Databases\BACKUP\DatabaseName.bak"
' Restore
objShell.Exec(currentpath & "\osql.exe -S" & WshNetwork.ComputerName &
"\DatabaseName -E -Q ""restore database DatabaseName from disk =
'C:\SQL_Databases\BACKUP\DatabaseName.bak'""")
All I want to do is reproduce one of my SQL Server databases AS IS on
several client workstations by way of an MSDE installer.
Any help gratefully appreciated !
Thanks,
Mike
xposted to closely related groups: .sqlserver.msde, .sqlserver.security,
.sqlserver.setup
--
Liddle Feesh
*fap fap fap fap*
<>< <>< <>< <>< ><>
<>< <>< <>< <>< <>< <><
. ,
.:/
. ,,///;, ,;/
. o:::::::;;///
quote:
>::::::::;;\\\
''\\\\'" ';\
';\314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
304692 INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and
http://support.microsoft.com/?id=304692
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:clkWql2wDHA.2900@.cpmsftngxa07.phx.gbl...
quote:
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL
Server
quote:
> http://support.microsoft.com/?id=314546
> 304692 INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP
and
quote:
> http://support.microsoft.com/?id=304692
Hi Kevin and Joseph,
Many thanks for your replies relating to my difficulties with the orphaned
users that arise when restoring a backup on a different server.
Using the sp_change_user_login stored procedure worked perfectly.
Thanks again for your advice, and please accept my apologies for the slight
crosspost.
Best Regards,
Mike
Liddle Feesh
*fap fap fap fap*
<>< <>< <>< <>< ><>
<>< <>< <>< <>< <>< <><
. ,
.:/
. ,,///;, ,;/
. o:::::::;;///
quote:
>::::::::;;\\\
''\\\\'" ';\
';\sql
Banthor wrote: