An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.
For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.
Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.
The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.
- Open Microsoft SQL Server Management Studio (SSMS).
- Connect to the server that houses the Db you are working with.
- Run the modified script
- Right click on the Db in SSMS and select Tasks > Take Offline
- If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
- Go back to SSMS and right click on the Db and select Tasks > Bring Online.
- If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
- Now you can rename the Db to the new name if you wish using SSMS.
This topic reminds of me this little quiz of which jug will fill first.
Pretty much all of the documentation and recommendations out there say to keep the tempdb data files the same size so that the round-robin data flow works properly, i.e. the tempdb data files fill up evenly. This means that the data for a large temp table is actually split across the files and does not reside in one file.
Below is the code necessary to prove this scenario.
I tested this process on Microsoft SQL Server 2012 – Service Pack 1.
If working with a default installation of SQL Server Express The below script should print out the code to generate four equally sized (500 Mb) tempdb data files with no auto growth.
Run the script against the instance, review the print out and then copy/paste and run it against the instance
If you’ve been working towards a new deployment to a live database chances are you have written several scripts (possibly dozens) that have been developed/tested against the the development server.
Now the time has come to put the update live. Which would require executing each script against the live database.
This task can be automated by using a very handy batch script to run against the directory the files are saved in.
Caveat: This process does not take into account error handling or rollbacks, it’s just a simple example people can build on.
In order for this to work the files must have been named in a manner that the necessary order of execution corresponds to ascii sort order, i.e. 001_CreateTable.sql, 002_PopulateTable etc. This is standard practice for sql file naming conventions.
Simply create a .BAT file with the following command:
(Swap servername and databaseName for your required server and database names, TIP: SELECT @@servername can provide you with the full server name.)
Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and the command will loop and execute every SQL script in the folder.
Ok so here’s a nifty little batch script that will loop through all the directories in a directory to gather all the subsequent files into a new directory. (Code at bottom of the page)
(For those of you that don’t know what a batch script is here’s a quick definition. A batch file is a text file that contains a sequence of commands for a computer operating system. It’s called a batch file because it batches (bundles or packages) into a single file a set of commands that would otherwise have to be presented to the system interactively from a keyboard one at a time.)
Here’s a diagram explaining how the batch script provided at the bottom of the page works. (Note that the folders aren’t copied just the files.)
So why would you use this?
Lets say you’re dealing with a system that has outputted thousands of folders into a single folder and each folder contains files.
You could be looking at millions of files to be gathered, a process that cannot be done manually.
Or a simpler use case might be you’ve downloaded thousands of movies and each was put into a separate folder. Now you just want the media files in one location.
How to use:
Open the program notepad.
Copy the code below and past it into notepad saving the file with the extension .bat
Place the batch file in the parent folder, i.e. the folder all the other folders are in.
Double click the batch file.
The batch file will create a folder one directory level above where the parent folder is located and copy all the files to this location.
An example of how to use the file is as follows:
If you had the parent folder “My Movies” on your desktop.
Pasting the batch file into this folder, and double clicking on it, would copy every movie into a folder called “Copied from My Movies” on your desktop.
And here’s the code,
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
|TITLE WAIT !|
|:: ASSIGN THE FILE PATH OF BATCH FILE TO A VARIABLE|
|:: GET THE NAME OF THE FOLDER WHICH THE BATCH FILE IS IN|
|FOR %%a IN (.) DO SET currentFolder=%%~na|
|:: GO UP ONE DIRECTORY|
|:: MAKE A DYNAMIC FOLDER NAME|
|::SET folderName=Copied From %currentFolder%|
|SET "folderName=Copied from %currentFolder%"|
|:: CREATE A FOLDER TO PUT THE COPIED FILES IN|
|:: IF FOLDER ALREADY EXISTS DELETE IT|
|IF EXIST "%folderName%" RMDIR "%folderName%" /S /Q|
|:: ASSIGN DESTINATION FOLDER TO A VARIABLE|
|:: CREATE A LOG FILE IN DESTINATION FOLDER|
|:: CREATE ERROR MESSAGE|
|IF NOT EXIST "%sourceDir%" (ECHO.Could not find %sourceDir% &GoTo:DONE)|
|:: OVERWRITE PREVIOUS LOG|
|echo.%date% – %time%|
|:: COPY FILES|
|FOR /F "Delims=" %%! IN ('DIR "%sourceDir%\" /b /s /a-d 2^>NUL') DO (|
|@XCOPY "%%!" "%destinationFolder%\" /i /y /h /f /c >>"%_report%",2>&1)|