Wednesday, March 28, 2012

Restore databases into a new installation of SQL Server

Hi,
I want to restore my databases from the old SQL Server installation
into a new installation of SQL Server. My new installation of SQL
Server has different data path from the old installation. In addition,
the data owners of some databases in the old SQL Server installation
are not dbo. I have backups for all the databases in the old
installation SQL Server.
Can anyone tell me how to restore my databases from the old SQL Server
installation into a new installation of SQL Server for my case? (I
want to use new path for data and log files as well as keep the old
data owners in the restored databases.) Do I need to perform restore
operation against my master database in the new installation of SQL
Server? (The data path for a newly created database has been changed
in the new installation of SQL Server.)
My idea is:
1. Create new databases whose names are the same with the databases of
the old SQL installation. Set the new databases with the new data path
I want.
2. Create new login accounts whose user names and passwords are the
same with the old login accounts. Assign appropriate permissions to
the new login accounts (e.g, the data owner for some database).
3. Use Enterprise Manager to perform restore operation against the
newly created databases using the backups from the old SQL Server
installation.
Please advice.
Hai-ChuHave a look at these:
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
Andrew J. Kelly SQL MVP
"Hai-Chu Hsu" <seapearl1023@.ms65.url.com.tw> wrote in message
news:e6c8ee4f.0402281515.23041d51@.posting.google.com...
> Hi,
> I want to restore my databases from the old SQL Server installation
> into a new installation of SQL Server. My new installation of SQL
> Server has different data path from the old installation. In addition,
> the data owners of some databases in the old SQL Server installation
> are not dbo. I have backups for all the databases in the old
> installation SQL Server.
> Can anyone tell me how to restore my databases from the old SQL Server
> installation into a new installation of SQL Server for my case? (I
> want to use new path for data and log files as well as keep the old
> data owners in the restored databases.) Do I need to perform restore
> operation against my master database in the new installation of SQL
> Server? (The data path for a newly created database has been changed
> in the new installation of SQL Server.)
> My idea is:
> 1. Create new databases whose names are the same with the databases of
> the old SQL installation. Set the new databases with the new data path
> I want.
> 2. Create new login accounts whose user names and passwords are the
> same with the old login accounts. Assign appropriate permissions to
> the new login accounts (e.g, the data owner for some database).
> 3. Use Enterprise Manager to perform restore operation against the
> newly created databases using the backups from the old SQL Server
> installation.
> Please advice.
> Hai-Chu

No comments:

Post a Comment