July 5, 2012 at 5:54 am
Need to move a database[reporting] of sql server 2000 from E drive to F drive
i have to move a database data&log file form e:\mssql\data to f:\\mssql\data
1. [E:\mssql\data\reporting.mdf and E:\mssql\data\reporting_log.ldf]to another drive
[F:\mssql\data\reporting.mdf and F:\mssql\data\reporting_log.ldf]
2.new backup should be done in F:\Backups\reporting.bak from E:\backups\reporting.bak
3.Need to take or change the backup location also to F from E drive
4.Need to take or change the TLbackup(Transaction Log BAckups)
location also to F from E drive
Thanks
Naga.Rohitkumar
July 5, 2012 at 6:16 am
Detach the database, move the files, attach the database.
To move the backups you'll need to go and edit the jobs or maintenance plans that do the backups and change the destination for them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2012 at 6:19 am
Can the database be offline while you move it? If so, that makes it easy. If not, it's not too hard, but it is more complex.
If it can be offline, detach the database (you can do this by right-clicking it in Management Studio or Enterprise Manager and selecting the appropriate option to detach it), then move the files, then re-attach it. Done.
If it has to be online while moving it, take a look at Alter Database, specifically the data on files and file options. Details here: http://msdn.microsoft.com/en-us/library/bb522469.aspx
As far as backup locations go, that's separate. That'll depend on how you're doing the backups. Scheduled maintenance plan maybe?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 5, 2012 at 7:08 am
GSquared (7/5/2012)
If it has to be online while moving it, take a look at Alter Database, specifically the data on files and file options. Details here: http://msdn.microsoft.com/en-us/library/bb522469.aspx
Not an option on SQL 2000, besides even that is offline for the actual movement of the files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply