en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אפר7

Written by: ronen ariely
07/04/2020 01:40 RssIcon

Let's assume that we have two databases A and B and we want to swap the names so the old database A will become B and the old database B will become A. 

In first glance you might think that you can simply backup both databases and restore database A with the name B and restore database B with the name A. This solution for swat two databases was presented in the MSDN forum

In this post I demonstrate one of the problematic issue in changing database name while restore it. The basic issue which I present is a case where the database name was hard coded used for example in Stored Procedure. Therefore, this solution for swapping databases is a risky procedure! 

USE master
GO
DROP DATABASE IF EXISTS A
GO
DROP DATABASE IF EXISTS B
GO
 
-- ALTER DATABASE [A] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- ALTER DATABASE [B] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
---- Starting...
create database A
GO
CREATE DATABASE B
GO
 
USE A
GO
 
create table A_T (id int)
GO
INSERT A_T(id) VALUES (1),(11),(111),(1111)
GO
 
CREATE PROCEDURE dbo.MySP AS BEGIN
    declare @stmt nvarchar(1000)
    SET @stmt = 'select id, DB = ''DBA'' from A.dbo.A_T'
    EXECUTE sp_executesql @stmt
END
GO
 
EXECUTE dbo.MySP
GO
/*
id  DB
1   DBA
11  DBA
111 DBA
1111    DBA
*/
 
 
USE B
GO
 
create table B_T (id int)
GO
INSERT B_T(id) VALUES (2),(22),(222),(2222)
GO
 
 
 
-- Full backup both databases
use master
GO
 
BACKUP DATABASE [A]
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\A.bak'
    with NAME = N'A-Full Database Backup'
GO
BACKUP DATABASE [B]
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\B.bak'
    with NAME = N'A-Full Database Backup'
GO
 
DROP DATABASE A
DROP DATABASE B
GO
 
-- Restore both databases
RESTORE DATABASE [B] -- We restore files of database A as new database B
    FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\A.bak'
    WITH  FILE = 1,
        MOVE N'A' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\B.mdf'
        MOVE N'A_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\B_log.ldf',
        NOUNLOAD,  REPLACE,  STATS = 5
GO
RESTORE DATABASE [A]  -- We restore files of database B as new database A
    FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\B.bak'
    WITH  FILE = 1,
        MOVE N'B' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\A.mdf'
        MOVE N'B_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\A_log.ldf',
        NOUNLOAD,  REPLACE,  STATS = 5
GO
 
USE B
GO
-- Notice that in the new database B we have the tables of the old database A as expeted
SELECT * FROM A_T
GO
-- this database used to be A and it is B now.
-- It includes the table of A abviously
 
-- WALLA!!!! DATAABSES WERE SWAPPED
 
 
-- ISSUE!
-- we use the new database B which inlcude the SP of the old database A with hard code of the database name
-- We expect to get the table of B since we are in database B
-- but the SP was hard coded to A so we get the content of the new database A!!!
 
EXECUTE dbo.MySP
GO
-- ERROR !!! Invalid object name 'A.dbo.A_T'
-- Since this was hard codded to 'A.dbo.A_T' but in the new restored database it is here 'B.dbo.A_T'

 

This procedure as you can see might fail and we should never count on it!