Restore SQL databes to diffrent server using Scripts  
Author Message
zbcong





PostPosted: Mon Feb 13 04:47:08 CST 2006 Top

SQL Server >> Restore SQL databes to diffrent server using Scripts

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_login 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

SQL Server263  
 
 
Ryan





PostPosted: Mon Feb 13 04:47:08 CST 2006 Top

SQL Server >> Restore SQL databes to diffrent server using Scripts 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/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server

--
HTH. Ryan




>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_login 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





PostPosted: Mon Feb 13 09:53:08 CST 2006 Top

SQL Server >> Restore SQL databes to diffrent server using Scripts Many thanks!!

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

Laters

Bill C