Backup all DBs at once using T-SQL
-
I’m not sure if this should be in the backup forum or the T-SQL forum but I think it’s more a T-SQL question.
I’ve been programming for years but new to T-SQL and I’m having trouble. This is my problem:
We host a lot of DB’s and are always creating / deleting new ones. We have been using the wizard to create T-SQL scripts to backup the DB’s but sometimes it’s over looked! I want to create a final T-SQL script to backup everything!
This is my thought:
use master
declare @dbName char(100);
select @dbName = name from sysdatabases
loop though all DB’s names {
BACKUP DATABASE @dbName TO DISK = N'D:\MSSQL-Data\MSSQL\BACKUP\@dbName.bak' WITH INIT , NOUNLOAD , NAME = N'@dbName-FULL', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='@dbName' and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='@dbName')
RESTORE VERIFYONLY FROM DISK = N'D:\MSSQL-Data\MSSQL\BACKUP\@dbname.bak' WITH FILE = @i
}
I’m not sure if I can substitute @dbName inside the backup command like I’ve done. The backup command comes form the wizard BTW. I’m also not sure how to loop though the records found in the sysdatabases table.
-
That's a great help!
Ok, i've done up a little T-SQL using your example. I have two related questions.
1. The output that is generated, can i capture that to a file or a DB?
2. I get an error:
Server: Msg 3147, Level 16, State 1, Line 1
Backup and restore operations are not allowed on database tempdb.
How can i make sp_msforeachdb skip tempdb?
-
#1:
if ''?'' <> ''tempdb''
begin
..............
end
-
1. In the job step go to advanced and enter an output file location.
2. Try this, you can exclude db's as needed...
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
SELECT @sql = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''I:\MSSQL\BACKUP\'+@DBNAME+'.BAK''WITH INIT'
PRINT @sql
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end
-
JOY !!!!!!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply