Here I am going to share how can you take the database backup of all the SQL Server databases at once instead of taking it individually.
First, you need to create a folder in any drive to store the backup of database which you are going to take. For example, you have to create a folder named "dbBackup" in my drive "C:\"
Then, copy the below code & paste it in your SQL Server Query Executer page:
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0
-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1
-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ' - ' +
replace(replace(@DBName,':','_'),'\','_')
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''c:\dbBackup\' +
@DBFileName + '.bak' + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
Now execute the page and check your Folder "dbBackup" in Drive "C:/" . You will find all the databases backup in it.
Now, you are done! Simple, right?
HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.