April 12, 2012 at 11:33 pm
Hi all
I want to know that suppose
We have multiple databases suppose
DB1,DB2...Db10 .Now I want to take the backup
of these databases.Can anyone please tell me
how to write stored procedure for this and also if i want to restore
these databases.cam anyone please guide me and tell me about how to write the procedure.is it possible???If yes then please tell me
April 12, 2012 at 11:43 pm
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2012 at 11:44 pm
Start by looking up BACKUP DATABASE and RESTORE DATABASE in Books Online.
April 13, 2012 at 12:07 am
No I mean sorry that i was not specific
I just want to know that can i write a
procedure with the help of which i can take the
backup of all databases and can restore all the databases.
rather to take backup individually.
April 13, 2012 at 12:15 am
First result on Google for sql server script backup all databases
Simple script to backup all SQL Server databases
[/url]
You do know you'll lose less time using Google than typing something in a forum and waiting for someone to reply, don't you?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 5:42 am
And instead of giving you a fish, we are trying to teach you to fish. Take some time to read Books Online. You will find the informatioon you need to learn to write BACKUP and RESTORE scripts. Also, you can do both using SSMS and you can even have it write the scripts for you by pressing the script button after setting all the options.
April 13, 2012 at 7:39 am
praspras42 (4/13/2012)
I just want to know that can i write a procedure with the help of which i can take the backup of all databases and can restore all the databases. rather to take backup individually.
Yes, it is possible.
Now go look at the links other posters have supplied. That will tell you how.
April 13, 2012 at 8:16 am
praspras42 (4/13/2012)
No I mean sorry that i was not specificI just want to know that can i write a
procedure with the help of which i can take the
backup of all databases and can restore all the databases.
rather to take backup individually.
Ola Hallengren put a lot of time and effort into his scripts, you may want to take a look at those.
Check this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2012 at 9:40 am
I hope below Script will Help you :
Backup all the databases
select name into #temp2 from master..sysdatabases
select 'BACKUP DATABASE ' +name+
' TO DISK = N''\\192.168.23.80\Backup\VITAS2SQLA_BACKUPS\Reporting server backup\'+name+'.bak'''+
' WITH NOFORMAT, NOINIT, NAME = N'''+Name+'-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO '
from #TEMP2
order by name
Restore them all in one go
create table #TEMP (dbname varchar(100), location varchar(1000))
go
delete #TEMP
go
declare @DBNAME varchar(100)
declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @DBNAME
while @@FETCH_STATUS=0
begin
insert #TEMP
SELECT top 1 database_name,physical_device_name FROM msdb.dbo.backupset bk, msdb.dbo.backupmediafamily bf
WHERE bk.media_set_id=bf.media_set_id and database_name=@DBNAME AND type ='D' ORDER BY backup_start_date DESC
print @DBNAME
fetch c1 into @DBNAME
end
close c1
deallocate c1
go
select
'RESTORE DATABASE ['+dbname+']
FROM DISK = N'''+ location+char(39)+'
WITH FILE = 1,MOVE N'''+dbname+''' TO N''D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\'+dbname+'.mdf'',
MOVE N'''+dbname+'_log'' TO N''L:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\'+dbname+'_log.ldf'', NOUNLOAD, STATS = 10
GO'
from #TEMP
order by dbname
Let me know if you have any query
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply