Move database file: method

Showing posts with label method. Show all posts

Monday, March 26, 2012

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?
Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default...;EN-US;q224071
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>
|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/default...;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
drive
> on
>

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...b;EN-US;q224071
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/defaul...b;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
drive[vbcol=seagreen]
> on
>

moving a database to a different drive

what is the reccomended method for moving a database to a different drive on
the same computer? Just a backup and restore?Hi,
You could do a backup and restore using the 'WITH MOVE'
OPTION or you could (better use) detach the database, copy
the datafile to new location and attach the database files.
You can look at sp_detach_db and sp_attach_db procedures
in BOL.
hth
DeeJay
>--Original Message--
>what is the reccomended method for moving a database to a
different drive on
>the same computer? Just a backup and restore?
>
>.
>|||ok. I'll check out those procedures in Books Online. Thanks for the input.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> >--Original Message--
> >what is the reccomended method for moving a database to a
> different drive on
> >the same computer? Just a backup and restore?
> >
> >
> >.
> >|||Have a look at
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"djc" <noone@.nowhere.com> wrote in message
news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> what is the reccomended method for moving a database to a different drive
on
> the same computer? Just a backup and restore?
>|||Thanks. I will.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eC3pXbxUEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Have a look at
> INF: Moving SQL Server Databases to a New Location with Detach/Attach
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "djc" <noone@.nowhere.com> wrote in message
> news:elhbGfwUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > what is the reccomended method for moving a database to a different
drive
> on
> > the same computer? Just a backup and restore?
> >
> >
>|||Hi,
I will use the detatch/attatch method to move but your reply brings another
question to me. I did not see an option to restore 'WITH MOVE' in enterprise
manager GUI restore... is it only available when using TSQL? The reason I
ask is I had previously done a backup and restore to new database name and
new location in order to quickly create a 'staging' or 'testing' version of
a production database and want to be sure that was ok... meaning I wanted to
verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
was a default option. Because, in that case, I wanted the database to be
different... not a move of the real database..
If I actually got a clear question out of that (doubtful ;) ) any info would
be appreciated... I think I'm just being overly paranoid.
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> Hi,
> You could do a backup and restore using the 'WITH MOVE'
> OPTION or you could (better use) detach the database, copy
> the datafile to new location and attach the database files.
> You can look at sp_detach_db and sp_attach_db procedures
> in BOL.
> hth
> DeeJay
> >--Original Message--
> >what is the reccomended method for moving a database to a
> different drive on
> >the same computer? Just a backup and restore?
> >
> >
> >.
> >|||The move option is exposed on the right-most tab, where EM specifies the physical file names. In the right
part, just type the desired file name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:%23Cj8oj5UEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I will use the detatch/attatch method to move but your reply brings another
> question to me. I did not see an option to restore 'WITH MOVE' in enterprise
> manager GUI restore... is it only available when using TSQL? The reason I
> ask is I had previously done a backup and restore to new database name and
> new location in order to quickly create a 'staging' or 'testing' version of
> a production database and want to be sure that was ok... meaning I wanted to
> verify that I had NOT chosen the 'WITH MOVE' option... I didn't know if it
> was a default option. Because, in that case, I wanted the database to be
> different... not a move of the real database..
> If I actually got a clear question out of that (doubtful ;) ) any info would
> be appreciated... I think I'm just being overly paranoid.
>
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:1cc9b01c45309$967fde70$a101280a@.phx.gbl...
> > Hi,
> >
> > You could do a backup and restore using the 'WITH MOVE'
> > OPTION or you could (better use) detach the database, copy
> > the datafile to new location and attach the database files.
> >
> > You can look at sp_detach_db and sp_attach_db procedures
> > in BOL.
> >
> > hth
> >
> > DeeJay
> > >--Original Message--
> > >what is the reccomended method for moving a database to a
> > different drive on
> > >the same computer? Just a backup and restore?
> > >
> > >
> > >.
> > >
>

Friday, March 23, 2012

Moving 6.5 DB's

I have a legacy database installed on a SQL Server 2000 machine, running in
6.5 compatibility mode. What is the best method to move it to another SQL
Server 2000 server, while still in 6.5 compatibility mode?
More details: The DB is about 4.8GB. It is a data source for a 3rd party
application, so accessing the client code will be difficult at best (I do
know how to modify its .ini file to redirect the connection string to another
server, as this has been done before). I am concerned with updating the
database to 2000, since I can't say for sure that the client app will still
work if I do.
I tried using a standard backup from EM, which failed. I discovered that
the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
still being available for backward compatibility , it doesn't show what the
syntax used to be or what arguments it used back then.
The method of moving the database I was trying to do was to back up the
database, and restore it to a different server. The DB is for archive
purposes only, so there are no transactions taking place.
I am concerned with the best way to do this. Since discovering that there is
no back-up of the database (The job set up by the previous DB Admin has been
failing all this time), and if I upgrade it, there is no fall-back. The only
way to recover it then is the server back-up tapes. This is the only copy of
this database, so I'm concerned with the best approach to this situation.
Of course, once I have it copied into the test environment, I will test how
it integrates with the client app after upgrading to 2000. But for now, I
just need to know how to safely move it from the production server to the
test server.
Suggestions? Thanks!Joel,
First step - make a backup. I'd also perform a DBCC CHECKDB on the database
as well. The backup/restore method should work with no issues (outside of
possible drive/path differences). Also, sp_detach_db and sp_attach_db would
probably be the easiest. You can control the compatibility mode by using
sp_dbcmptlevel (see SQL BOL) for the database.
For moving the database see (in addition to SQL BOL):
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to
> another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will
> still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what
> the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there
> is
> no back-up of the database (The job set up by the previous DB Admin has
> been
> failing all this time), and if I upgrade it, there is no fall-back. The
> only
> way to recover it then is the server back-up tapes. This is the only copy
> of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test
> how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!|||I thought about the detach / attach options as well. However, I'm trying to
copy the database, not move it. I thought detaching & attaching was only for
moving them.
"Jerry Spivey" wrote:
> Joel,
> First step - make a backup. I'd also perform a DBCC CHECKDB on the database
> as well. The backup/restore method should work with no issues (outside of
> possible drive/path differences). Also, sp_detach_db and sp_attach_db would
> probably be the easiest. You can control the compatibility mode by using
> sp_dbcmptlevel (see SQL BOL) for the database.
> For moving the database see (in addition to SQL BOL):
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
> >I have a legacy database installed on a SQL Server 2000 machine, running in
> > 6.5 compatibility mode. What is the best method to move it to another SQL
> > Server 2000 server, while still in 6.5 compatibility mode?
> >
> > More details: The DB is about 4.8GB. It is a data source for a 3rd party
> > application, so accessing the client code will be difficult at best (I do
> > know how to modify its .ini file to redirect the connection string to
> > another
> > server, as this has been done before). I am concerned with updating the
> > database to 2000, since I can't say for sure that the client app will
> > still
> > work if I do.
> >
> > I tried using a standard backup from EM, which failed. I discovered that
> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> > how
> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> > still being available for backward compatibility , it doesn't show what
> > the
> > syntax used to be or what arguments it used back then.
> >
> > The method of moving the database I was trying to do was to back up the
> > database, and restore it to a different server. The DB is for archive
> > purposes only, so there are no transactions taking place.
> >
> > I am concerned with the best way to do this. Since discovering that there
> > is
> > no back-up of the database (The job set up by the previous DB Admin has
> > been
> > failing all this time), and if I upgrade it, there is no fall-back. The
> > only
> > way to recover it then is the server back-up tapes. This is the only copy
> > of
> > this database, so I'm concerned with the best approach to this situation.
> >
> > Of course, once I have it copied into the test environment, I will test
> > how
> > it integrates with the client app after upgrading to 2000. But for now, I
> > just need to know how to safely move it from the production server to the
> > test server.
> >
> > Suggestions? Thanks!
>
>|||Joel,
You can detach the database, copy the file, then attach the database both
locally and remotely. Notice you'll be copying the file not moving it in
this case.
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>I thought about the detach / attach options as well. However, I'm trying to
> copy the database, not move it. I thought detaching & attaching was only
> for
> moving them.
> "Jerry Spivey" wrote:
>> Joel,
>> First step - make a backup. I'd also perform a DBCC CHECKDB on the
>> database
>> as well. The backup/restore method should work with no issues (outside
>> of
>> possible drive/path differences). Also, sp_detach_db and sp_attach_db
>> would
>> probably be the easiest. You can control the compatibility mode by using
>> sp_dbcmptlevel (see SQL BOL) for the database.
>> For moving the database see (in addition to SQL BOL):
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> HTH
>> Jerry
>> "Joel" <Joel@.discussions.microsoft.com> wrote in message
>> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>> >I have a legacy database installed on a SQL Server 2000 machine, running
>> >in
>> > 6.5 compatibility mode. What is the best method to move it to another
>> > SQL
>> > Server 2000 server, while still in 6.5 compatibility mode?
>> >
>> > More details: The DB is about 4.8GB. It is a data source for a 3rd
>> > party
>> > application, so accessing the client code will be difficult at best (I
>> > do
>> > know how to modify its .ini file to redirect the connection string to
>> > another
>> > server, as this has been done before). I am concerned with updating
>> > the
>> > database to 2000, since I can't say for sure that the client app will
>> > still
>> > work if I do.
>> >
>> > I tried using a standard backup from EM, which failed. I discovered
>> > that
>> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL
>> > for
>> > how
>> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE
>> > command
>> > still being available for backward compatibility , it doesn't show what
>> > the
>> > syntax used to be or what arguments it used back then.
>> >
>> > The method of moving the database I was trying to do was to back up the
>> > database, and restore it to a different server. The DB is for archive
>> > purposes only, so there are no transactions taking place.
>> >
>> > I am concerned with the best way to do this. Since discovering that
>> > there
>> > is
>> > no back-up of the database (The job set up by the previous DB Admin has
>> > been
>> > failing all this time), and if I upgrade it, there is no fall-back. The
>> > only
>> > way to recover it then is the server back-up tapes. This is the only
>> > copy
>> > of
>> > this database, so I'm concerned with the best approach to this
>> > situation.
>> >
>> > Of course, once I have it copied into the test environment, I will test
>> > how
>> > it integrates with the client app after upgrading to 2000. But for now,
>> > I
>> > just need to know how to safely move it from the production server to
>> > the
>> > test server.
>> >
>> > Suggestions? Thanks!
>>|||Hi,
To add on to Joel; Since compatibility mode is stored in Master database --
sysdatabases table; you may need to set the
db compatibility level to 65 again using
EXEC sp_dbcmptlevel 'DBNAME', 65
Note:-
If you need to keep the source database online then use BACKUP / RESTORE
commds to copy the database.
1. Backup the database
2. copy the backup file to destination
3. Restore the database
4. Set the dbcopmatibility to 65
5. sync the logins and users using sp_change_users_login
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ucTpDiUxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Joel,
> You can detach the database, copy the file, then attach the database both
> locally and remotely. Notice you'll be copying the file not moving it in
> this case.
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>>I thought about the detach / attach options as well. However, I'm trying
>>to
>> copy the database, not move it. I thought detaching & attaching was only
>> for
>> moving them.
>> "Jerry Spivey" wrote:
>> Joel,
>> First step - make a backup. I'd also perform a DBCC CHECKDB on the
>> database
>> as well. The backup/restore method should work with no issues (outside
>> of
>> possible drive/path differences). Also, sp_detach_db and sp_attach_db
>> would
>> probably be the easiest. You can control the compatibility mode by
>> using
>> sp_dbcmptlevel (see SQL BOL) for the database.
>> For moving the database see (in addition to SQL BOL):
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> HTH
>> Jerry
>> "Joel" <Joel@.discussions.microsoft.com> wrote in message
>> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>> >I have a legacy database installed on a SQL Server 2000 machine,
>> >running in
>> > 6.5 compatibility mode. What is the best method to move it to another
>> > SQL
>> > Server 2000 server, while still in 6.5 compatibility mode?
>> >
>> > More details: The DB is about 4.8GB. It is a data source for a 3rd
>> > party
>> > application, so accessing the client code will be difficult at best (I
>> > do
>> > know how to modify its .ini file to redirect the connection string to
>> > another
>> > server, as this has been done before). I am concerned with updating
>> > the
>> > database to 2000, since I can't say for sure that the client app will
>> > still
>> > work if I do.
>> >
>> > I tried using a standard backup from EM, which failed. I discovered
>> > that
>> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL
>> > for
>> > how
>> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE
>> > command
>> > still being available for backward compatibility , it doesn't show
>> > what
>> > the
>> > syntax used to be or what arguments it used back then.
>> >
>> > The method of moving the database I was trying to do was to back up
>> > the
>> > database, and restore it to a different server. The DB is for archive
>> > purposes only, so there are no transactions taking place.
>> >
>> > I am concerned with the best way to do this. Since discovering that
>> > there
>> > is
>> > no back-up of the database (The job set up by the previous DB Admin
>> > has
>> > been
>> > failing all this time), and if I upgrade it, there is no fall-back.
>> > The
>> > only
>> > way to recover it then is the server back-up tapes. This is the only
>> > copy
>> > of
>> > this database, so I'm concerned with the best approach to this
>> > situation.
>> >
>> > Of course, once I have it copied into the test environment, I will
>> > test
>> > how
>> > it integrates with the client app after upgrading to 2000. But for
>> > now, I
>> > just need to know how to safely move it from the production server to
>> > the
>> > test server.
>> >
>> > Suggestions? Thanks!
>>
>|||DUMP and LOAD is essentially the same as BACKUP and RESTORE, for instance:
DUMP DATABASE db
TO DISK = 'C:\a.bak'
LOAD DATABASE db
FROM DISK = 'C:\a.bak'
I don't know if new features of BACKUP and RESTORE are available for DUMP and LOAD, like RECOVERY
etc. Wouldn't think so.
However, it doesn't matter. Always execute the backup and restore commands from the master database,
and master cannot be in down-level compatibility mode. Or, use detach and attach, as suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there is
> no back-up of the database (The job set up by the previous DB Admin has been
> failing all this time), and if I upgrade it, there is no fall-back. The only
> way to recover it then is the server back-up tapes. This is the only copy of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!

Moving 6.5 DB's

Wednesday, March 21, 2012

move T-log to another Disk?

Hello DBAs,
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl" wrote in =message news:031f01c3514e$f6=3ce230$a301280a@.phx.gbl...Hello DBAs,I think there are several different ways to move a =transaction log file to another disk. What would you do? what method will you =go with?Currently DBs and T-logs are located in same disk, and I =learned that if they are located different disks would increase SQL server performance from SQL trainning class.Please advice me on this, =new DBA here.Thanks in advance.Sunny

--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
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
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>

Wednesday, March 7, 2012

Move DTS package from Server A to Server B

Hi

What would you say is the best method to move a DTS package from, say, a test environment to the production server please?

Thanks muchlySave it as a file instead of within msdb.|||DTS best practice,check this link.

http://vyaskn.tripod.com/sql_server_dts_best_practices.htm|||Thanks Blindman

Had just imported it as you posted - it is nearly the end of the Friday working day for me here - that's my excuse for not googling properly before posting :rolleyes:

You guys across the pond enjoy the rest of your Friday afternoon (working...) - I'm off to play the girly version of Football :D|||Check out DTSBackup at sqldts.com

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
--
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from
SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to
the
> > new? I expect to add users manually and point the client applications
to
> > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do
the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > method, I can easily switch back to the old database if something
doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>
> AMB
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to the
> > new? I expect to add users manually and point the client applications to
> > the new server. I've done the Upgrade Advisor and resolved a couple minor
> > things. For testing, I've taken a BAK file from the old server (sql 2000)
> > and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a backup/restore
> > method, I can easily switch back to the old database if something doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
> > How to move databases between computers that are running SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> >
> > AMB
> >
> > "HK" wrote:
> >
> > > Question: Which method should I use to do the simultaneous change
from SQL
> > > 2000 to 2005, and the change from an old PC (server) to a new one?
> > >
> > > Current: SQL 2000 + Old Hardware
> > > New (Future): SQL 2005 + New Hardware
> > >
> > > What's the best way to get a single live database from the current to
the
> > > new? I expect to add users manually and point the client
applications to
> > > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading
to
> > > 2005 in the process, and a few test queries execute properly. So
I'm
> > > inclined to just do a backup and restore at that magic moment when we
do the
> > > upgrade. And then I'd add logins and jobs manually. Is there some
> > > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > > method, I can easily switch back to the old database if something
doesn't
> > > work. Plus, I can test this method many times before the big day, but
I
> > > can't easily test a detach of this database in advance. Thoughts?
> > >
> > >
> > >

Monday, March 26, 2012

moving a database to a different drive

moving a database to a different drive

moving a database to a different drive

Friday, March 23, 2012

Moving 6.5 DB's

Moving 6.5 DB's

Moving 6.5 DB's

Wednesday, March 21, 2012

move T-log to another Disk?

Wednesday, March 7, 2012

Move DTS package from Server A to Server B

Move DB by backup/restore or SP_Detach?

Move DB by backup/restore or SP_Detach?

Move DB by backup/restore or SP_Detach?

Move database file

Blog Archive

About Me