platformaerp.com

backup

Duplicate instance

20240514 - Bardzo przydatny skrypt do duplikowania instancji bazy danych.

DECLARE @oldname nvarchar(50)   = N'XXXXXXXXXXXX'   -- database name
declare @newname nvarchar(100)  = N'XXXXXXXXXXXX_TEST'      -- nowa nazwa
declare @logtype nvarchar(50) = '.log' --log,.ldf   -- support for log or ldf
-- Auto paths
DECLARE @backuppath nvarchar(500)   = (SELECT cast(SERVERPROPERTY ('InstanceDefaultBackupPath') as varchar)) --N'D:\Backup\'
DECLARE @newpath    nvarchar(500)   = (SELECT cast(SERVERPROPERTY ('InstanceDefaultDataPath') as varchar))
DECLARE @fileName   nvarchar(500)   = @backuppath + @oldname + '_' + CONVERT(VARCHAR(20),GETDATE(),112)+ N'.BAK' -- filename for backup

declare @oldldf nvarchar(500)   = (SELECT name  FROM  sys.master_files WHERE  database_id = db_id(@oldname) and (physical_name like N'%'+@logtype))
declare @oldmdf nvarchar(500)   = (SELECT name  FROM  sys.master_files WHERE  database_id = db_id(@oldname) and physical_name like N'%.mdf')
declare @newmdf nvarchar(500)   = @newpath + @newname+N'.mdf'
declare @newldf nvarchar(500)   = @newpath + @newname+@logtype

print '@backuppath  '+@backuppath
print '@newpath  '+@newpath
print '@filename  '+@filename
print '@oldldf  '+@oldldf
print '@oldmdf  '+@oldmdf
print '@newmdf  '+@newmdf
print '@newldf  '+@newldf

--backup
BACKUP DATABASE @oldname TO DISK = @fileName

--odtworzenie instancji
RESTORE DATABASE @newname
FROM  DISK =  @fileName
WITH  FILE = 1,
MOVE @oldmdf TO @newmdf,
MOVE @oldldf TO @newldf,
NOUNLOAD,  REPLACE,  STATS = 10

Backup instance

/*
Utworzenie kopii bazy danych
*/

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
declare @pathFORCOPY VARCHAR(256)

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SET @path = 'd:\data\BACKUP\' --+ @fileDate + '\'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like 'CRM_METALBUD_2016'  --('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
      BACKUP DATABASE @name TO DISK = @fileName
        --ODTWORZENIE INSTANCJI TESTOWEJ
    --if (DATEPART(DW, GETDATE()) =1) and @name like 'CRM_METALBUD_2016'  --neidziela i crm
    --Begin
        declare @newname varchar(100)  =@name +'_TEST'
        declare @mdf varchar(max) =N'D:\DATA\Database\' + @newname+'.mdf'
        declare @ldf varchar(max) =N'D:\DATA\Database\' + @newname+'.ldf'
        RESTORE DATABASE @newname
        FROM  DISK =  @fileName
        WITH  FILE = 1,
        MOVE N'xCRM' TO @mdf,
        MOVE N'xCRM_log' TO @ldf,
        NOUNLOAD,  REPLACE,  STATS = 10
    --End
       FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Drop database

use master
alter database [DATABASENAME] set single_user with rollback immediate
DROP DATABASE [DATABASENAME]