Migrating database fron one server to another

[jannat]

Can someone help me please?? I have to migrate the whole contents of a server to a new server, they both SQL 7.0. and I need to migrate not only the database but everything else running on the server... how do I do that?

Thanks in advance.

[Jun]

Hi Jannat,

There are several ways to move databases including all tghe objects from one to another. I can show you two of the ways to do that.

1) Use the DTS's "Transfer SQL server Object Task" to transfer some or all the database objects from one SQL Server to another if you are familiar with DTS. Check the SQL BOL for "DTS" -> "Package Task" -> "Transfer SQL server Object Task" for more detailed info.

2) Use Backup and Restore to move or copy databases from one server to another. I have five SQL servers and need to move databases from server to server. Here is one of sample T-SQL script you can try.

/************************************************** *********************************
Make a copy of a database using BACKUP and RESTORE between SQL Servers
This example uses both the BACKUP and RESTORE statements
to make a copy of Test database. The MOVE statement causes
the data and log file to be restored to the specified locations.
The RESTORE FILELISTONLY statement is used to determine the number and names
of the files in the database being restored. The new copy of the
database is named TestDB. For more information, see RESTORE FILELISTONLY.


Three Steps:
1) Back up the database on the Server (source Server) that contains the database
2) Go to the Target Server which will accept the database and check the logical
name of the data & log files
3) Restore the database with the "WITH MOVE" clause

************************************************** *************************************/

-- This is an example of the whole process:

/* Step 1) Back up the database on the Source Server */

-- Use T-SQL or EM to backup the database to a remote location, e.g.
BACKUP DATABASE _HTH_OPERATIONS
TO DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 2) Go to the Target Server and check the data & log files on the Target Server **/
RESTORE FILELISTONLY
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 3) Restore the database with the "WITH MOVE" clause on Target Server **/
RESTORE DATABASE _HTH_OPERATIONS
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'
WITH MOVE 'DB1_data' TO '\SQL2sql_dataMSSQL7dataDB1_data.mdf',
MOVE 'DB1_log' TO '\SQL2logfilesDB1_log.ldf'
GO
/************************************************** *****/

In this case, I put the data file and log file in two different directory on the target server (SQL2). You can change the database name and network locations to meet you requirement. If you have multiple databases to move, you just need to make copies of the above T-SQL statements and create a big T-SQL scripts to run at one time.

Hope this will work for you!

Jun


------------
jannat at 9/7/01 3:06:00 PM

Can someone help me please?? I have to migrate the whole contents of a server to a new server, they both SQL 7.0. and I need to migrate not only the database but everything else running on the server... how do I do that?

Thanks in advance.

[David Westmore]

It may sound like a stupid suggestion, but when I did this a few months ago, I decided to give the new server THE SAME NAME as the old one (obviously I had to rename the old one first).

I then restored everything from backups, INCLUDING the MSDB database, which probably contains a lot of things which you'll need to migrate (jobs, DTS packages and so on). What I did was to create each database as empty and then restore from the backup taken from the old server, using the "Force over existing database" option.

It all went surprisingly well...

Regards

David Westmore





------------
Jun at 9/8/01 7:04:53 PM

Hi Jannat,

There are several ways to move databases including all tghe objects from one to another. I can show you two of the ways to do that.

1) Use the DTS's "Transfer SQL server Object Task" to transfer some or all the database objects from one SQL Server to another if you are familiar with DTS. Check the SQL BOL for "DTS" -> "Package Task" -> "Transfer SQL server Object Task" for more detailed info.

2) Use Backup and Restore to move or copy databases from one server to another. I have five SQL servers and need to move databases from server to server. Here is one of sample T-SQL script you can try.

/************************************************** *********************************
Make a copy of a database using BACKUP and RESTORE between SQL Servers
This example uses both the BACKUP and RESTORE statements
to make a copy of Test database. The MOVE statement causes
the data and log file to be restored to the specified locations.
The RESTORE FILELISTONLY statement is used to determine the number and names
of the files in the database being restored. The new copy of the
database is named TestDB. For more information, see RESTORE FILELISTONLY.


Three Steps:
1) Back up the database on the Server (source Server) that contains the database
2) Go to the Target Server which will accept the database and check the logical
name of the data & log files
3) Restore the database with the "WITH MOVE" clause

************************************************** *************************************/

-- This is an example of the whole process:

/* Step 1) Back up the database on the Source Server */

-- Use T-SQL or EM to backup the database to a remote location, e.g.
BACKUP DATABASE _HTH_OPERATIONS
TO DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 2) Go to the Target Server and check the data & log files on the Target Server **/
RESTORE FILELISTONLY
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 3) Restore the database with the "WITH MOVE" clause on Target Server **/
RESTORE DATABASE _HTH_OPERATIONS
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'
WITH MOVE 'DB1_data' TO '\SQL2sql_dataMSSQL7dataDB1_data.mdf',
MOVE 'DB1_log' TO '\SQL2logfilesDB1_log.ldf'
GO
/************************************************** *****/

In this case, I put the data file and log file in two different directory on the target server (SQL2). You can change the database name and network locations to meet you requirement. If you have multiple databases to move, you just need to make copies of the above T-SQL statements and create a big T-SQL scripts to run at one time.

Hope this will work for you!

Jun


------------
jannat at 9/7/01 3:06:00 PM

Can someone help me please?? I have to migrate the whole contents of a server to a new server, they both SQL 7.0. and I need to migrate not only the database but everything else running on the server... how do I do that?

Thanks in advance.

[Greg]

One thing to be aware of - especially if moving databases using Backup & Restore the potential for login and database user accounts.

Say you have 20 users who have SQL_Server logins on Server_A and have corresponding database user accounts in database_1. You backup database_1 on Server_A and do a RESTORE WITH MOVE onto Server_B.

If you have not first ensured that those 20 logins have been moved across you will have problems. The database user entries will be added to the sysusers table in the newly restored database_1 (on Server_B) but will not be accessible from EM. Even if you create those logins they still will not be able to access database_1. If you get to this point your only option will be to enable updating of system tables (not reccommended) to remove the "orpaned" database users then recreate the logins and grant database access from scratch.

Hope this heklps,

. . Greg


------------
David Westmore at 9/10/01 7:01:14 AM

It may sound like a stupid suggestion, but when I did this a few months ago, I decided to give the new server THE SAME NAME as the old one (obviously I had to rename the old one first).

I then restored everything from backups, INCLUDING the MSDB database, which probably contains a lot of things which you'll need to migrate (jobs, DTS packages and so on). What I did was to create each database as empty and then restore from the backup taken from the old server, using the "Force over existing database" option.

It all went surprisingly well...

Regards

David Westmore





------------
Jun at 9/8/01 7:04:53 PM

Hi Jannat,

There are several ways to move databases including all tghe objects from one to another. I can show you two of the ways to do that.

1) Use the DTS's "Transfer SQL server Object Task" to transfer some or all the database objects from one SQL Server to another if you are familiar with DTS. Check the SQL BOL for "DTS" -> "Package Task" -> "Transfer SQL server Object Task" for more detailed info.

2) Use Backup and Restore to move or copy databases from one server to another. I have five SQL servers and need to move databases from server to server. Here is one of sample T-SQL script you can try.

/************************************************** *********************************
Make a copy of a database using BACKUP and RESTORE between SQL Servers
This example uses both the BACKUP and RESTORE statements
to make a copy of Test database. The MOVE statement causes
the data and log file to be restored to the specified locations.
The RESTORE FILELISTONLY statement is used to determine the number and names
of the files in the database being restored. The new copy of the
database is named TestDB. For more information, see RESTORE FILELISTONLY.


Three Steps:
1) Back up the database on the Server (source Server) that contains the database
2) Go to the Target Server which will accept the database and check the logical
name of the data & log files
3) Restore the database with the "WITH MOVE" clause

************************************************** *************************************/

-- This is an example of the whole process:

/* Step 1) Back up the database on the Source Server */

-- Use T-SQL or EM to backup the database to a remote location, e.g.
BACKUP DATABASE _HTH_OPERATIONS
TO DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 2) Go to the Target Server and check the data & log files on the Target Server **/
RESTORE FILELISTONLY
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'

/** Step 3) Restore the database with the "WITH MOVE" clause on Target Server **/
RESTORE DATABASE _HTH_OPERATIONS
FROM DISK = '\Sql1SQL BackupsHourlyDB1.BAK'
WITH MOVE 'DB1_data' TO '\SQL2sql_dataMSSQL7dataDB1_data.mdf',
MOVE 'DB1_log' TO '\SQL2logfilesDB1_log.ldf'
GO
/************************************************** *****/

In this case, I put the data file and log file in two different directory on the target server (SQL2). You can change the database name and network locations to meet you requirement. If you have multiple databases to move, you just need to make copies of the above T-SQL statements and create a big T-SQL scripts to run at one time.

Hope this will work for you!

Jun


------------
jannat at 9/7/01 3:06:00 PM

Can someone help me please?? I have to migrate the whole contents of a server to a new server, they both SQL 7.0. and I need to migrate not only the database but everything else running on the server... how do I do that?

Thanks in advance.