about restore

  • 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

  • BACKUP (Transact-SQL)

    RESTORE (Transact-SQL)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Start by looking up BACKUP DATABASE and RESTORE DATABASE in Books Online.

  • 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.

  • 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

  • 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.

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • praspras42 (4/13/2012)


    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.

    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" 😉

  • 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