SQL server Backup

Last updated: 2015-02-18

I never thought SQL server backup was easy to remember.

This is probably because the restore command involves 3 physical files, 2 logical names and the Db name.

So to help remember it, a picture might help:

Remember the SQL Login is outside of the Db, so it will have to be reattached using sp_change_users_login.

A nice to have backup script:

-- Backup
use master
BACKUP DATABASE MyDb
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDb20080915.bak'
-- Tip: You can Backup to a fileshare e.g. '\\someserver\someshare\somefile.bak'
-- Source. Print logical names to restore from disk (*.bak).
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDb20080915.bak'

/* Output
LogicalName PhysicalName Type FileGroupName Size
------------ ---------------------------------------------------------------------- ---- -------------- --------
MyDb C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb.mdf D PRIMARY 3145728
MyDb_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb_log.ldf L NULL 1048576
*/

-- Optional copy .bak to another machine, on where to import the database.
-- Let's say the .bak file to restore from is on the d: drive and same directory

-- Target. Print which .*df-files MyDb is already tied to:
sp_helpdb MyDb
/* output
name db_size owner dbid created status compatibility_level
----- -------- ------------ ----- ----------- ----------------------------------------------------------------
MyDb 4.00 MB rasor_rasor 17 Sep 14 2008 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE,
Version=611, Collation=Danish_Norwegian_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90

name fileid filename filegroup size maxsize growth usage
--------- ------ ---------------------------------------------------------------------- ----------- -------- -------------- ------- ---------
MyDb 1 d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb.mdf PRIMARY 3072 KB Unlimited 10% data only
MyDb_log 2 d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb_log.ldf NULL 1024 KB 2147483648 KB 10% log only
*/

-- optionally delete the target db:
use master
--drop database MyDb -- does also delete .mdf og .ldf

-- restore
RESTORE DATABASE MyDb
FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDb20080915.bak'
WITH
MOVE 'MyDb' TO 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb.mdf',
MOVE 'MyDb_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDb_log.ldf'
REPLACE -- use only replace, if existing DB was not dropped before restore

-- reattach the server login to the db user
USE MyDb
sp_change_users_login 'Auto_Fix','MyAccount'
/*
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
*/
USE [master]
GO
--EXEC master.dbo.sp_defaultdb @loginame= N'MyAccount', @defdb= N'MyDb' --optional
GO

use MyDb
sp_updatestats -- after restore

/*
-- reindex tables and views manually??
USE MyDb
GO
DBCC INDEXDEFRAG( N'MyDb', N'[dbo].[VIEW_SomeView]', N'VIEW_SomeView_cdx' )
GO
DBCC DBREINDEX(N'[dbo].[VIEW_SomeView]', N'VIEW_SomeView_cdx', 0)
GO
*/

/*
--Optional change sortorder
sp_helpdb 'MyDb'

--Output: status
--Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52

--change sortorder
--CI: Case Insensitive //o = O
--CS: Case Sensitive
--AI: Accent Insensitive //Ø = 0 = Ö
--AS: Accent Sensitive
ALTER DATABASE MyDb Collate SQL_Latin1_General_CP1_CI_AI
*/

You could restore into a brand new DB this way

USE [master]
-- Create new empty DB
CREATE DATABASE [MyDb_Restore] ON  PRIMARY
( NAME = N'MyDb_Restore', FILENAME = N'F:\MSSQL10.DB_ST01\MSSQL\DATA\MyDb_Restore.mdf' , SIZE = 102400KB , MAXSIZE = 10240000KB , FILEGROWTH = 10%)
 LOG ON
( NAME = N'MyDb_Restore_log', FILENAME = N'I:\MSSQL10.DB_ST01\MSSQL\Data\MyDb_Restore_log.ldf' , SIZE = 103424KB , FILEGROWTH = 102400KB )
GO

-- Restore a DB from another server into the new DB
RESTORE DATABASE [MyDb_Restore]
FROM  DISK = N'\\someserver24\Bak\MyDb_Full_20130416.bak'
WITH  FILE = 1,
MOVE N'MyDb' TO N'F:\MSSQL10.DB_ST01\MSSQL\DATA\MyDb_Restore.mdf',
MOVE N'MyDb_log' TO N'I:\MSSQL10.DB_ST01\MSSQL\Data\MyDb_Restore_log.ldf',
NOUNLOAD,  STATS = 5,
REPLACE -- use only replace, if existing DB was not dropped before restore
GO

In rare cases you would want to rename logical names inside a DB

-- You might need to (temporary) rename the logical DB name
USE [MyDb]
GO
ALTER DATABASE [MyDb] MODIFY FILE (NAME=N'MyDb', NEWNAME=N'MyDb2')
GO
USE [MyDb]
GO
ALTER DATABASE [MyDb] MODIFY FILE (NAME=N'MyDb_log', NEWNAME=N'MyDb2_log')
GO

If all restore (into new file) attempts  fails there is a possibility to reuse the original file location by using DOS SUBSt command like here:

REM Create drives L: and J: and overwrite physical files on those drives:
subst J: C:\data\j
subst L: C:\data\j
rem subst J: /D
rem subst L: /D

J:
MD "MSSQL10\MSSQL\DATA\"

Now restore was possible to same physical places as in prod:

USE [master]

RESTORE DATABASE [MyDB_Restore]
FROM DISK = N'C:\Data\DB_Bak\MyDB_Full_20130416.bak'
WITH FILE = 1,
--MOVE N'MyDB' TO N'C:\Users\raasor\MyDB.mdf',
--MOVE N'MyDB_log' TO N'C:\Users\raasor\MyDB_log.ldf',
NOUNLOAD, STATS = 5,
REPLACE -- use only replace, if existing DB was not dropped before restore
-- Overwriting physical files on L: and J:

Script the DB

Instead of using Backup/Restore you can script the DB from SMSS

In this example we delete the TargetDB, but not users and roles

-- TargetDB Content - Delete
-- DB: TargetDB - Tasks - Generate Scripts - Select objects: All except: Users, DB Roles, Schemas - 
-- Options: Save to file: \\somenas\someshare\TargetDB_DELETE_20150218_0800.sql
-- Options adv: Script Drop&Create: Drop
-- Script Data Compression opt: True
-- Script Full-Txt Indexes: True
-- Script Indexes: True
-- Script Triggers: True

…then we create content from SourceDB, but not users and roles

-- Source DB Content - Create
-- DB: SourceDB - Tasks - Generate Scripts - Select objects: All except: Users, DB Roles, Schemas - 
-- Options: Save to file: \\somenas\someshare\SourceDB_FULL_20150218_0810.sql
-- Options adv: Script Use DB: False
-- Type of data: Schema and data
-- Script Data Compression opt: True
-- Script Full-Txt Indexes: True
-- Script Indexes: True
-- Script Triggers: True

The end.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: