May 23, 2017 at 1:38 am
Hello All,
I need to move about 50 DBs from my instance to anoter instance.
Can you please share with me a dynamic script that backup many DBs and another dynamic script that will restore the DBs backup on the 2nd instance?
Thanks in advance!!!
May 23, 2017 at 6:13 am
Hi all,
I found script that can dynamically made a backup for all DBs (run the script on the Source server and also execute the output):
/* Created By: AJAY DWIVEDI
Created Date: NOV 25, 2014
Purpose: Script out Take Backups
Total Inputs: 3
*/
DECLARE @ID TINYINT --DB No
DECLARE @name VARCHAR(50) -- database name
DECLARE @Is_Copy_only TINYINT
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @BackupString NVARCHAR(2000);
DECLARE @VerificationString NVARCHAR(2000);
--1) specify database backup directory
SET @path = 'c:\Backup1'
--2) Specify (True=1) or (False=0) for COPY_ONLY backup option
SET @Is_Copy_only = 1;
SELECT @fileDate = DATENAME(DAY,GETDATE())+CAST(DATENAME(MONTH,GETDATE()) AS VARCHAR(3))
+DATENAME(YEAR,GETDATE())+'_'+REPLACE(REPLACE(RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7),':',''), ' ','0')
--3) Specify your DB names for backup in case of data migration
DECLARE db_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY name) as ID, name
FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(NAME,'status') = 'ONLINE'
--AND name IN ('Pubs') -- Data Migration
AND name NOT IN ('master','model','msdb','tempdb') -- Instance Migration
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ID, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupString = '
-- '+CAST(@ID AS VARCHAR(2))+') ['+@name+']
EXEC master.sys.xp_create_subdir '''+@path+'\'+@name+''';
GO
BACKUP DATABASE ['+@name+'] TO DISK = '''+@path+'\'+@name+'\'+ @name + '_' + @fileDate + '.BAK''
WITH ';
IF(@Is_Copy_only = 1)
SET @BackupString = @BackupString + 'COPY_ONLY, ';
SET @BackupString = @BackupString + 'STATS = 10 ,CHECKSUM;
GO';
/*
SET @VerificationString = '
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'''+@name+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@name+''' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@name+''''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'''+@path+'\'+@name+'\'+ @name + '_' + @fileDate + '.BAK'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
';
*/
PRINT @BackupString;
-- PRINT @VerificationString;
FETCH NEXT FROM db_cursor INTO @ID, @name;
END
CLOSE db_cursor
DEALLOCATE db_cursor
May 25, 2017 at 6:39 am
For a restore of multiple use the dynamic script below.
Please run the script on the Source server and than run the output on the Target server.
set nocount on;
with cte (database_name, backup_finish_date, media_set_id)
as (
select msdb.dbo.backupset.database_name,
max(msdb.dbo.backupset.backup_finish_date),
max(media_set_id)
from msdb.dbo.backupset
join sys.databases
on msdb.dbo.backupset.database_name = sys.databases.name
where sys.databases.database_id > 4
and msdb.dbo.backupset.type = 'D'
group by msdb.dbo.backupset.database_name
)
select
--need to replace the '\' sign with the DefaultDataPath [on the target server run this command: select serverproperty('InstanceDefaultDataPath')]
--and with DefaultLogPath [on the target server run this command: select serverproperty('InstanceDefaultLogPath')]
'
USE [master]
RESTORE DATABASE ['+cte.database_name+'] FROM DISK = N'''+msdb.dbo.backupmediafamily.physical_device_name+''' WITH FILE = 1, MOVE N'''+typelog.name+''' TO N''\'+typelog.name+'.mdf'' , MOVE N'''+typedata.name+''' TO N''\'+typedata.name+'.ldf'' , NOUNLOAD, STATS = 10;
GO
'
from cte
join
msdb.dbo.backupmediafamily
on cte.media_set_id = msdb.dbo.backupmediafamily.media_set_id
join
sys.master_files typedata
on cte.database_name = db_name(typedata.database_id)
join
sys.master_files typelog
on typedata.database_id = typelog.database_id
where typedata.type = 1
and typelog.type = 0
and typedata.database_id>4
May 29, 2017 at 12:36 am
Hi,
if you like powershell, this link could help you to :
https://dbatools.io/functions/
Kind regards,
Andreas
May 30, 2017 at 4:31 am
89netanel - Tuesday, May 23, 2017 1:38 AMHello All,
I need to move about 50 DBs from my instance to anoter instance.
Can you please share with me a dynamic script that backup many DBs and another dynamic script that will restore the DBs backup on the 2nd instance?Thanks in advance!!!
If they're large databases i often prefer to offline the source databases, copy the database files to the target locations, then attach the files to the target SQL server.
You can always bring the old databaases online if need be
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2017 at 1:36 pm
/* Copy multiple databases via BACKUP - RESTORE
Assumptions:
SQL Server 2008 or later (set values in DECLARE, += operator, compression, copy_only)
URL is accessible from both source and destination, and has sufficient free space
No check for missing logins on destination server
Linked server exists on source server to destination, RPC enabled
*/
DECLARE @url NVARCHAR(1000) = N'\\Server\Share\Path\';
DECLARE @date CHAR(10) = CONVERT(CHAR(10), CURRENT_TIMESTAMP, 120);
DECLARE @cmd NVARCHAR(MAX) = '';
SELECT @cmd += 'BACKUP DATABASE [' + name + '] TO DISK=N''' + @url + name + '_copy_' + @date + '.bak'' WITH CHECKSUM, COMPRESSION, COPY_ONLY, INIT, STATS=10; '
FROM sys.databases
-- Adjust WHERE clause to pick your 50 databases
WHERE state_desc='ONLINE' AND name IN ('dasher', 'dancer', 'prancer', 'vixen', 'comet', 'cupid', 'donner', 'blitzen');
EXEC (@cmd);
SET @cmd = REPLACE(REPLACE(REPLACE(@cmd, 'BACKUP DATABASE', 'RESTORE DATABASE'), 'TO DISK', 'FROM DISK'), 'COMPRESSION, COPY_ONLY, INIT', 'RECOVERY, REPLACE');
EXEC (@cmd) AT [RemoteServer];
/* Advanced topics
Add WITH MOVE clauses to RESTORE if destination disk structure does not match source.
Query sys.master_files (or <dbname>.sys.database_files) to find all files for the source database.
You can query the destination server registry to get the default data and log folder, or query sys.master_files at the destination to get the most common file paths.
Add ", MOVE 'logical file name' TO 'new destination path\filename'" clauses to each RESTORE DATABASE.
Add "ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO sa;" so current user is not the owner.
Destination is a later version? Add "ALTER DATABASE [<dbname>] SET COMPATIBILITY_LEVEL=<destination server compatibility level>;"
Destination is a test server, no log backups? Add "ALTER DATABASE [<dbname>] SET RECOVERY SIMPLE;"
*/
May 31, 2017 at 5:53 pm
I usually just use the ola scripts for a backup of a bunch of databases as to the restores
I use a powershell script for the restore/refreshing. Fairly bulletproof for my needs.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply