Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Doing backup of entire SQL at once

leave a comment »

So, I’m working on migration of our intranet based on WSS 3.0 to the latest SharePoint 2013. As part of that process you need to make backup of all databases and move them to new server. Sure, you can do that one by one, but wouldn’t be easier to just do it at once. Here is a script you can use for it :

declare @name varchar(1000)
declare @sql varchar(8000)

declare kur SCROLL cursor for
SELECT name FROM master..sysdatabases
OPEN kur;
FETCH NEXT FROM kur INTO @name;
WHILE @@FETCH_STATUS=0
BEGIN

if (@name<>’tempdb’)
begin
PRINT ‘*** ‘ + @name;
set @sql=’backup database [‘+@name+’]
to disk = ”z:\’+@name+’_20130712_1000.bak”
with copy_only’
exec(@sql)
end

FETCH NEXT FROM kur INTO @name;
END
CLOSE kur
DEALLOCATE kur

If you combine above script with my previous post around mapping network path in SQL session then you can move those backup straight to remote server without storing them locally.

hope that this make you day easier

Advertisements

Written by Mariusz Gorzoch

12 July 2013 at 10:06

Posted in Bez kategorii

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: