|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Backup SQL ServerI use MS SQL Server Express 2005 (the 'unpaid' version). I managed to create
a backup of my database using an option onder 'Server Objects'. My questions: 1. Is it possible to schedule this backup or is this only a feature of the full version of MS SQL Server? 2. How can I restore a database from a backup? Any help is appreciated. Jos Roijakkers (j.roijakk***@qred-it.nl) writes:
> I use MS SQL Server Express 2005 (the 'unpaid' version). I managed to SQL Express does not come with SQL Server Agent. But isn't there some sort > create a backup of my database using an option onder 'Server Objects'. > My questions: > > 1. Is it possible to schedule this backup or is this only a feature of the > full version of MS SQL Server? of scheduler built-in in Windows? You spark off a BACKUP command from OSQL that you schuedule. > 2. How can I restore a database from a backup? You use the RESTORE command, which is documented in Books Online. The simple operation is: RESTORE DATABASE db FROM DISK = 'c:\backup\yourbackup.bak' It gets more complicated if you want to restore the database under a different name. There is also a dialogue for restoring databases in SQL Server Management Studio Express. (At least I assume there is.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks
Show quote > Jos Roijakkers (j.roijakk***@qred-it.nl) writes: > >> I use MS SQL Server Express 2005 (the 'unpaid' version). I managed to >> create a backup of my database using an option onder 'Server >> Objects'. My questions: >> >> 1. Is it possible to schedule this backup or is this only a feature >> of the full version of MS SQL Server? >> > SQL Express does not come with SQL Server Agent. But isn't there some > sort > of scheduler built-in in Windows? You spark off a BACKUP command from > OSQL that you schuedule. >> 2. How can I restore a database from a backup? >> > You use the RESTORE command, which is documented in Books Online. The > simple operation is: > > RESTORE DATABASE db FROM DISK = 'c:\backup\yourbackup.bak' > > It gets more complicated if you want to restore the database under a > different name. > > There is also a dialogue for restoring databases in SQL Server > Management Studio Express. (At least I assume there is.) > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books. > mspx Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > 1: Seems this article is for you: http://www.sqldbatips.com/showarticle.asp?ID=27
2: Using the RESTORE command. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Jos Roijakkers" <j.roijakk***@qred-it.nl> wrote in message news:5d4a1f3d9dd28c840efa3e7622a@news.microsoft.com... >I use MS SQL Server Express 2005 (the 'unpaid' version). I managed to create a backup of my >database using an option onder 'Server Objects'. My questions: > > 1. Is it possible to schedule this backup or is this only a feature of the full version of MS SQL > Server? > > 2. How can I restore a database from a backup? > > Any help is appreciated. > > Thanks
Show quote > 1: Seems this article is for you: > http://www.sqldbatips.com/showarticle.asp?ID=27 > > 2: Using the RESTORE command. > > "Jos Roijakkers" <j.roijakk***@qred-it.nl> wrote in message > news:5d4a1f3d9dd28c840efa3e7622a@news.microsoft.com... > >> I use MS SQL Server Express 2005 (the 'unpaid' version). I managed to >> create a backup of my database using an option onder 'Server >> Objects'. My questions: >> >> 1. Is it possible to schedule this backup or is this only a feature >> of the full version of MS SQL Server? >> >> 2. How can I restore a database from a backup? >> >> Any help is appreciated. >> Tibor,
I have tried the method described in your article. Unfortunately, it does not seem to work. When I try to use sqlcmd -s .\sqlexpress -i c:\expressmaint.sql in a DOS-box, I get a timeout with a probable cause of MS SQL 2005 not supporting remote connections (limitation of Express?). Trying to launch the backup from the Windows Scheduler does not create a backup file. The sqlcmd process is shortly shown in the taskmanager, but the scheduled job ends with a result code ox1 (which is fault, I assume). The scheduler uses this command: sqlcmd -s. \sqlexpress -i "c:\MSSQLbackup.sql" where c:\MSSQLbackup.sql contains the following lines: exec expressmaint @database = 'ScanArt', @optype = 'DB', @backupfldr = 'd:\temp', @verify = 1 @reportfldr = 'd:\temp', @report = 1 There is no reportfile created, either. What am I doing wrong? Show quote > 1: Seems this article is for you: > http://www.sqldbatips.com/showarticle.asp?ID=27 > > 2: Using the RESTORE command. > > "Jos Roijakkers" <j.roijakk***@qred-it.nl> wrote in message > news:5d4a1f3d9dd28c840efa3e7622a@news.microsoft.com... > >> I use MS SQL Server Express 2005 (the 'unpaid' version). I managed to >> create a backup of my database using an option onder 'Server >> Objects'. My questions: >> >> 1. Is it possible to schedule this backup or is this only a feature >> of the full version of MS SQL Server? >> >> 2. How can I restore a database from a backup? >> >> Any help is appreciated. >> Jos Roijakkers (j.roijakk***@qred-it.nl) writes:
> I have tried the method described in your article. Unfortunately, it does Express supports remote connections, but it is off by default.> not seem to work. > When I try to use > > sqlcmd -s .\sqlexpress -i c:\expressmaint.sql > > in a DOS-box, I get a timeout with a probable cause of MS SQL 2005 not > supporting remote connections (limitation of Express?). I think (local)\sqlexpress is a better bet, as it will not use TCP/IP, but shared memory. > Trying to launch the backup from the Windows Scheduler does not create a I guess that if it does not work from a DOS box, it will not work from> backup file. The sqlcmd process is shortly shown in the taskmanager, but > the scheduled job ends with a result code ox1 (which is fault, I assume). > The scheduler uses this command: Winwdows Scheduler either. So make sure you are first on speaking terms through command-line window, before you use the Windows scheduler. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics