Migrating DBs to another server

Question

Post reply

Migrating DBs to another server

LearningDBA

SSCertifiable

Points: 6609
More actions
December 19, 2018 at 7:07 am

#373353

I am planning a migration for some DBs (around 20) so I have this script I use to take the backup of DBs and I am wondering if I get some help on how to use the script to restore all DBs. Scripts are below:
---------------------
/*Backup 1 database*/
---------------------

USE [DBMAINT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[backup_database]
       @DBName sysname = null

as
BEGIN
       declare @folder nvarchar(500)
       declare @path nvarchar(2000)
       declare @sql nvarchar(max)


       set @folder = 'L:\Backup\'

             set @path = @folder+QUOTENAME(@dbName)+'\'

       exec master.sys.xp_create_subdir @path

       set @sql = 'BACKUP DATABASE '+QUOTENAME(@dbName)+' TO DISK='''+@path+'\'
             +QUOTENAME(@dbName)
             +N'.bak' +''' with stats=10'

exec(@sql)

END

go

---------------------
/*Backup all database*/
---------------------

USE [DBMAINT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[backup_all_databases]

      @full_only tinyint = 0
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @name SYSNAME
       DECLARE dbcursor INSENSITIVE CURSOR FOR
             SELECT name
             FROM sys.databases
             WHERE STATE = 0 --Online
             AND name in ('DBMAINT','AddressTesting','AsureID')--<> 'tempdb'

       OPEN dbcursor

       FETCH NEXT FROM dbcursor INTO @name
             WHILE @@FETCH_STATUS = 0
             BEGIN
                    EXEC backup_database @dbName=@name
                    FETCH NEXT FROM dbcursor INTO @name
             END;

             CLOSE dbcursor
             DEALLOCATE dbcursor
END
--------------------------------------
/*Create a procedure to restore a DB*/
--------------------------------------

create procedure [dbo].[restore_database]
       @DBName sysname = null
as
BEGIN
       declare @folder nvarchar(500)
       declare @path nvarchar(2000)
       declare @sql nvarchar(max)

       set @folder = 'L:\Backup\'
      set @path = @folder+QUOTENAME(@dbName)+'\'

       set @sql =
       'use master
       restoreDATABASE '+QUOTENAME(@dbName)+' from DISK='''+@path+'\'+QUOTENAME(@dbName)+'\'+QUOTENAME(@dbName)+'.bak'' WITH FILE= 1,
       MOVE '+@DBName+ ' TO N''D:\Databases\'+@DBName+'.mdf'',
       MOVE '+@DBName+ '_log TO N''L:\Logs\'+@DBName+'_log.LDF'',
       NOUNLOAD, STATS = 5
      GO'
exec(@sql)
END[/code]
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

You can write a dynamic SQL script for both backup and restore by using a system tables.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Michael L John One Orange Chip Points: 27055 More actions · Answer 2

Instead of re-inventing the wheel, take a look at these tools:

https://dbatools.io/commands/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Michael L John - Wednesday, December 19, 2018 10:03 AM
Instead of re-inventing the wheel, take a look at these tools:
https://dbatools.io/commands/

I have not used this. Have to try. Thanks for link.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

LearningDBA SSCertifiable Points: 6609 More actions · Answer 4

This is the script I am using which I found online, made some changes and it's working just fine.
DECLARE @name VARCHAR(256) -- database name DECLARE @backuppath NVARCHAR(256) --– – path for backup files DECLARE @datapath VARCHAR(256) --– – path for data files DECLARE @logpath VARCHAR(256) --– – path for log files DECLARE @backupfileName VARCHAR(256) --– – filename for backup DECLARE @datafileName VARCHAR(256) --– – filename for database DECLARE @logfileName VARCHAR(256) --– – filename for logfile DECLARE @logName VARCHAR(256) -- filename for logfile

-- specify database backup directory

SET @backuppath = '\\ipaddress\Backup\'
SET @datapath = 'F:\F_DSQL16-DATA\Data\'
SET @logpath = 'F:\F_DSQL16-LOGS\Data\'

CREATE TABLE #files(fname varchar(200),depth int, file_ int)

INSERT #files

EXECUTE master.dbo.xp_dirtree @backuppath, 1, 1

SELECT * FROM #files

DECLARE files CURSOR FOR
SELECT fname FROM #files

OPEN files
FETCH NEXT FROM files INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
      DECLARE @cleanname AS VARCHAR(255)
      SET @cleanname = REPLACE(@name, '.BAK','')
      PRINT @cleanname
      SET @backupfileName = @backuppath + @name
      SET @datafileName = @datapath + @cleanname   + '.MDF'
      SET @logfileName = @logpath + @cleanname   + '_log.LDF'
      SET @logName = @cleanname + '_log'

      RESTORE DATABASE @cleanname
      FROM DISK = @backupfileName
      WITH RECOVERY,
      MOVE @cleanname TO @datafileName,
      MOVE @logName TO @logfileName

FETCH NEXT FROM files INTO @name

END

CLOSE files
DEALLOCATE files

DROP TABLE #files

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]