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.

Leave a Reply

1 Comment on "Backing up all SQL Server user databases to file"

Notify of
avatar
Sort by:   newest | oldest | most voted
world travelar
Guest

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

wpDiscuz

Redirecting shortly

%d bloggers like this: