| Author |
Topic |
|
MaKKrO
Starting Member
8 Posts |
 Posted - 2008-11-13 : 03:37:12
|
| Hi,I am planning to move all the databases sitting on a SQL2005 to another SQL2005.I was just wondering the impact of detaching a DB when live.Do you have any idea what the consequences are ?Is it safe ?THank you. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-13 : 04:41:33
|
| You can't detach a database until you close all of active connections. When you will click on detach database option it will show you number of active connections and you have to kill these process first. So no need to worry. |
 |
|
|
MaKKrO
Starting Member
8 Posts |
Posted - 2008-11-13 : 04:57:47
|
| Which means that I can't do a detach/attach on a 24/7 live DB.Any other idea ?I have tried the Backup & Restore but it won't work. The .bak file has the old backup as well, I've tried to tick restore them one by one - no chance !Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:00:59
|
| you're problem with backup is what? create a backup into a new file. don't append it to the old one.backup is ideal for this scenario._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
MaKKrO
Starting Member
8 Posts |
Posted - 2008-11-13 : 06:14:18
|
| Yeah, I thought it was but...If I try to append the backup to new file, it goes til 80% then fails.Here is the error in the event viewer : ckupDiskFile::CreateMedia: Backup device 'C:\Argo.bak' failed to create. Operating system error 5(error not found).I am logged in with the local admin.If I append the backup to an file already there, it works fine. |
|
|
|
MaKKrO
Starting Member
8 Posts |
Posted - 2008-11-13 : 06:24:53
|
| Here is what I've tried with SQLCMD.1> use master2> goChanged database context to 'master'.1> :connect 127.0.0.1Sqlcmd: Successfully connected to server '127.0.0.1'.1> BACKUP DATABASE [Argo_FHWW] TO DISK = "c:\Argo.bak"2> goMsg 3201, Level 16, State 1, Server 133467-APP1, Line 1Cannot open backup device 'c:\Argo.bak'. Operating system error 5(error not found).Msg 3013, Level 16, State 1, Server 133467-APP1, Line 1BACKUP DATABASE is terminating abnormally.1> |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:27:51
|
| it means that the accoutn under which the sql server service runs under doesn't have permissions to write to the folder you're trying to write to.choose a folder that you have permissions too._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
MaKKrO
Starting Member
8 Posts |
Posted - 2008-11-13 : 06:36:01
|
| I am using the local admin account.This account does have permission to write anywhere on the C drive.When i open M$ SQL Server Managment Studio, I am using the Windows Auth mode and the same account to login. |
|
|
|
MaKKrO
Starting Member
8 Posts |
|
|
MaKKrO
Starting Member
8 Posts |
Posted - 2008-11-13 : 06:47:42
|
| Do I need to have the database created to restore it ?Or will the restore create it ?Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 07:00:00
|
| you specify the databse name to which you with to restore to. db doesn't have to be created before._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
MaKKrO
Starting Member
8 Posts |
|
|
|
Attach/Detach Live database