04/05/2016
OK everyone, it been a while since I been on my page. Here is a script I wrote to back entire MsSQL sever database to a new location or hard drive or just backup entire server to one backup file.
I could be a very use able tool for DBAs to quickly copy an entire server database to a new server or attach from a new hard drive to back to server.
--iBay DB_Backup written by Roy Zahid
DECLARE VARCHAR(50) -- database name
DECLARE VARCHAR(256) -- path for backup files
DECLARE VARCHAR(256) -- copy string
DECLARE VARCHAR(256) -- backup filename
DECLARE VARCHAR(20) -- used for file name
DECLARE nvarchar(1000) -- Path of Data
DECLARE nvarchar(1000) -- attache data file
DECLARE nvarchar(1000) -- attach log file
-- specity database dirctory
SET = N'D:\Data\MsSQL';
-- specify database backup directory
SET = N'I:\Data\MsSQL';
-- specify filename format
--SELECT = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO
WHILE @ = 0
BEGIN
-- uncomment this two lines to just backup entire server
--SET = + + '_' + + '.BAK'
--BACKUP DATABASE TO DISK =
-- copy data code blake to new location and reconneting database
EXEC sp_detach_db , 'true';
SET = N'xcopy /S /E /Y ' + + '\' + + ' ' + + '\' + + '\';
EXEC master.dbo.xp_cmdshell
SET = + '\' + + '\' + + '.mdf'
SET = + '\' + + '\' + + '_log.ldf';
EXEC sp_attach_db , ,
-- end copy data
FETCH NEXT FROM db_cursor INTO
END
CLOSE db_cursor
DEALLOCATE db_cursor