On moving to Seattle…and reviving my camera…
March 6, 2013
CASE WHEN “I need to learn more T-SQL” THEN GoogleTheResult() END
March 13, 2013
Show all

Backing up all SQL Server user databases to file

The ultimate lazy backup script. I wasn’t feeling to selective about which databases to backup, and I figured, it’d be best to back them all up, even that testing database I occasionally use for data import/export. Never know when I might need it again…

This sits inside a SQL Server Agent job, and runs nightly. Simple enough really.

All works as expected on SQL Server 2012.

DECLARE @Database varchar(max)
DECLARE @path varchar(max)
SET @path = concat(N'D:\BACKUP\',convert(varchar,getdate(),112),' ALL DATABASES BACKUP.bak')
DECLARE my_cursor CURSOR FOR
select name from sys.databases where database_id>4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BACKUP DATABASE @Database TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'SQL Server all user databases backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM my_cursor
INTO @Database
END
CLOSE my_cursor
DEALLOCATE my_cursor

I’m sure there’s a better way to do this, but there doesn’t appear to be a column in the sys.databases table which denotes a database as a system or user database. Instead, I’ve used the WHERE database_id > 4 – which removes master, model, msdb, and tempdb from the backup.

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
world travelar Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest most voted
Notify of
world travelar
Guest
world travelar

By using this SQL query you can backup a SQL server database.
Single Database Backup:
BACKUP DATABASE DataBaseName TO DISK = ‘FileName’
All Database Backup: http://cybarlab.blogspot.com/2012/12/sql-server-database-backup.html
Hope it will help you

%d bloggers like this: