MS SQL Server - Restore SQL databes to diffrent server using Scripts

Author

Restore SQL databes to diffrent server using Scripts

Williamc_sircon

2006-02-13, 7:23 am

I hope someone here can point me in the right direction.

We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.

We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.

We use the " sp_change_users_logi
n auto_fix, <username>" to recreate the
link between the user and its database.

Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.

Any help will be gratefully received!

Bill C

Ryan

2006-02-13, 7:23 am

You could consider creating a DTS Package to do this for you. Here are some
articles that will help :-

http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server

--
HTH. Ryan

"Williamc_sircon" <williamc@sircon.co.uk> wrote in message
news:1139826814.497790.140590@f14g2000cwb.googlegroups.com...
>I hope someone here can point me in the right direction.
>
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
>
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
>
> We use the " sp_change_users_logi
n auto_fix, <username>" to recreate the
> link between the user and its database.
>
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
>
> Any help will be gratefully received!
>
> Bill C
>

Williamc_sircon

2006-02-13, 11:23 am

Many thanks!!

These will keep me going for a while by the looks of it!!

Laters

Bill C

Sponsored Links