Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Database Backup


 / Published in: SQL
 

  1. DECLARE @DBName varchar(255)
  2.  
  3. DECLARE @DATABASES_Fetch int
  4.  
  5. DECLARE DATABASES_CURSOR CURSOR FOR
  6. SELECT
  7. DATABASE_NAME = db_name(s_mf.database_id)
  8. FROM
  9. sys.master_files s_mf
  10. WHERE
  11. -- ONLINE
  12. s_mf.state = 0
  13.  
  14. -- Only look at databases to which we have access
  15. AND has_dbaccess(db_name(s_mf.database_id)) = 1
  16.  
  17. -- Not master, tempdb or model
  18. AND db_name(s_mf.database_id) NOT IN ('Master','tempdb','model')
  19. GROUP BY s_mf.database_id
  20. ORDER BY 1
  21.  
  22. OPEN DATABASES_CURSOR
  23.  
  24. FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
  25.  
  26. WHILE @@FETCH_STATUS = 0
  27. BEGIN
  28. declare @DBFileName varchar(256)
  29. SET @DBFileName = datename(dw, getdate()) + ' - ' +
  30. REPLACE(REPLACE(@DBName,':','_'),'\','_')
  31.  
  32. exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''c:\db backup\' +
  33. @DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
  34. @DBName + '-Full DATABASE Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')
  35.  
  36. FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
  37. END
  38.  
  39. CLOSE DATABASES_CURSOR
  40. DEALLOCATE DATABASES_CURSOR

Report this snippet  

You need to login to post a comment.