July 5, 2012 at 9:58 am
I want to move a database server to server without using Backup and restore?
Example:
I am having a XXX database in ABC server, I want to move this database to XYZ server which do not have the XXX database.
July 5, 2012 at 10:03 am
What is wrong with backup and restore? It by far the easiest and safest way to do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 10:03 am
You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 5, 2012 at 10:26 am
AAKR (7/5/2012)
I want to move a database server to server without using Backup and restore?Example:
I am having a XXX database in ABC server, I want to move this database to XYZ server which do not have the XXX database.
the easiest way is to
take the database offline
copy (don't move) the files to the new server
Bring original database online
attach the db files on the target server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 5, 2012 at 10:30 am
SQLRNNR (7/5/2012)
You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.
The backup piece of this was pretty much my point. You need to backup your database first. This is usually the reason this type of request comes up because the backup process can cause blocking and such. Since this has to be done first anyway you really don't gain much at that point by copying the files and attaching. You might as well just copy the bak file and restore.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 5, 2012 at 10:48 am
Sean Lange (7/5/2012)
SQLRNNR (7/5/2012)
You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.The backup piece of this was pretty much my point. You need to backup your database first. This is usually the reason this type of request comes up because the backup process can cause blocking and such. Since this has to be done first anyway you really don't gain much at that point by copying the files and attaching. You might as well just copy the bak file and restore.
I didn't see your post. Our posts hit simultaneously 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 5, 2012 at 11:13 am
Thank you all of you guys.
* copy files from server to server it takes more time...
I heard an option with using of Sanpshot replication we can create the database.
any one of you know this option how to we work it out
July 5, 2012 at 11:27 am
AAKR (7/5/2012)
Thank you all of you guys.* copy files from server to server it takes more time...
I heard an option with using of Sanpshot replication we can create the database.
any one of you know this option how to we work it out
Do you actually want to move the database or just copy it? Is this a "one off" or do you need to do this on a regular basis? Are both systems on the same SAN by any chance?
--Jeff Moden
July 5, 2012 at 11:37 am
AAKR (7/5/2012)
* copy files from server to server it takes more time...
Yes it does, but if you want to move or copy a DB from one server to another, there's just about no way to get around the fact that you are going to have to copy the data over. Whether that's in a backup file, the database files or snapshot replication's snapshot files, the data has to move.
Safest and simplest - backup the DB, copy the backup, restore on other server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2012 at 12:20 pm
AAKR (7/5/2012)
Thank you all of you guys.* copy files from server to server it takes more time...
I heard an option with using of Sanpshot replication we can create the database.
any one of you know this option how to we work it out
What is it you are trying to accomplish? If you can outline the goal and the requirements, I am sure we can come up with alternatives that would meet the requirements.
You asked about SAN snapshot - which is an option if your volumes were set up that way to begin with and will be dependent on the SAN you are using. But, that may not be the best option - again, depending on the requirements.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 5, 2012 at 2:01 pm
You're generally stuck with 2 options mentioned.
Option1
Detach or offline database (generally almost instantaneous)
Copy files (takes time)
Reattach files (generally almost instantaneous)
Option2
Take compressed backup (can take time)
Copy compressed back (usually quick)
Restore backup (generally takes same time as backup)
Depending upon your database size and network speed one may be quicker than the other.
In my experience on a fast network detach\attach is usually quicker. But depends on what state you want source db to be left in.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 5, 2012 at 2:13 pm
What about taking compressed backup over network?
July 5, 2012 at 2:16 pm
Daxesh Patel (7/5/2012)
What about taking compressed backup over network?
If the network is stable, it would work. I personally prefer to do backups to a local resource as even a minor network issue could cause a backup failure. If you have a good fast network, however, I would consider doing the restore of a compressed backup over the network. SQL Server seems to be more tolerant of minor network issues during a restore than a backup.
July 5, 2012 at 2:23 pm
SQL Server seems to be more tolerant of minor network issues during a restore than a backup.
100% agree, thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply