I have a vendor who uses sql server 2005 but we are currently using 2000 sp4
.
He has sent me a mdf, a ldf file and a bak file. I would like to get this
on my ss2k server. What is the best way to do this?
I've tried restoring the bak file but it tells me that there is a
compatibility problem.
I've tried to create a new db, detach and reattach the mdf and ldf file that
the vendor sent and I get the following error:
Error 602: Could not find row in sysindexes for database ID XX, object ID
X, index ID X. Run DBCC Checktable on sysindexes.
Does this mean that I need to run DBCC checktable on sysindexes on the
master db?
I ran this on my ss2k machine. Should the vendor run the dbcc
checktable(sysindexes) on his 2005 machine first and then ship me the mdf an
d
ldf?
Got to get this somehow, all suggestions are greatly appreciated.
EdieCould you have the vendor restore the database as a different name on their
2005 server (so as not to disrupt their real copy), change the compatibility
to 80, then back *that* up, and try restoring that on 2000? I haven't tried
that, it's just an outside suggestion.
You could follow the same steps and try it yourself, if you have a 2005
instance available (or could set one up).
"Edie Richardson" <EdieRichardson@.discussions.microsoft.com> wrote in
message news:E9B97EAB-4F28-4F88-BBAD-D268B4538AE5@.microsoft.com...
>I have a vendor who uses sql server 2005 but we are currently using 2000
>sp4.
> He has sent me a mdf, a ldf file and a bak file. I would like to get
> this
> on my ss2k server. What is the best way to do this?
> I've tried restoring the bak file but it tells me that there is a
> compatibility problem.
> I've tried to create a new db, detach and reattach the mdf and ldf file
> that
> the vendor sent and I get the following error:
> Error 602: Could not find row in sysindexes for database ID XX, object ID
> X, index ID X. Run DBCC Checktable on sysindexes.
> Does this mean that I need to run DBCC checktable on sysindexes on the
> master db?
> I ran this on my ss2k machine. Should the vendor run the dbcc
> checktable(sysindexes) on his 2005 machine first and then ship me the mdf
> and
> ldf?
> Got to get this somehow, all suggestions are greatly appreciated.
> Edie|||You cannot get a 2005 database into 2000 at the binary level (using either r
estore or attach).
Script and BCP/DTS/BULK INSERT etc is the way to go here. Compatibility leve
l of the database does
not change this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Edie Richardson" <EdieRichardson@.discussions.microsoft.com> wrote in messag
e
news:E9B97EAB-4F28-4F88-BBAD-D268B4538AE5@.microsoft.com...
>I have a vendor who uses sql server 2005 but we are currently using 2000 sp
4.
> He has sent me a mdf, a ldf file and a bak file. I would like to get thi
s
> on my ss2k server. What is the best way to do this?
> I've tried restoring the bak file but it tells me that there is a
> compatibility problem.
> I've tried to create a new db, detach and reattach the mdf and ldf file th
at
> the vendor sent and I get the following error:
> Error 602: Could not find row in sysindexes for database ID XX, object ID
> X, index ID X. Run DBCC Checktable on sysindexes.
> Does this mean that I need to run DBCC checktable on sysindexes on the
> master db?
> I ran this on my ss2k machine. Should the vendor run the dbcc
> checktable(sysindexes) on his 2005 machine first and then ship me the mdf
and
> ldf?
> Got to get this somehow, all suggestions are greatly appreciated.
> Edie|||> Script and BCP/DTS/BULK INSERT etc is the way to go here. Compatibility
> level of the database does not change this.
Ugh, hadn't tried it...
Showing posts with label ldf. Show all posts
Showing posts with label ldf. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Moving .ldf to new drive, doesn't work gives "Cannot associate files with different datab
I am trying to move a log file to a non-RAID drive. I have detached
the database and made a copy of the .ldf file. I have taken this copy
of the .ldf file and placed it where I want it to be. I would like to
keep the .mdf file where it is located on the RAID drive, but for some
reason I can only reattach if the .ldf file is in the same location as
the .mdf file.
I have then run:
USE MASTER
GO
EXEC sp_attach_db @.dbname = N'Corporate',
@.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
@.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
And it returns:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
Is this possible?The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>|||On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> The procedure you described should work fine. But I suspect your .ldf file is
> not really from that database.
> Linchi
> "capern...@.gmail.com" wrote:
> > I am trying to move a log file to a non-RAID drive. I have detached
> > the database and made a copy of the .ldf file. I have taken this copy
> > of the .ldf file and placed it where I want it to be. I would like to
> > keep the .mdf file where it is located on the RAID drive, but for some
> > reason I can only reattach if the .ldf file is in the same location as
> > the .mdf file.
> > I have then run:
> > USE MASTER
> > GO
> > EXEC sp_attach_db @.dbname = N'Corporate',
> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> > And it returns:
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> > Is this possible?
It is really from that database. I can attach it back with no problems
if the files are in the same directory.|||Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegroups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>> The procedure you described should work fine. But I suspect your .ldf
>> file is
>> not really from that database.
>> Linchi
>> "capern...@.gmail.com" wrote:
>> > I am trying to move a log file to a non-RAID drive. I have detached
>> > the database and made a copy of the .ldf file. I have taken this copy
>> > of the .ldf file and placed it where I want it to be. I would like to
>> > keep the .mdf file where it is located on the RAID drive, but for some
>> > reason I can only reattach if the .ldf file is in the same location as
>> > the .mdf file.
>> > I have then run:
>> > USE MASTER
>> > GO
>> > EXEC sp_attach_db @.dbname = N'Corporate',
>> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
>> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
>> > And it returns:
>> > Server: Msg 5173, Level 16, State 2, Line 1
>> > Cannot associate files with different databases.
>> > Is this possible?
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>
the database and made a copy of the .ldf file. I have taken this copy
of the .ldf file and placed it where I want it to be. I would like to
keep the .mdf file where it is located on the RAID drive, but for some
reason I can only reattach if the .ldf file is in the same location as
the .mdf file.
I have then run:
USE MASTER
GO
EXEC sp_attach_db @.dbname = N'Corporate',
@.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
@.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
And it returns:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
Is this possible?The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>|||On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> The procedure you described should work fine. But I suspect your .ldf file is
> not really from that database.
> Linchi
> "capern...@.gmail.com" wrote:
> > I am trying to move a log file to a non-RAID drive. I have detached
> > the database and made a copy of the .ldf file. I have taken this copy
> > of the .ldf file and placed it where I want it to be. I would like to
> > keep the .mdf file where it is located on the RAID drive, but for some
> > reason I can only reattach if the .ldf file is in the same location as
> > the .mdf file.
> > I have then run:
> > USE MASTER
> > GO
> > EXEC sp_attach_db @.dbname = N'Corporate',
> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> > And it returns:
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> > Is this possible?
It is really from that database. I can attach it back with no problems
if the files are in the same directory.|||Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegroups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>> The procedure you described should work fine. But I suspect your .ldf
>> file is
>> not really from that database.
>> Linchi
>> "capern...@.gmail.com" wrote:
>> > I am trying to move a log file to a non-RAID drive. I have detached
>> > the database and made a copy of the .ldf file. I have taken this copy
>> > of the .ldf file and placed it where I want it to be. I would like to
>> > keep the .mdf file where it is located on the RAID drive, but for some
>> > reason I can only reattach if the .ldf file is in the same location as
>> > the .mdf file.
>> > I have then run:
>> > USE MASTER
>> > GO
>> > EXEC sp_attach_db @.dbname = N'Corporate',
>> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
>> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
>> > And it returns:
>> > Server: Msg 5173, Level 16, State 2, Line 1
>> > Cannot associate files with different databases.
>> > Is this possible?
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>
Moving .ldf to new drive, doesn't work gives "Cannot associate fil
The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>
Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegr oups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>
Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegr oups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>
Wednesday, March 21, 2012
Move tempdb files to a new disk
I have done a new installation which is up and running.
However the disk on which the tempdb mdf and ldf files are
is giving a problem. Is there a way to move the files to a
different disk? I know we can't detach and attach it.
Alter database does not allow the primary data file to be
deleted.
I can create a new data file on the other disk and stop
the existing datafile from growing. This will effectively
move all tempdb activity to the new disk. But is there no
better way to move the mdf and ldf files to the new disk?
TIA,
JackINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...1&Product=sql2k
Scroll down to the bottom and find the section titled 'Moving Tempdb'
You are correct in that Detach/Attach doesn't work on TempDB. This KB
article does explain the proper way to move TempDB.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||You do this using ALTER DATABASE. See KB 224071 for more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||Hi,
How to Move TEMPDB from one location to another
---
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and
log files, and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the
mssqlserver service before it takes affect.
This steps are common for SQL Server 7.0 and 2000.
Thanks
Hari
MCDBA
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack
However the disk on which the tempdb mdf and ldf files are
is giving a problem. Is there a way to move the files to a
different disk? I know we can't detach and attach it.
Alter database does not allow the primary data file to be
deleted.
I can create a new data file on the other disk and stop
the existing datafile from growing. This will effectively
move all tempdb activity to the new disk. But is there no
better way to move the mdf and ldf files to the new disk?
TIA,
JackINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...1&Product=sql2k
Scroll down to the bottom and find the section titled 'Moving Tempdb'
You are correct in that Detach/Attach doesn't work on TempDB. This KB
article does explain the proper way to move TempDB.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||You do this using ALTER DATABASE. See KB 224071 for more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||Hi,
How to Move TEMPDB from one location to another
---
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and
log files, and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the
mssqlserver service before it takes affect.
This steps are common for SQL Server 7.0 and 2000.
Thanks
Hari
MCDBA
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack
Monday, March 19, 2012
Move System and User database
Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
K
That is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K
|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>
|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database[vbcol=seagreen]
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scri...ptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.
>.
>
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
K
That is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K
|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>
|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database[vbcol=seagreen]
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scri...ptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.
>.
>
Move System and User database
Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.[vbcol=seagreen]
>.
>
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.[vbcol=seagreen]
>.
>
Move System and User database
Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> > Dear All,
> >
> > Is it possible to move system(master, model, msdb and so on...) and user
> > database to another sql server by just copying all ldf and mdf files in
> > data
> > directory while the sql on the source server is down?
> >
> > Any problem using this method instead of backup and restore?
> > as I don't want to detach the database from source server.
> >
> > Thanks for any advices
> > K
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
--
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
>> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and so on...) and
>> > user
>> > database to another sql server by just copying all ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and restore?
>> > as I don't want to detach the database from source server.
>> >
>> > Thanks for any advices
>> > K
>>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>> http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465
Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in
message
>> news:D1EB32EB-A2F6-465D-978E-
58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and
so on...) and user
>> > database to another sql server by just copying all
ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and
restore?
>> > as I don't want to detach the database from source
server.
>> >
>> > Thanks for any advices
>> > K
>>
>.
>
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:
> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> > Dear All,
> >
> > Is it possible to move system(master, model, msdb and so on...) and user
> > database to another sql server by just copying all ldf and mdf files in
> > data
> > directory while the sql on the source server is down?
> >
> > Any problem using this method instead of backup and restore?
> > as I don't want to detach the database from source server.
> >
> > Thanks for any advices
> > K
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
--
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
>> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and so on...) and
>> > user
>> > database to another sql server by just copying all ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and restore?
>> > as I don't want to detach the database from source server.
>> >
>> > Thanks for any advices
>> > K
>>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James
>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>> That is never a good idea. Check these out:
>> http://www.support.microsoft.com/?id=314546 Moving
DB's between Servers
>> http://www.support.microsoft.com/?id=224071 Moving
SQL Server Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465
Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To
Transfer Logins and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping
Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User
Logon and/or Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to
Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
>> Restoring a .mdf
>> http://www.support.microsoft.com/?id=307775 Disaster
Recovery Articles
>> for SQL Server
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in
message
>> news:D1EB32EB-A2F6-465D-978E-
58F96DB228F9@.microsoft.com...
>> > Dear All,
>> >
>> > Is it possible to move system(master, model, msdb and
so on...) and user
>> > database to another sql server by just copying all
ldf and mdf files in
>> > data
>> > directory while the sql on the source server is down?
>> >
>> > Any problem using this method instead of backup and
restore?
>> > as I don't want to detach the database from source
server.
>> >
>> > Thanks for any advices
>> > K
>>
>.
>
Monday, March 12, 2012
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sq
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Move sql 2005 ee db to new server without disk access?
Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.
Friday, March 9, 2012
Move mdf/ldf files for a replicated database
Can someone lend some assistance in this? It sounds like it should be
able to be done.
able to be done.
We have a large replicated database in SQL 2000. We need to move the
mdf and ldf files to a location on another drive.
Will doing this affect or break replication in any way? Is there any
way to prevent this?
Thanks in advance.
Glenn Dekhayser
Voyant Strategies"gdekhayser" <gdekhayser@.voyantinc.com> wrote in message
news:1113794872.548904.155990@.l41g2000cwc.googlegr oups.com...
> Can someone lend some assistance in this? It sounds like it should be
> able to be done.
> We have a large replicated database in SQL 2000. We need to move the
> mdf and ldf files to a location on another drive.
> Will doing this affect or break replication in any way? Is there any
> way to prevent this?
> Thanks in advance.
> Glenn Dekhayser
> Voyant Strategies
There are a number of posts about this in
microsoft.public.sqlserver.replication:
http://groups-beta.google.com/group...arch+this+group
The general answer seems to be that you need to break and recreate your
replication setup, but you might want to post in that group for more
details.
Simon
Move LDF -Has anyone seen this happen?
Hi
We have a SQL 2000 (sp3) server with a database that I set up to have the
MDF on D: and the LDF on E:. All was going along fine for several months and
one day the server rebooted. The SQL log says something like "Service is
shutting down due to server shutdown."
When it came back up the LDF File was on D: (in the same folder as the MDF).
The original LDF was gone from E:
The SQL Log doesn't ever say anything like "Recreating LDF in default
location." or any anything else that would explain what happened.
I assume the change happened during the reboot (actually I didn't notice it
until a week and a half later). I am relatively sure no human did anything
that caused the log file to move.
So has anyone seen this happen before.?
TIA
-Dick"DickChristoph" <dchristo101@.yahoo.com> wrote in message
news:hRBbb.19675$pU4.6621@.twister.rdc-kc.rr.com...
> Hi
> We have a SQL 2000 (sp3) server with a database that I set up to have the
> MDF on D: and the LDF on E:. All was going along fine for several months
and
> one day the server rebooted. The SQL log says something like "Service is
> shutting down due to server shutdown."
> When it came back up the LDF File was on D: (in the same folder as the
MDF).
> The original LDF was gone from E:
> The SQL Log doesn't ever say anything like "Recreating LDF in default
> location." or any anything else that would explain what happened.
> I assume the change happened during the reboot (actually I didn't notice
it
> until a week and a half later). I am relatively sure no human did anything
> that caused the log file to move.
> So has anyone seen this happen before.?
Nope.
I can't think of a way it would happen w/o human intervention.
You can check the ERRORLOG files for sp_detachdb (I think they show up
there).
You should have the last 4 and the current one.
> TIA
> -Dick
>
>
Subscribe to:
Posts (Atom)