Changing a SQL Backup Device Location

The time comes when we need to change where our SQL Backups are located. By Default they are placed in the root SQL backup directory. But as databases grow the need to move these is also needed. This quick tutorial will show you how to move a backup job quickly without needing to edit and re-configure the backup jobs. 

Lets go ahead and launch the SQL Server Management Studio from the start menu on our server. 

Login to SQL Server.

Scroll down to Server Objects > Backup Devices

In this example, we don't have any devices so we will add a couple. 

Since we are working with our Support database we will name this backup job Support_A. We will leave this backup in the default location. In this SQL 2012 its the Backup Folder within the root SQL installation directory. 

We do the same for our second backup device. This one we will name Support_B. We create two backup devices to our full database backups alternate between the two backup devices, giving us at least two full backups to restore from in case of emergency. 

Now we have our devices created we can change the location of where one of them is stored. To do this we click "new Query" above on the toolbar. 

We will be issuing three sets of commands. The first to delete the device, the second to recreate it, and the third to verify this all worked. 

The fist command would be 

EXEC master.dbo.sp_dropdevice
@logicalname = N'Support_A'
GO

Type this, highlight it and click the "Execute" button in the toolbar above. 

The second command re-created the same device but changes the loc
ation of where the file will be stored. 

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'Support_A',
@physicalname = N'\\NAS\Storage\SQL\Support_A.BAK'
GO

In this instance we are going to store the backup on a local NAS drive using the UNC path. 

Finally a simple command to verify this all worked. 

SELECT *
FROM sys.backup_devices
GO 

As you can see the Support_A device now reflects the new NAS storage location. 

Using this method means you will not need to  go into your backup jobs and re-configure anything. They will run like normal but will store the files where you specified. 

Shrinking SQL Log files

Sometimes SQL Log files can grow out of control. This is typical when a backup of the database is not setup or has been failing for one reason or another. This quick tutorial will show you have to shrink the log file back to a smaller size. 

Launch the SQL Server Management Studio from the start menu.

Login to your Database Server

Browse down to your DB that needs to be shrunk. Right click the DB and click Properties.

Once in the propertied of the DB click Files on the left side. This will give you the name of the log file that you wish to shrink.

Under Database File the first column is Logical Name, and you will need the logical name of the LOG File.

When done close the window by selecting Cancel below.

Now right click the Database and click New Query.

Determine what version of SQL you are working with if you are not sure.

But typing Select @@version and clicking the Execute button on the toolbar above you will see the results in the pane below. In this instance it is SQL 2012. 

It will take a total of 4 commands to shrink the file. These commands depend on the version of SQL you are running. Since we are 2012 the 4 commands are below. 

These commands work with 2008, 2012, and 2014.

Alter Database DatabaseName Set Recovery Simple
Alter Database DatabaseName Set Recovery Full
DBCC SHRINKFILE<TransactionLogName, 1)
Alter Database DatabaseName Set Recovery Simple
TransactionLogName is found above in step 4


After typing the commands in, highlight each command in order one at a time, and hit the execute button.

When you done you should be able to browse to the log file location and see the new size of 1 MB. 

The commands for SQL 2000 and SQL 2005 are below. 

Alter Database DatabaseName Set Recovery Simple

Alter Database DatabaseName Set Recovery Full
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)
Alter Database DatabaseName Set Recovery Simple

Setting up a Maintenance Plan for SQL Express 2014

Launch the SQL Server Management Studio from the start menu. 

Login to the SQL Server as the SA User.

The Database we will be working with VSDatabase, we can scroll down and click Server Objects then click Backup Devices.

Right click Backup Devices and choose New Backup Device.

Enter in the Backup Device name we typically names these after the database. Since we will be creating two devices lets use VSDatabase_a.

The file location defaults to the instance installation under the BACKUP folder.

Repeat the process for VSDatabase_b.

Open Notepad and enter in the following line of code.
Set LogName=%date:~4,2%%date:~7,2%%date:~10,4%


This sets the Variable LogName equal to today's date. In some cases if the date of the system (Europe) uses the "/" then add this line of code on the next separate line.

Set LogName=%LogName:/=-%

Next we will set the path to the log directory in SQL. This will depend on the version you are running.
//SQL 2008 R2
cd "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\"

//SQL 2012 
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log"

//SQL 2014 

cd "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log"

You can see in the example its ok to have them all as long as you comment the non valid options out. Two backslashes will make the line a comment and not run. 

Now we can create the Int-Opt file options. 

sqlcmd -E -S localhost -Q "DBCC CHECKDB(VSDatabase)"  >> %LogName%_CHECKDB.txt

sqlcmd -E -S localhost -Q "DBCC CHECKALLOC(VSDatabase)" >> %LogName%_CHECKALLOC.txt


sqlcmd -E -S localhost -Q "DBCC UPDATEUSAGE(VSDatabase)" >> %LogName%_UPDATE.txt

 Now save the file somewhere on the C drive or on the desktop for use later. We call this first file INT-OPT.bat 

(be sure show externtions for files is on so this does not get named .bat.txt)

You can reuse the same file and remove the INT-OPT code and add the DB backup code. 

sqlcmd -E -S localhost -Q "BACKUP DATABASE VSDatabase TO DISK = 'VSDatabase_a.BAK' WITH INIT" >> %LogName%_BACKUP.TXT

Save this file in the same location as INT-OPT.bat but name it VSDaatbase_A.bat

You can reuse the same file and remove the _A backup device and change this to the _B backup device. 

sqlcmd -E -S localhost -Q "BACKUP DATABASE VSDatabase TO DISK = 'VSDatabase_b.BAK' WITH INIT" >> %LogName%_BACKUP.TXT

Save this file in the same location as INT-OPT.bat but name it VSDaatbase_B.bat

Now that we have the .BAT files created and saved we can schedule them in the Task Scheduler to run as needed. 

Just right click the window and Choose "Create Basic Task"

We will call the first job INT-OPT since that is what it will run. 

This is a weekly Job.

It will run Sundays at 10:00 PM. 

Choose Start a Program and click Next to proceed.

Enter the path to the INT-OPT Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Enter in the admin username and password and click ok.

We start the process again this time naming the job VSDatabase_A.

This time this is a Daily Job. 

The job will run at 11:00 PM and it will occur every 2 days starting today. 

Choose Start a Program and click Next to proceed.

Enter the path to the VSDatabase_a Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Once you click OK - Enter in the admin username and password and click ok.

We start the process again this time naming the job VSDatabase_B.

This time this is a Daily Job. 

The job will run at 11:00 PM and it will occur every 2 days starting tomorrow.

This is important, this must start with tomorrows date to be sure the two jobs run on different days!  

Choose Start a Program and click Next to proceed.

Enter the path to the VSDatabase_b Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Once you click OK - Enter in the admin username and password and click ok.

You should run the jobs and also check the "LOG" location in the SQL directory to check for any errors. 

You can download the demo text file here.