Backup/Restore | SQL Freelancer Blog

Creating a Backup plan on SQL Express using Ola Hallengren’s scripts

By bradyupton in Backup/Restore, Maintenance March 23, 2016 0 Comment

SQL Express doesn’t have the SQL Server Agent so we can’t schedule jobs like normal. Follow this post to create a backup plan that will back up all the databases using Windows Task Scheduler.

Download CommandExecute – https://ola.hallengren.com/scripts/CommandExecute.sql
Download DatabaseBackup – https://ola.hallengren.com/scripts/DatabaseBackup.sql
Execute both of these stored procedures against the target server

Open Notepad on the target server and copy the following code changing the path to where the backups will be stored and the cleanup time. The cleanup time is specified in hours :

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backups',
@BackupType = 'FULL',
@Compress = 'Y', 
@CleanupTime = 48

Save the file with a .sql extension

Start the Task Scheduler by clicking on StartMenu/All Programs/Accessories/System Tools/Task Schedule
Click Create Basic Task to start the Scheduled Task Wizard
Type a name for the Task

Choose Daily from the scheduling options

Click Next, specify the information about the time to run the task. Set Start Time to an appropriate value when the load on the server is low. Set the recur every option to 1 day and click Next

Click Browse. Browse to SQLCMD.exe (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE) and then click Open
Type the following content to the Add arguments text box and then click Next
- –S ServerName –i “c:\SQLScripts\SQLBackups.sql”

Select the checkbox to Open the Advanced Properties for this task and click Finish

Check Run whether user is logged in or not and Run with highest privileges under Security Options then click OK.

**Note**

You may have to run this in the command prompt until it succeeds and copy exactly from CMD into the arguments section

Downgrade from SQL Server Enterprise Edition to Standard Edition

By bradyupton in Upgrades/Migrations November 4, 2013 0 Comment

I’ve came across a lot of SQL Server’s in my career that were installed using the Enterprise Edition and never utilized the Enterprise features. Downgrading to a lower edition cannot be done in place. This tip will describe the steps needed to downgrade.

Create Database Backups

As always, before doing anything drastic (or non-drastic), BACKUP all the databases (system and user)! Also, if you have a development environment, please test on it first.

Check for Enterprise features

After backing up the databases run the following query on each database:

SELECT * FROM sys.dm_db_persisted_sku_features

This DMV will tell you whether or not the database is utilizing any of the Enterprise features.

Check Version and Build Number

Because you never know when an install may fail, check the version and build number of the current SQL Server. After the downgrade, you will need to bring the new SQL Server back to the patch level. You can view this information by executing the following command:

SELECT @@VERSION

Save the results to a text editor.

Decision…

There are two different directions we can go from here.

We can do the traditional uninstall Enterprise, install Standard, patch, restore databases
Or we can do what I call “The Jonathan Kehayias” approach. (I saw this method awhile back from a forum post by Jonathan):

Copy System Databases

Shutdown the SQL Server service and copy the master, model and msdb database files (.mdf and .ldf) to another location. We will need these later:

Uninstall SQL Server

Uninstall SQL Server from Control Panel:

You only need to uninstall the Instance (includes SSIS, SSAS, SSRS, SSDT). There is no need to uninstall the Shared Components (SSMS, Client Tools, and Connectivity).

Reboot

Reboot the server. After rebooting, browse to the location of the data files and you will notice that the user databases weren’t removed with the uninstall, but the system databases were. This is why we copied them in the step above:

Click here to view the rest of this post.

Restore SQL Server Databases using DateTime functions

By bradyupton in Backup/Restore January 9, 2013 0 Comment

If you take full backups using SQL Server maintenance plans and let SQL Server use the default naming convention, you have probably noticed that usually you’ll have file name in the format of “database name + backup + date + time + .bak”. For example, a backup from the master database may look like this: “master_backup_2012_10_02_220558_8601773.bak”. It can be a challenge to script out automatic restores because the numbers on the end of the backup name constantly change. In this tip I will explain how to script out RESTORE DATABASE statements using DateTime functions.

Let’s say we have a folder full of backups like this:

Let’s say our boss wants us to restore Monday’s production backup (Alabama) every Friday afternoon to our development database (Tide). To accomplish this task, we can use the built-in SQL Server DateTime functions.

The below script will restore from the backup created on the first day of the current week. I’ve added comments to explain the code.

-- Declare variables
DECLARE @backup nvarchar(200) 
DECLARE @datebegin datetime
DECLARE @dateend datetime

-- Initalize variables
-- Set @datebegin equal to the first day of the current week
SELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
-- Set @dateend equal to the second day of the current week 
SELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) 

-- Set @backup equal to query dependent on datebegin and dateend 
SELECT TOP 1 @backup = name + '.bak' 
FROM msdb..backupset 
WHERE database_name = 'Alabama' 
AND backup_start_date BETWEEN @datebegin AND @dateend 
AND type = 'D' -- D is for full backups
ORDER BY backup_start_date ASC 

USE [master]

-- Put DB in Single_User Mode
ALTER DATABASE [Tide] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

-- Restore DB using query from @backup variable
RESTORE DATABASE [Tide] FROM  DISK = @backup WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 
GO

Below is a table of useful DateTime functions that you can use for the @datebegin and @dateend variables.

Day	SQL
Today	SELECT GETDATE()
Yesterday	SELECT DATEADD(d, -1, GETDATE())
First Day of Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Last Day of the Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)
First Day of the Current Month	SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
Last Day of the Current Month	SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
First Day of the Current Year	SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Last Day of the Current Year	SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

Another example may include where you need to take a backup from the first of the month of the production database and restore it weekly to the development database. In this situation you can edit the @datebegin and @dateend variables:

--Set @datebegin equal to the first day of the current month
SELECT @datebegin = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 

--Set @dateend equal to the second day of the current month
SELECT @dateend = SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1)

Click here to view the rest of this post.

SQL Server Log Shipping

By bradyupton in High Availability, Log Shipping August 24, 2011 0 Comment

Backup Database on Primary Server:

Restore Database on Secondary Server: (RESTORE WITH STANDBY)

(Picture of Object Explorer once restore is complete)

Create a shared folder on the primary server (SQL Server Agent service account must have read/write permissions):

Create a shared folder on the secondary server (SQL Server Agent service account must have read/write permissions):

Enable Log Shipping at the Publisher:

Click “Enable this as a primary database in a log shipping configuration”, then click Backup Settings:

Enter Network Share on Primary Server
Enter Local Folder path on Primary Server
Enter the number of Minutes, Hours, or Days to keep the deleted files and to Alert if no backup occurs
Name the SQL Agent Job and determine a schedule to backup the log files

Click OK

Click Add to add a secondary server:

Click Connect to connect to the secondary server:

Select No, since we initialized the database in the beginning by restoring in Standby Mode, otherwise you can choose one of the other options. Hit OK.

Go to next tab, Copy Files.

Enter Local Folder Path on Secondary server
Enter the number of Minutes, Hours, or Days to delete copied files
Name the SQL Agent Job and determine a schedule to copy the log files

Go to next tab, Restore Transaction Log.

Since we put the database in Standby/Read Only mode select Standby Mode
If you would like to delay restoring the transaction log you can enter a value in the “Delay restoring backups at least” otherwise leave at 0 minutes.
Enter the number of Minutes, Hours, or Days to Alert if no restore occurs
Name the SQL Agent Job and determine a schedule to restore the log files

Hit OK

**OPTIONAL** To create a monitor server click “Use monitor server instance” and then click Settings

Click Connect to connect to a monitor server instance
Select the login method you would like to use to connect to the monitor server.
Enter the number of Minutes, Hours, or Days to delete log file data
Name the SQL Agent Job and determine a schedule to for the alert task

Hit OK twice.