Backup and Restore DB in multiple channels

Before taking the backup please check the database size


CREATE TABLE #databases
(
DATABASE_NAME VARCHAR(50),
DATABASE_SIZE FLOAT,
REMARKS VARCHAR(100)
)

INSERT #Databases EXEC ('EXEC sp_databases');

SELECT @@SERVERNAME AS SERVER_NAME,
       DATABASE_NAME,
       SYSMFM.source_file_name_main,
       SYSMFM.physical_name_main,
       SYSMFL.source_file_name_log,
       SYSMFL.physical_name_log,
       DATABASE_SIZE AS '(KB)',
       ROUND(DATABASE_SIZE / 1024, 2) AS '(MB)',
       ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS '(GB)',
       SYSDB.compatibility_level,
       SYSDB.create_date,
       SYSDB.database_id,
       SYSDB.collation_name,
       SYSDB.recovery_model,
       SYSDB.recovery_model_desc,
       SYSDB.user_access,
       SYSDB.user_access_desc,
       SYSDB.state,
       SYSDB.state_desc
  FROM #databases AS DB
       INNER JOIN sys.databases AS SYSDB ON DB.DATABASE_NAME = SYSDB.name
       INNER JOIN (SELECT database_id,
                          name AS source_file_name_main,
                          physical_name AS physical_name_main
                     FROM sys.master_files AS SYSMF
                    WHERE SYSMF.file_id = 1) AS SYSMFM ON SYSMFM.database_id = SYSDB.database_id
       INNER JOIN (SELECT database_id,
                          name AS source_file_name_log,
                          physical_name AS physical_name_log
                     FROM sys.master_files AS SYSMF
                    WHERE SYSMF.file_id = 2) AS SYSMFL ON SYSMFL.database_id = SYSDB.database_id
WHERE SYSDB.database_id = DB_ID('Hallengren')
 ORDER BY DATABASE_SIZE desc;

DROP TABLE #databases;


Before taking the backup please check the backup size:


USE Hallengren
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO



The reserved one is the closely to the backup size

Then take the actual backup and restore it in the other site:


-- Source –

-- Backup the database in multi channel with multi pieces
BACKUP DATABASE Hallengren TO
DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file1.bak',
DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file2.bak',
DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file3.bak',
DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Hallengren backup', NOSKIP , STATS = 10, NOFORMAT


-- Destination --

-- Create the empty database with rightly located mdf, ldf and ndfs

-- Then restore the database with below command

RESTORE DATABASE Hallengren FROM
DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file1.bak'
,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file2.bak'
,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file3.bak'
,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file4.bak'
WITH REPLACE,
MOVE 'Hallengren' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL003\MSSQL\DATA\Hallengren.mdf',
MOVE 'Hallengren_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL003\MSSQL\DATA\Hallengren_log.ldf'
GO

Comments

Post a Comment

Popular posts from this blog

Log is not getting shipped in SQL Server Logshipping

Image
Well Problem could be of different types. But common issues are few. Cause Inside the LSRestore job history, you can find out two kind of messages  :  – Restore job skipping the logs on secondary server  Skipped log backup file. Secondary DB: ‘logshippingSecondary’, File: ‘\\myDB\logshipping\logshippingPrimary_20090808173803.trn’ – Backup log older is missing *** Error 4305: The file ‘\\myDB\logshipping\logshippingPrimary_20090808174201.trn’ is too recent to apply to the secondary database ‘logshippingSecondary’. **** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored. Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for tran
Read more

SQL Loader with dynamic control file generation

Hi All, Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts 1) proc1: for filtering files and running a loop. Every source file will be processed in this loop. 2) another procedure proc2 which is called by proc1 to perform load operation proc1 ------- while (( $i < $filecounter )); do echo "file number $i" ### Take the first File FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'` dos2unix $FILENAME ### if filename exists already continue the loop ### FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';" OUTPUT_FILECOUNT=`/app/u01/oracle/prod
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al
Read more