Please help before 4:15 today- Thanks

Question

Post reply

Please help before 4:15 today- Thanks

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
April 16, 2013 at 1:44 pm

#292282

I am posting the backup script to backup specific databases in subdirectories. Luckily the backup and subdirectories are creating but seperately. Can you please check the script and correct it so that backup should stores in subdirectorys.
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @BAK_PATH
-- Setting value of backup date and folder of the backup
SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\AdHoc_Backup\Full_Backup\'
SET @Baksql = ''
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR
SELECT NAME FROM SYS.DATABASES
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4 -- Exluding system databases
and NAME not in('TestAssetDB_new','TestAssetDB','Service Center','DSExAudit','Cordanterview','DSemailnder','BOEXI2','BOX2Audit','A2G','A2G_ew','Alris','Alris','AM11')
and NAME not like '%Manager'
-- Opening and fetching next values from sursor
OPEN c_bakup
FETCH NEXT FROM c_bakup INTO @BackupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BAK_PATH = @BackupFolder + @BackupFile
if (not exists(select 1 from @DirTree where subdirectory = @BackupFolder))
begin
-- create the subdirectory
EXEC master.dbo.xp_create_subdir @BAK_PATH
end
-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'''-- WITH COMPRESSION, INIT, STATS = 10;'
-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)
-- Opening and fetching next values from cursor
FETCH NEXT FROM c_bakup INTO @BackupFile
END
-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

did you test this script yourself yet?

does it run?

does it raise errors?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 2

As I said I ran successfully and created the backupfile as well the subdirectories but the backup files are not dumping in subdirectories.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Well, you printed the SQL to run the backups, what did it look like?

Snippet from your code: PRINT (@Baksql)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Please provide the script output from the print command.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

Or consider using this script:http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/

It works well.