Moving SQL Server System Tables

Moving SQL Server System Tables sounds like it's going to be a complicated and fairly long-winded task, but with a few SQL queries, you're good to go.

Pre-requisites:

Before you start, make sure that the user you've used to connect to your instance of MSSQL Server has the correct permissions to perform administrative tasks. You'll need to have a user that's part of the sysadmin group. I also assume that you're using SQL Server Management Studio, which can be obtained at the following address:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Queries for moving the system databases:

To find out the logical names of all the database files for a database, you'll need to run the following query:

SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  
    WHERE database_id = DB_ID(N'tempdb');  

 The above query will display a row for each file that the database is attached to and a logical name for each one.

Note: You can change the query to use another database by replacing 'tempdb' with the database name.

The below example displays what was displayed to me when I ran the query for the master database:

name CurrentLocation state_desc
master C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf ONLINE
masterlog C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf ONLINE

You can run the following command to move a database file location:

USE master;  
GO  
ALTER DATABASE databasename
MODIFY FILE (NAME = logicalname, FILENAME = 'F:\myfolder\databasefilename.mdf');  
GO  

Here's an example to move the tempdb 'database' files:

USE master;  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\TempDB\tempdb.mdf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'F:\TempDB\templog.ldf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp2, FILENAME = 'F:\TempDB\tempdb_mssql_2.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp3, FILENAME = 'F:\TempDB\tempdb_mssql_3.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp4, FILENAME = 'F:\TempDB\tempdb_mssql_4.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp5, FILENAME = 'F:\TempDB\tempdb_mssql_5.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp6, FILENAME = 'F:\TempDB\tempdb_mssql_6.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp7, FILENAME = 'F:\TempDB\tempdb_mssql_7.ndf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp8, FILENAME = 'F:\TempDB\tempdb_mssql_8.ndf');  
GO  

Once you've run the command, restart the SQL Server instance to make it use the newly stored configuration

Note: The tempdb database does not require you to move any files, but other databases do, so make sure you do that before restarting the SQL Server instance.

Issues that might occur:

If you've moved the 'msdb' database and restarted the SQL Server instance, you might run into an issue where the database displays '(recovery mode)' after the database name, in this case, you need to make sure that you've manually moved the database files from 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' or wherever your SQL Server instance has been installed, to the new folder that you specified above. You also need to make sure that Network Service has full access to the aforementioned folder.

Reasons for moving database files:

You may have your system set up to use solid SATA drives and wish to extend drive lifetime by reducing writes, in this case, moving the 'tempdb' files to a hard drive is a good idea.

You may want to store database files on another drive to save on disk space on drives that have little remaining space.

Other Resources:

Microsoft documentation for moving system databases:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases

Microsoft documentation for the msdb database:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database


Published at

Tags: SQL,MSSQL

Luke Alderton

Comments

Adivi70
https://buy-nimodipine-safely.tumblr.com https://its-batmanhere.tumblr.com https://thcmvss.tumblr.com https://letters-and-fire.tumblr.com https://agravio-moral.tumblr.com https://kmep1990.tumblr.com https://dylenalover.tumblr.com https://meanslappy.tumblr.com https://spookoplazm.tumblr.com https://umameninadeboa.tumblr.com https://avecesfrancisca.tumblr.com https://rascheplan.tumblr.com https://themaynards624.tumblr.com https://ultimate-dr-puns.tumblr.com
03/12/2018
Tepul31
http://higgs-tours.ning.com/profiles/blogs/ciafil-o-en-acheter-tadalafil-belgique-vente-libre http://higgs-tours.ning.com/profiles/blogs/donde-a-la-orden-siagra-env-o-r-pido-ecuador http://social.leembe.com/blogs/post/40448 http://www.q-voice.tv/old/redirect/test/qa/index.php?qa=66885&qa_1=farmacia-online-donde-comprar-falsigra-sin-receta-fiable http://laugarnecaravans.ning.com/profiles/blogs/farmacia-online-donde-comprar-generico-cialis-al-mejor-precio http://ggwadvice.com//index.php?qa=51730&qa_1=naprolag-naproxen-500-commander-achat-naprolag-cher-france http://ssbsavannah.ning.com/profiles/blogs/tantrix-sildenafil-citrate-onde-comprar-envio-rapido-online http://divasunlimited.ning.com/profiles/blogs/site-seguro-para-comprar-ciavor-urgente-rep-blica-federativa-do http://viatorfroshreligion.ning.com/profiles/blogs/comprar-tizanidina-zanaflex http://www.onfeetnation.com/profiles/blogs/amiloride-5-mg-comprar-en-una-farmacia-en-linea-con-entrega-r http://howidoit.ning.com/profiles/blogs/c-mo-realizar-un-pedido-zidovudine-300mg-sin-receta-por-internet http://www.facecool.com/profiles/blogs/comprar-cianeo-tadalafil-n-o-precisa-receita-m-dica-brasil-qual-o https://www.olliesmusic.com/blog/28524/comprar-generico-leflunomide-20-mg-online-panamГЎ-comprar-leflunomide-online/ http://neolatino.ning.com/profiles/blogs/betapace-40mg-onde-comprar-entrega-r-pida-on-line-rep-blica http://kasabovart.ning.com/profiles/blogs/avanafilo-comprar-en-farmacia-en-linea-con-env-o-gratis-chile http://ssbsavannah.ning.com/profiles/blogs/comprar-melatonin-sin-receta-r-pido-rep-blica-del-ecuador-meloset http://howidoit.ning.com/profiles/blogs/acheter-pilule-mesalamine-acheter-mesalamine-au-maroc http://innovatoruddannelse.ning.com/profiles/blogs/expit-order-where-to-order-sildenafil-citrate-safely http://writeskills.ning.com/profiles/blogs/dapoxetine-bon-prix-priligy-sans-ordonnance-en-pharmacie-quebec http://facebookhitlist.com/profiles/blogs/comprar-fluene-100-mg-de-confianza-fluene-150mg-generico http://bioimagingcore.be/q2a/50096/ernafil-purchase-sildenafil-citrate-guaranteed-shipping http://bioimagingcore.be/q2a/50049/cyvel-tadalafil-comprar-ahora-dominicana http://weebattledotcom.ning.com/profiles/blogs/buy-clomiphene-25mg-without-prescription-cheapest-genuine http://www.godry.co.uk/profiles/blogs/meilleur-site-pour-acheter-du-idilico-sildenafil-citrate http://sallieanncooks.ning.com/profiles/blogs/order-olanzapine-online-can-i-order-zyprexa-without-rx http://thehiphophub.ning.com/profiles/blogs/comprar-dali-tadalafil-20mg-gen-rico-urgente-pela-internet-brasil
08/12/2018
Dufox53
http://mcdonaldauto.ning.com/profiles/blogs/order-disilden-120mg-online-where-can-i-buy-real-disilden-online http://isladepascua.ning.com/profiles/blogs/site-seguro-para-comprar-virineo-gen-rico-com-desconto-internet http://recampus.ning.com/profiles/blogs/mometasona-comprar-en-una-farmacia-en-linea-sin-gastos-de-envio http://www.themindbook.org/nmp/blogs/post/118 http://snopeczek.hekko.pl/227697/farmacia-comprar-generico-nateglinida-espana-comprar-starlix http://jawbite.ning.com/profiles/blogs/tadovas-20-mg-o-achat-bon-marche-pharmacie-acheter-tadovas http://professoratciutatvella.ning.com/profiles/blogs/achat-de-en-ligne-revathio-bon-marche-achat-revathio-130-en-ligne http://sallieanncooks.ning.com/profiles/blogs/tada-40-mg-comprar-sem-receita-medica-pela-internet-brasil http://network-marketing.ning.com/profiles/blogs/sex-up-sildenafil-citrate-o-en-acheter-acheter-sildenafil-citrate http://crossroadshob.ning.com/profiles/blogs/achat-securise-alendronate-bas-prix-sans-ordonnance-achat http://playit4ward-sanantonio.ning.com/profiles/blogs/farmacia-online-donde-comprar-silvagra-entrega-r-pida-chile http://bioimagingcore.be/q2a/49283/comprar-sildenafil-citrate-prescrição-internet-portugal http://adrenalineprovinggrounds.ning.com/profiles/blogs/o-acheter-en-ligne-sex-o-vigor-vente-sex-o-vigor-canada http://source1law.com/s1l/blogs/218/7619/achat-securise-mildfil-tadalafil-pour-homme-achat http://thehiphophub.ning.com/profiles/blogs/site-seguro-para-comprar-escitan-envio-rapido-na-internet-brasil http://augasthya-plus.ning.com/profiles/blogs/como-comprar-adapaleno-15-mg-sin-receta-barato-colombia http://sfchsjournalism.ning.com/profiles/blogs/como-fa-o-pra-comprar-zyad-tadalafil-gen-rico-sem-prescri-o http://higgs-tours.ning.com/profiles/blogs/viasil-onde-comprar-internet-viasil-generico-belo-horizonte http://www.collabzo.com/pwrportal/blogs/83/12115/donde-se-puede-comprar-generico http://lifestir.net/blogs/post/74233 http://sallieanncooks.ning.com/profiles/blogs/buy-ledipasvir-sofosbuvir-90-400mg-cheap-where-to-buy-harvoni http://sallieanncooks.ning.com/profiles/blogs/vigore-sildenafil-citrate-200mg-vrai-bas-prix-achat-vigore-130 http://wu-world.com/profiles/blogs/para-comprar-generico-vigrande-sin-receta-buen-precio-rep-blica http://vocal-buzz.ning.com/profiles/blogs/cyvel-order-without-prescription-cyvel-buy-review http://www.myslimfix.com/profiles/blogs/onde-comprar-cialis-tadalafil-10mg-r-pido-internet-rep-blica http://mcdonaldauto.ning.com/profiles/blogs/cheap-medovigor-200-mg-buy-online-can-i-purchase-sildenafil http://wu-world.com/profiles/blogs/comprar-prilo-130-mg-urgente-pela-net-brasil
08/12/2018
Udede34
http://whazzup-u.com/profiles/blogs/order-filda-40mg-order-filda-from-overseas http://professoratciutatvella.ning.com/profiles/blogs/acheter-pilule-dafilar-tadalafil-bon-marche-mastercard-dafilar http://football.ning.com/profiles/blogs/achat-express-azona-100-mg-moins-cher-site-francais-prix-azona-25 http://brooklynne.net/profiles/blogs/lopressor-hct-metoprolol-como-comprar-sin-receta-pago-mastercard http://brooklynne.net/profiles/blogs/differin-buy-cheap-anyone-buy-differin-online-canada http://showmeanswer.com/index.php?qa=37587&qa_1=cheap-zoyagra-150mg-online-purchase-sildenafil-citrate-safely http://cdloga78.ning.com/profiles/blogs/buy-discount-procops-130mg-where-to-buy-sildenafil-citrate-quick http://clan.hupshup.com/blogs/post/11968 http://tnfdjs.ning.com/profiles/blogs/donde-para-ordenar-sinota-de-forma-segura-uruguay http://writeskills.ning.com/profiles/blogs/onde-comprar-cianeo-tadalafil-60mg-forma-segura-na-internet http://whozwholive.ning.com/profiles/blogs/achat-securise-dafilar-tadalafil-bon-marche-visa-vente-generique http://wu-world.com/profiles/blogs/edegra-acheter-blog-achat-sildenafil-citrate-internet http://cqa.aaua.edu.ng/index.php?qa=22243&qa_1=buy-amore-36-without-rx-online-amore-36-sales http://digitalguerillas.ning.com/profiles/blogs/genegra-25-mg-como-posso-comprar-gen-rico-pre-o-online-no-brasil http://property.ning.com/profiles/blogs/site-seguro-para-comprar-varfarina-1mg-gen-rico-sem-receita-pela http://wu-world.com/profiles/blogs/zyad-onde-comprar-pre-o-tadalafil-preco-drogasil http://football.ning.com/profiles/blogs/ciavor-diario-onde-comprar-e-quanto-custa-na-internet-brasil http://network-marketing.ning.com/profiles/blogs/comprar-tada-tadalafil-sem-prescri-o-pela-net
13/12/2018
Post a comment
Sit tight...
We're adding your comment.
Thank you.
Your comment has been added.
There's been a problem.
Please try again later.
Share with
Tags
Latest Comments
By Ecake68 on MVC forms in Umbraco
16 Dec 2018, 16:11 PM
By Wowob43 on Restrict Umbraco Back Office by IP
16 Dec 2018, 10:21 AM
By Rosax69 on How does DNS work? For dummies!
15 Dec 2018, 16:44 PM
By Aroli61 on Using SQL DataTables to speed up database performance
15 Dec 2018, 09:02 AM
By Xasus39 on MVC forms in Umbraco
14 Dec 2018, 20:04 PM
By Shane on Xamarin Forms multiple instances of same app open
14 Dec 2018, 05:33 AM
By Apabu19 on Displaying text on a 16x2 LCD screen with a Raspberry Pi
13 Dec 2018, 18:26 PM
By Anuri66 on Using SQL DataTables to speed up database performance
13 Dec 2018, 13:52 PM
Categories
App Development
Event
Game Development
Mapping
Modelling
Programming
Review
Robotics
Tutorial
Web Development