SQL server backup
i have a windows server 2003 with sql server 2005 and i want to move ths database to new server and my problem how i can move the old database to the new one because in the same time i received each time new query from other side ( internet application - and from a mobile operator who store sms msg on my database) so i want to move it to my new server and copy all old database and keep it running so what should i do ?
Lee
What you're asking is similar to telling a mechanic that you wish to keep driving a car while he changes the engine. You will have to take it offline while you do it.
ASKER
mmm ok can u tell me how i can backup it :D i have a sql server and i want ot move all database to another server ? because this is my first time .. i use sql server
my basic knowledge it to dump the database than import it to my new sql sever:)
but i don\t know how to do it in sql sever
my basic knowledge it to dump the database than import it to my new sql sever:)
but i don\t know how to do it in sql sever
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no :D i don't know how to restore it ? can u tell me how and thank u a lottttt :) , the backup work if i move from sql server 200 or 2005 to sql sever 2008 ?
Well, you can do a backup without necessarily stopping your services therefore no seriously impacting your clients depending on which approach you take.
Do your backup as already described above but do not stop the services or application.
Restore the backup to the new server. Now at this point, you have two approaches.
1.
You can mirror the databases which means there will be no downtime for your applications. Once the databases are mirrored, you can failover to the new server, reconnect your services/app to the new server and finally remove the mirror then take the old server offline.
2. The second way you have stop your services but for a much shorter period.
Restore the full backup to the new server with norecovery. Stop services and application, take tail transaction log backup which will take a very short time. Restore the transaction log on the new server. Then reconnect your services/app to the new server.
With this approach, you will minimize the downtime if any of your service
Do your backup as already described above but do not stop the services or application.
Restore the backup to the new server. Now at this point, you have two approaches.
1.
You can mirror the databases which means there will be no downtime for your applications. Once the databases are mirrored, you can failover to the new server, reconnect your services/app to the new server and finally remove the mirror then take the old server offline.
2. The second way you have stop your services but for a much shorter period.
Restore the full backup to the new server with norecovery. Stop services and application, take tail transaction log backup which will take a very short time. Restore the transaction log on the new server. Then reconnect your services/app to the new server.
With this approach, you will minimize the downtime if any of your service
Yes it will work if you restore to a later version of the database as the database compatibility level will stay the same as the one you have backed up.
You cannot restore (very easily) from a later version to an earlier version. Assuming you restore to SQL 2005 again.
To restore if you'll need to know and understand a few things. Is the current database an Express database? Is the target database an Express database? Realistically you should post another question but in simple steps you need to create a new blank database on the new server of the same name as the one you backed up. Once created, ensure the recovery model is set correctly. You will need to read up on those yourself so sarch Microsofts website for answers.
Once created, right mouse click and choose Tasks -> Restore -> Database and then choose from device, find your .bak file which you must have on the server, tick the box, click options and choose Overwrite.
You cannot restore (very easily) from a later version to an earlier version. Assuming you restore to SQL 2005 again.
To restore if you'll need to know and understand a few things. Is the current database an Express database? Is the target database an Express database? Realistically you should post another question but in simple steps you need to create a new blank database on the new server of the same name as the one you backed up. Once created, ensure the recovery model is set correctly. You will need to read up on those yourself so sarch Microsofts website for answers.
Once created, right mouse click and choose Tasks -> Restore -> Database and then choose from device, find your .bak file which you must have on the server, tick the box, click options and choose Overwrite.
ASKER
thank you ewangoya for your answer please can u tell me who i can mirror the database to new server ? i will put it as a new question and u will answer to me ??? waiting your reply
When restoring SQL will use the same location it had from the backed up database so if the path is different from the default you will need to choose a location for the mdf and ldf files.
ASKER
thanks can anyone tell me about mirroring ? i like it ? can anyone answer to me about it :)