| Author |
Topic |
|
jpl458
Starting Member
7 Posts |
 Posted - 2010-11-19 : 15:42:26
|
| I have an SQL Server 2008 database on one computer, and want to have another copy of the database on another computer that has Sql Server 2008 R2, but I don't quite know how to get a copy over there. Detach and attach seem to be the way, but I want to have both databases working, and I am not sure detach would leave the original in good working order. Any help will be appreciated.Thanks in advance.jpl |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 15:49:36
|
| It should be ok but I would do a full backup first.When you've done that you might as well transfer the backup and restore it rather than detach and copy - means the source database doesn't need to be taken off-line.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jpl458
Starting Member
7 Posts |
Posted - 2010-11-21 : 11:52:37
|
| I tried backup and restore by creating a new database with exactly the same name as the database on the old system. When I tried to restore to the new database I kept getting an error that stated that the two databases were not the same. Again, any help would be appreciated. I am not comfortable using attach and detach since I don't know exactly what detach does - is it like a copy or a move?Thanks in advance, again.jpl |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-21 : 12:48:45
|
| Are your two servers the same version and service pack?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jpl458
Starting Member
7 Posts |
Posted - 2010-11-21 : 14:07:24
|
| The original is on Windows XP using SQL Server 2008 and I want to have the same database on Windows 7 with Sql Server 2008 R2 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-21 : 14:52:33
|
| >> I tried backup and restore by creating a new database with exactly the same name as the database on the old systemDon't create the new database - drop the one you have created.The restore will create the database for you.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-11-21 : 15:23:47
|
quote:
Originally posted by jpl458
When I tried to restore to the new database I kept getting an error that stated that the two databases were not the same.
Post the error.quote:
Originally posted by jpl458
I am not comfortable using attach and detach since I don't know exactly what detach does - is it like a copy or a move?
It just releases the database files from SQL Server. After a database has been detached, you can then copy/move the files. You also can simply just reattach it after you are done to make the database available again in the original SQL Server. The major difference between backup/restore and detach/attach is that backup is an online operation. The database is available during the backup process. With detach though, it requires downtime.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jpl458
Starting Member
7 Posts |
Posted - 2010-11-21 : 18:51:30
|
| What do you mean by "releases the database files from SQL Server"? Does that mean they no longer exist in SQL Server? I guessing there is more to it than move or copy. If they no longer exist then hoe do I get them back so I have two versions, the old version becoming a sand box.I've read through a lot of web stuff, and have a book on Sql Server 2005 that doesn't explain anything. Probably time for a new book.Thanks for the quick responses and your time.jpl |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-21 : 19:23:00
|
| >> Does that mean they no longer exist in SQL Server?Yes.A database is held in files (data and log - at least one of each a 'mdf and 'ldf). When the server is running these are locked and updated by the server.If the server is stopped or the database detached then they are just files. When the server is started again the location is held in the master database and the database recovered from the files (in fact the server goes through a process very similar to attaching all the databases from files). When you do a manual attach you have to specify the file location.A common mistake is to try to backup the files while the server is still running which can end up with useless file copies.A backup does not remove the database from the server but just backs up the necessary data and log entries to a single file while it's still on-line. When you do a restore the data and log files are recreated from the backup file and the database attached.I don't like doing a detach because it takes the database off line and the attach might not work - should be similar to a restart of the server but you never know.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jpl458
Starting Member
7 Posts |
Posted - 2010-11-21 : 22:08:49
|
| Thanks for the explanation, but how would you restore a database to an empty SQL Server. I tried creating a database with the same name as the one that I backed up. I tried that and it said it was not the same database. Is that method possible?By your description, I don't think I want to mess with attach and detach till I learn more.Again thanks a ton for the information.jpl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Attach and detach