Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-14 : 03:47:16
|
I have a database at work. Sometimes I take it home to work on the weekend. For a while, what I have been doing is stopping sql server. Copying the DB to a portable drive. Going home. Created a DB with the same name. Stopping SQL Server. Copying the Work DB over the empty one at home I have just created and then starting SQL Server. Not the way its meant to be done I'm sure. If I am right, one of the bad things that happens as a consequence is that the master db is completely unaware of the new database and doesnt have all the system tables that is would otherwise have. Am I right?What is the best way to get done what I am trying to do? |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-14 : 05:56:27
|
instead of copying the file, create a backup of the database --------------------keeping it simple... |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-14 : 07:13:52
|
Also you could use:sp_detach_db / sp_attach_dbrockmoose |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-14 : 12:27:50
|
jen, I've had trouble in the past backing up a database on one system and trying to restore it to another. Because the target db does not have the backup in its history, you can't navigate to it to restore from it. What am I missing? |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-14 : 12:30:35
|
rockmoose, sp_attach_db looks like the business! thanks |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-15 : 03:07:08
|
quote: Originally posted by coolerbob jen, I've had trouble in the past backing up a database on one system and trying to restore it to another. Because the target db does not have the backup in its history, you can't navigate to it to restore from it. What am I missing?
what do you mean, you can't navigate to it?--------------------keeping it simple... |
|
|
martinch
Starting Member
35 Posts |
Posted - 2005-03-16 : 05:05:52
|
quote: Originally posted by coolerbob jen, I've had trouble in the past backing up a database on one system and trying to restore it to another. Because the target db does not have the backup in its history, you can't navigate to it to restore from it. What am I missing?
If I'm understanding you correctly, what you need to do is (in the Restore window in Enterprise Manager) select the "From Device" radio button, click "Select Devices...", then select the "Disk" radio button, and click "Add...", and then point it to the backup file using the file browser.Hope that helps! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 07:57:57
|
Even better, skip Enterprise Manager entirely, and use Query Analyzer:RESTORE DATABASE myDB FROM DISK='\\uncpath\to\backupfile.bak' |
|
|
|
|
|
|
|