473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Best practice for database migration

Can you tell me what is the best practice for SQL database migration from
one DB server, to another one, new DB server. The old DB server will be
removed.

1. Backup from old and restore all databases on the new server
2. Export data and copy/import on a new server
3. Something else...

Thanks in advance for any good advice...

Regards

Nov 17 '06 #1
2 2479
BD

RONIN wrote:
Can you tell me what is the best practice for SQL database migration from
one DB server, to another one, new DB server. The old DB server will be
removed.

1. Backup from old and restore all databases on the new server
2. Export data and copy/import on a new server
3. Something else...

Thanks in advance for any good advice...

Regards
Before exporting, create a SQL script containing all users and roles.

SQL Server's database user IDs are 'connected' to an OS logon or a SQL
Server ID. But in fact it's the under-the-hood SID that it is linked
to.

So when you restore the db on the new server, and check the Users
container, you will likely see some UserIDs without Logons. They're
effectively Orphaned.

You'll need to remove those users from the db, and re-add them. Either
manually, or with a script generated on the source db.

In my case, I have a stored procedure which drops all users that lack a
corresponding Logon.

My 2 cents...

BD

Nov 17 '06 #2
I would "Detach" the database from the old server and "attach" the databases
on the new server. But as BD explained, you still have to address the users
of the database (ie security).

Oscar...

"RONIN" <ro********@yahoo.comwrote in message
news:ej**********@ss408.t-com.hr...
Can you tell me what is the best practice for SQL database migration from
one DB server, to another one, new DB server. The old DB server will be
removed.

1. Backup from old and restore all databases on the new server
2. Export data and copy/import on a new server
3. Something else...

Thanks in advance for any good advice...

Regards

Nov 18 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
11
by: Jep | last post by:
Any help or advice appreciated. We have just installed and configured a new DB2 v.8.1 with latest FixPack. Just a few days later it dies and now we have a DB that is totally unusable. - - - -...
5
by: allenj | last post by:
DB2 UDB 7.2 WSE Fixpak 9 Linux Red Hat 7.3 I will soon be upgrading my DB2 v7 database to v8, and at the same time will be upgrading the server hardware (ie a brand new server). What is the...
17
by: | last post by:
I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best practice. I don't want the user to have access to...
4
by: a | last post by:
Hey gang, quick question. Given an app where many classes will be acessing a database, what is the best way to go? a. A function that returns a connection string to each class, and lets them...
20
by: Keith G. Murphy | last post by:
I'm trying to get a feel for what most people are doing or consider best practice. Given a mod_perl application talking to a PostgreSQL database on the same host, where different users are...
4
by: Collin Peters | last post by:
I have searched the Internet... but haven't found much relating to this. I am wondering on what the best practices are for migrating a developmemnt database to a release database. Here is the...
16
by: Rex | last post by:
Hi All - I have a question that I think MIGHT be of interest to a number of us developers. I am somewhat new to VIsual Studio 2005 but not new to VB. I am looking for ideas about quick and...
2
by: sabbadin12 | last post by:
Hi, I'm going to work on an application that uses a postgreSQL database so that it can uses SQLServer 2005. I think I solved most problems on the programming side, but I still have some doubts...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.