Adatbázis mentés/visszaállítás

Forrás

Logging in and Starting up OSQL| | A note about who can create backups

You can create a backup of the database by logging in as the system administrator or a Metrix administrator. Before you can log in as a Metrix administrator, you'll have to log in as a system administrator and create a user account and assign it to the MetrixAdmin user role. See Setting Up Users for more information. |# Enter the "DOS command line" mode of Windows by choosing Run from the Start menu and typing "command" into the dialog box.

1. At the DOS prompt, start OSQL and connect to your MSDE/SQL Server by typing:OSQL -U sa -P admin -S (local)\METRIX
   • sa is the username of an administrator for this MSDE/SQL Server installation
   • admin is the password for that user (it will be admin unless that has been changed).
   • (local) points to a server on the local computer. If you are accessing the server from a computer other than the one on which the server is installed, replace (local) with the name of the server.
2. This starts up OSQL and you can now begin using commands to work with your database(s).

   Backing-up a Database Using OSQL

   To create a backup file of an existing database, use the following command:BACKUP DATABASE dbname TO DISK = 'c:\path\to\backupfile.dat'
   GoWhere:

• dbname is the name of the database you are backing up (by default this will be something like "METRIX")
• c:\path\to\backupfilename.dat is the full path to the backup file you are creating. _Make a note of this location because you will need it later when you restore this file to the database server._Note: If you do not know the name of your existing METRIX database, open METRIX using the shift-bypass technique and choose "Connection" from the File menu. The database name will appear in the "Select the database on the server..." dropdown box.

  Restoring a New Database Using OSQL

  If you are creating a database that has never before existed on your instance of SQL/MSDE, you can use the following RESTORE command:RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat'
  Go

  Restoring an Existing Database Using OSQL

  If you are restoring a database that already exists on your instance of SQL/MSDE, you will need to enter the following command:RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat' WITH REPLACE
  Go{_}If that does not work, then you can enter the following statement as one continuous string, but follow the capitalizations and the spacing in the example._RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat' WITH MOVE
  'UpRunningSQL_dat' to 'c:\Program Files\Microsoft SQL
  Server\MSSQL$METRIX\Data\METRIXV120copy.mdf', MOVE 'UpRunningSQL_log'
  to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\METRIXV120copy.ldf'Where:
• c:\path\to\backupfile.dat is the full path to the file you created during the backup procedure above.
• 'c:\Program Files\Microsoft SQL Server...' is the path to your MSDE/SQL Server installation.
• METRIXV120copy.ldf is the new name of the log file for the database you created during the backup procedure above.
• METRIXV120copy.mdf is the new name of the data file for the database you created during the backup procedure above.You can get the logical file names of the MDF and the LDF using:RESTORE DATABASE m5 FROM DISK = 'D:\Metrix\2006 06 28 Go Live\20060628GoLive.dat' WITH MOVE 'METRIX 130' to
  'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\m5.mdf', MOVE 'METRIX 130_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\m5.ldf'or with:RESTORE FILELISTONLY FROM DISK = 'c:\path\to\backup.dat'

  External Resources

• More info on this "WITH MOVE" method
• MSDN reference on RESTORE DATABASE command
• MSDN on RESTORE FILELISTONLY
  Comments  (Hide)While setting up Metrix on an instance of MSDE that was installed to the D drive, rather than C, we kept seeing errors when trying to restore the database in OSQL. As it turned out, the .mdf and .ldf files that are referred to in the above statement have different names.
  These are the names that were used in this installation:
  UpRunningSQL_dat became Metrix120.mdf
  UpRunningSQL_log became Metrix120_log.ldf
  The revised string reads:
  RESTORE DATABASE newdb FROM DISK = 'c:\path\to\backupfile.dat' WITH MOVE
  'Metrix120.mdf' to 'c:\Program Files\Microsoft SQL
  Server\MSSQL$METRIX\Data\METRIXV120copy.mdf', MOVE 'Metrix120_log.ldf'
  to 'c:\Program Files\Microsoft SQL Server\MSSQL$METRIX\Data\METRIXV120copy.ldf'
  Posted by Kim Snyder at Feb 09, 2006 14:23 | Permalink | |