littlemoon Posted February 6, 2013 Report Posted February 6, 2013 sql 2005 to 2008 migration cheshtunamu now question is oka database ni one server nunchi inko server ki migrate chesthunapudu... is it possible to write a query which picks up the backup file --copies onto new server -- and restores the data? auto restore laga GUI use cheyakunda.. also sercurity permissions and rules kuda migrate avali ante ela cheyadam ? 2 seprate queries laga rayala? emaina sample scripts unte pls share.. [size=4] [/size] ila konni links dorikayi but mari comples anpisthundi.. http://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts http://www.dbforums.com/microsoft-sql-server/1673431-how-make-sql-script-will-restore-backup-database.html [size=4] [/size]
tom bhayya Posted February 6, 2013 Report Posted February 6, 2013 [img]http://lh3.ggpht.com/-8RwT8_LLHM4/UOoO_Lps31I/AAAAAAAAIyk/-Y1PHoxQ2yY/s150/Brahmi.gif[/img]. .net kadha dba job kuda neekey ichaara moonu
littlemoon Posted February 6, 2013 Author Report Posted February 6, 2013 [quote name='Alexander' timestamp='1360177101' post='1303236054'] [img]http://lh3.ggpht.com/-8RwT8_LLHM4/UOoO_Lps31I/AAAAAAAAIyk/-Y1PHoxQ2yY/s150/Brahmi.gif[/img]. .net kadha dba job kuda neekey ichaara moonu [/quote] [size=4] [/size][size=4] ento e work [/size]
Vaampire Posted February 6, 2013 Report Posted February 6, 2013 yes its possible. ela ani nannu adagaku. some dba people should help it. maa company production database ni every ni8 back up chesukoni reports create chesey server lo restore chesthundi automatic gaaa. edo schedule untadi. daantlo script endo naaku telvadu
littlemoon Posted February 6, 2013 Author Report Posted February 6, 2013 [quote name='Vaampire' timestamp='1360177381' post='1303236084'] yes its possible. ela ani nannu adagaku. some dba people should help it. maa company production database ni every ni8 back up chesukoni reports create chesey server lo restore chesthundi automatic gaaa. edo schedule untadi. daantlo script endo naaku telvadu [/quote] thanks for the info... SSIS package laga use chesthunara ? or just normal sql job?
ChampakDas Posted February 6, 2013 Report Posted February 6, 2013 1. detach db present on 2005 ... '.mdf' files one server to another server copy chesi..then attach them to 2008. 2. login's and passwords separate ga cheyali anukunta... ee rendu process oke script lo peduthey oke step lo ipotundhi...2nd step ela chestharu anta..never tried though msdn kada trustable http://support.microsoft.com/kb/918992/en-us?p=1
subbayya Posted February 6, 2013 Report Posted February 6, 2013 [quote name='littlemoon' timestamp='1360176958' post='1303236031'] sql 2005 to 2008 migration cheshtunamu now question is oka database ni one server nunchi inko server ki migrate chesthunapudu... is it possible to write a query which picks up the backup file --copies onto new server -- and restores the data? auto restore laga GUI use cheyakunda.. also sercurity permissions and rules kuda migrate avali ante ela cheyadam ? 2 seprate queries laga rayala? emaina sample scripts unte pls share.. ila konni links dorikayi but mari comples anpisthundi.. [url="http://www.mssqltips.com/sqlservertip/1243/auto-generate-sql-server-database-restore-scripts"]http://www.mssqltips...restore-scripts[/url] [url="http://www.dbforums.com/microsoft-sql-server/1673431-how-make-sql-script-will-restore-backup-database.html"]http://www.dbforums....p-database.html[/url] [size=4] [/size] [/quote] Oracle ayithe vunnai ba... Oracle servers *NIX platforms kabatti na daggara only shell scripts vunnai... but vatini copy chesi post chesthe security breach avuthadi...network vallu itte pattestharu...
Ranga_The_Donga Posted February 6, 2013 Report Posted February 6, 2013 [color=#333333][font=verdana, tahoma, arial, sans-serif][size=3] Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.[/size][/font][/color][color=#333333][font=verdana, tahoma, arial, sans-serif][size=3] [b]Step 1:[/b] Retrive the Logical file name of the database from backup. [color=blue]RESTORE [/color][color=black]FILELISTONLY[/color] [color=blue]FROM DISK = [/color][color=red]'D:BackUpYourBaackUpFile.bak'[/color] [color=black]GO[/color] [b]Step 2:[/b] Use the values in the LogicalName Column in following Step. [color=green]----Make Database to single user Mode[/color] [color=blue]ALTER DATABASE [/color][color=black]YourDB[/color] [color=blue]SET [/color][color=black]SINGLE_USER [/color][color=blue]WITH ROLLBACK [/color][color=black]IMMEDIATE[/color][/size][/font][/color][color=#333333][font=verdana, tahoma, arial, sans-serif][size=3] [color=green]----Restore Database[/color] [color=blue]RESTORE DATABASE [/color][color=black]YourDB[/color] [color=blue]FROM DISK = [/color][color=red]'D:BackUpYourBaackUpFile.bak'[/color] [color=blue]WITH [/color][color=black]MOVE [/color][color=red]'YourMDFLogicalName' [/color][color=blue]TO [/color][color=red]'D:DataYourMDFFile.mdf'[/color][color=gray],[/color] [color=black]MOVE [/color][color=red]'YourLDFLogicalName' [/color][color=blue]TO [/color][color=red]'D:DataYourLDFFile.ldf'[/color][/size][/font][/color][color=#333333][font=verdana, tahoma, arial, sans-serif][size=3] [color=green]/*If there is no error in statement before database will be in multiuser mode. If error occurs please execute following command it will convert database in multi user.*/[/color] [color=blue]ALTER DATABASE [/color][color=black]YourDB [/color][color=blue]SET [/color][color=black]MULTI_USER GO[/color][/size][/font][/color]
Vaampire Posted February 6, 2013 Report Posted February 6, 2013 [quote name='littlemoon' timestamp='1360177581' post='1303236090'] thanks for the info... SSIS package laga use chesthunara ? or just normal sql job? [/quote] ssis package aithey kaadu. edo script anukunta. i cant ask them details
150bryant Posted February 6, 2013 Report Posted February 6, 2013 DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'C:\Backup\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor [color=#000000][font=Verdana, Arial, Helvetica, sans-serif][size=3]Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the [/size][/font][/color][b]@path[/b][color=#000000][font=Verdana, Arial, Helvetica, sans-serif][size=3] to the appropriate backup directory.[/size][/font][/color]
150bryant Posted February 6, 2013 Report Posted February 6, 2013 http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
littlemoon Posted February 6, 2013 Author Report Posted February 6, 2013 [quote name='GatisKandis' timestamp='1360177629' post='1303236094'] 1. detach db present on 2005 ... '.mdf' files one server to another server copy chesi..then attach them to 2008. 2. login's and passwords separate ga cheyali anukunta... ee rendu process oke script lo peduthey oke step lo ipotundhi...2nd step ela chestharu anta..never tried though msdn kada trustable [url="http://support.microsoft.com/kb/918992/en-us?p=1"]http://support.micro...18992/en-us?p=1[/url] [/quote] will chk the link thank u ... kani detach enduku ? prod DB's ivi can't take them offline... n na degara backup files unayi.. asalu ardham kani point naku how to write a query to auto copy the .bak file from 1 server to another.. linked server emaina cheyala?
150bryant Posted February 6, 2013 Report Posted February 6, 2013 http://stackoverflow.com/questions/10688274/sql-server-backup-script http://ola.hallengren.com/ http://msdn.microsoft.com/en-us/library/ms187510.aspx
150bryant Posted February 6, 2013 Report Posted February 6, 2013 [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]Hi there,[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]I am trying to migrate some of SQL Server 2005 databases to SQL Server 2008 R2. I first used the backup script generated by SQL Server 2005 Management Studio to back up my databases; then used the restore script generated by SQL Server 2008 R2 Management Studio to back up my databases.[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]backup script example:[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]-------------------------------[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]BACKUP DATABASE [ADINA] TO DISK = N'F:\SQLbackup\UpgradeTo2008R2\ADINA.bak' WITH NOFORMAT, INIT, NAME = N'ADINA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 [/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]GO[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]Restore script example:[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]-------------------------------[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]RESTORE DATABASE [ADINA] FROM DISK= N'\\192.168.100.100\Sqlbackup$\UpgradeTo2008R2\ADINA.bak' WITH FILE = 1, MOVE N'ADINA_dat' TO N'D:\SQLMSN\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ADINA.mdf', MOVE N'ADINA_log' TO N'D:\SQLMSN\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ADINA.ldf', NOUNLOAD, REPLACE, STATS = 10 [/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]GO[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]All the databases seemed to be restored fine. I could see the tables in each database. I also set up a maintenance plan to back up these database daily. However next day I found that the backup maintenance job failed for those databases that use "Full" recovery model with the following message: [/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]Executing the query "BACKUP DATABASE [ADINA] TO DISK = N'F:\\SQLbackup\\..." failed with the following error: "Database 'ADINA' cannot be opened. It is in the middle of a restore.[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]The database's status is 'Restoring' and I cannot see the tables. Why is that? I could see the tables immediately after restoring it.[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]The above approach worked for databases with "Simple" recovery model.[/background][/size][/font][/color] [color=#444444][font=Arial, sans-serif][size=3][background=rgb(244, 248, 251)]Please issue RESTORE DATABASE [your db name] WITH RECOVERY[/background][/size][/font][/color]
150bryant Posted February 6, 2013 Report Posted February 6, 2013 http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/
.thumb.jpg.ce42bb16ec102475a78c2087ce269c7d.jpg)
Recommended Posts