WordPress.com



---define variables and its values:setvar DATABASE UAT2010_Search:setvar SOURCE HQSPDBSU01:setvar DESTINATION HQSPDBSU02:setvar BACKUPPATH g:\MSSQL:setvar RESTOREPATH g:\MSSQL:setvar LOGICALDATANAME UAT2010_Search:setvar LOGICALLOGNAME UAT2010_Search_Log:setvar RESTOREDATAPATH "G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA":setvar RESTORELOGPATH "G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA":setvar COPYPATH g$\MSSQL:setvar Timeout 10---Precheck for an existence of database:CONNECT $(SOURCE)select * from sys.databases where name='$(DATABASE)'Go:CONNECT $(DESTINATION)select * from sys.databases where name='$(DATABASE)'SET NOCOUNT ONGO:CONNECT $(SOURCE)Goprint '*** Take full backup of Source database $(DATABASE) with copy_only option***'IF (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='10.50' and SERVERPROPERTY('EngineEdition')=3) OR (left(cast(SERVERPROPERTY('ProductVersion')as varchar),5)='10.00' and SERVERPROPERTY('EngineEdition')in(2,3))BEGIN-- Compression Option is setBACKUP DATABASE $(DATABASE) TO DISK = '$(BACKUPPATH)\$(DATABASE).bak'WITH COPY_ONLY, NOFORMAT, INIT, NAME = '$(DATABASE) Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10,COMPRESSIONENDELSEBEGIN--Backups are not compressed for older versions BACKUP DATABASE $(DATABASE) TO DISK = '$(BACKUPPATH)\$(DATABASE).bak'WITH COPY_ONLY, NOFORMAT, INIT, NAME = '$(DATABASE) Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10ENDGO----2. Copy the files from Source to Destination , Refer below link for more --information-- '*** Copy database $(DATABASE) from Source server $(Source) to Destination server $(destination) ***'!!ROBOCOPY $(BACKUPPATH)\ \\$(Destination)\$(COPYPATH) $(DATABASE).*GO---–3. Restore database to Destinationprint '*** Restore full backup of database $(DATABASE) ***':CONNECT $(Destination)GORESTORE DATABASE $(DATABASE) FROM disk = '$(RESTOREPATH)\$(DATABASE).bak' WITH RECOVERY, NOUNLOAD, STATS = 10,REPLACE, MOVE '$(LogicalDataName)' TO '$(RestoreDataPath)\$(LogicalDataName).mdf', MOVE '$(LogicalLogName)'TO '$(RestoreDataPath)\$(LogicalLogName).ldf'GO---Post Check for an existence of database on both source and destination:CONNECT $(SOURCE)select * from sys.databases where name='$(DATABASE)'Go:CONNECT $(DESTINATION)select * from sys.databases where name='$(DATABASE)'Output –Connecting to HQSPDBSU01...(1 row(s) affected)Disconnecting connection from HQSPDBSU01...Connecting to HQSPDBSU02...(0 row(s) affected)Disconnecting connection from HQSPDBSU02...Connecting to HQSPDBSU01...*** Take full backup of Source database UAT2010_Search with copy_only option***12 percent processed.22 percent processed.32 percent processed.42 percent processed.51 percent processed.60 percent processed.70 percent processed.80 percent processed.90 percent processed.Processed 5168 pages for database 'UAT2010_Search', file 'UAT2010_Search' on file 1.100 percent processed.Processed 3 pages for database 'UAT2010_Search', file 'UAT2010_Search_log' on file 1.BACKUP DATABASE successfully processed 5171 pages in 0.830 seconds (48.668 MB/sec).------------------------------------------------------------------------------- ROBOCOPY :: Robust File Copy for Windows ------------------------------------------------------------------------------- Started : Thu Aug 15 14:31:58 2013 Source : g:\MSSQL\ Dest : \\HQSPDBSU02\g$\MSSQL\ Files : UAT2010_Search.* Options : /COPY:DAT /R:1000000 /W:30 ------------------------------------------------------------------------------ 1g:\MSSQL\ Newer 6.6 mUAT2010_Search.bak 0.0% 7.5% 15.0% 22.6% 30.1% 37.7% 45.2% 52.8% 60.3% 67.9% 75.4% 83.0% 90.5% 98.0%100% ------------------------------------------------------------------------------ Total Copied Skipped Mismatch FAILED Extras Dirs : 1 0 1 0 0 0 Files : 1 1 0 0 0 0 Bytes : 6.62 m 6.62 m 0 0 0 0 Times : 0:00:00 0:00:00 0:00:00 0:00:00 Speed : 40397395 Bytes/sec. Speed : 2311.557 MegaBytes/min. Ended : Thu Aug 15 14:31:59 2013*** Copy database UAT2010_Search from Source server HQSPDBSU01 to Destination server HQSPDBSU02 ***Disconnecting connection from HQSPDBSU01...Connecting to HQSPDBSU02...*** Restore full backup of database UAT2010_Search ***12 percent processed.22 percent processed.32 percent processed.42 percent processed.51 percent processed.61 percent processed.71 percent processed.81 percent processed.91 percent processed.100 percent processed.Processed 5168 pages for database 'UAT2010_Search', file 'UAT2010_Search' on file 1.Processed 3 pages for database 'UAT2010_Search', file 'UAT2010_Search_log' on file 1.RESTORE DATABASE successfully processed 5171 pages in 0.450 seconds (89.765 MB/sec).Disconnecting connection from HQSPDBSU02...Connecting to HQSPDBSU01...(1 row(s) affected)Disconnecting connection from HQSPDBSU01...Connecting to HQSPDBSU02...(1 row(s) affected)Disconnecting connection from HQSPDBSU02... ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download