Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
1
Guests Online:
66

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

moving system databases

Please wait...

moving system databases

Postby Thirster42 on Fri Dec 28, 2012 4:51 pm

i need to move some system databases on a few servers. i used to have a website that had this pretty well documented, but i can't find it.

what's the best way to move system databases? for example: master, model, msdb, tempdb.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: moving system databases

Postby SQLDenis on Fri Dec 28, 2012 5:42 pm

This should do it

http://msdn.microsoft.com/en-us/library/ms345408.aspx


tempdb gets recreated from scratch everytime you restart no need to move
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: moving system databases

Postby SQLDenis on Fri Dec 28, 2012 5:47 pm

SQLDenis wrote:tempdb gets recreated from scratch everytime you restart no need to move



Unless you mean on the same server in that case

  1. ALTER DATABASE tempdb
  2. MODIFY FILE ....new location


I did that the other day and silly me moved it to a drive where the SQL account didn't have permissions....then SQL Server wouldn't start up :-)
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: moving system databases

Postby onpnt on Fri Dec 28, 2012 6:21 pm

Move means to a new drive? Like Denis asked, that would be the important part to know. If it is a move, Denis already gave the answer (and for a complete SQL Server move). Both require downtime of course.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: moving system databases

Postby Thirster42 on Fri Dec 28, 2012 7:57 pm

onpnt wrote:Move means to a new drive? Like Denis asked, that would be the important part to know. If it is a move, Denis already gave the answer (and for a complete SQL Server move). Both require downtime of course.



moving the files to a different drive.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: moving system databases

Postby faris90 on Tue Dec 31, 2013 8:29 am

I wonder if there's any way to get access to the binary data for a backup in a CLR proc to back up to a non-azure location in a similar fashion, or if you'd need to back up to a file and then move that.What happens when you click on visit the preview portal at the bottom?
Certkiller www.cert-killer.ws
000-667 braindumps 000-667 braindumps
faris90
Newbie
Newbie
 
Posts: 1
Joined: Tue Dec 31, 2013 8:27 am
Unrated

Re: moving system databases

Postby Markk786 on Fri Mar 09, 2018 9:43 pm

Is there any way to patch-up Microsoft SQL Server with php based tool?
dermatophagiathoracentesisepicanthalfolds

Infantigo
Markk786
Newbie
Newbie
 
Posts: 2
Joined: Sun Jul 30, 2017 2:15 am
Unrated