[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL server backup

Posted on 2012-09-19
Medium Priority
Last Modified: 2012-09-19
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 ?
Question by:afifosh
  • 4
  • 4
LVL 25

Expert Comment

by:Lee Savidge
ID: 38412710
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.

Author Comment

ID: 38412736
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
LVL 25

Accepted Solution

Lee Savidge earned 800 total points
ID: 38412783

1. Stop all services that write the database. If they're coming in via a web application, then stop IIS.
2. Open SQL Management Studio
3. Connect to the database
4. On the left hand side of the screen should be the object explorer. If not, press F8
5. Expand Databases
6. Right mouse click on the database you wish to back up
7. Choose Tasks -> Back Up
8. At the top of the popup, ensure the back up type is Full
9. Near the bottom right there is a button that says remove. Remove everything in that bottom text box
10. Choose Back up destination To Disk
11. Click Add
12. In the pop up, click on the ... button to choose where you want the back up to go. I'd choose the desktop so you can find it. When you expand the tree to the Desktop folder, ensure you click and highlight it and in the filename box give the back up file a name, for example: 20120919-MyDatabase.bak - The format of the name is your choice but ALWAYS ensure you put a .bak extension as SQL doesn't put it there by default
13. Press Ok
14. Press Ok
15. Top left choose Options
16. Choose Overwrite all existing backup sets
17. Press Ok

At this point it will start creating the backup file.

Zip the file as it will be quite large and then move the file to the new server and restore. Do you know how to restore?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Closing Comment

ID: 38412811
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 ?
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 38412820
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.
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
LVL 25

Expert Comment

by:Lee Savidge
ID: 38412842
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.

Author Comment

ID: 38412843
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
LVL 25

Expert Comment

by:Lee Savidge
ID: 38412845
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.

Author Comment

ID: 38412870
thanks can anyone tell me about mirroring ? i like it ? can anyone answer to me about it :)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question