We have a small SQL Server 7 database (5mb) that needs transferring from one SQL server to another SQL server in the next state. What is the easiest way to back up this database and transfer it to the new database and install?
SQL Server databases can be copied in a multitude of ways. At the physical level, a SQL database is normally two files - an .ldf and an .mdf file. The .ldf file is a log file, and the .mdf file is the 'master data file'. There are also secondary data files (with the extension .ndf ). The SQL Server Service constantly has file locks on these files, and this makes it impossible to just copy the files without stopping the SQL Server Service itself - or detaching the database. Both of these file-copy methods are therefore undesirable. Nevertheless, they are popular because of their simplicity:
Method (a)
1. Stop Server and Copy mdf and ldf file.
2. In SQL Server Query Analyser, run the following (where the database name is 'TEST', and the master file and data file (test.mdf and test.ldf) are both in the default c:\mssql7\data\ directory) :
EXEC sp_attach_db @dbname = 'TEST' ,
@filename1 = 'c:\mssql7\data\test.mdf',
@filename2 = 'c:\mssql7\data\test.ldf'
Method (b)
1. In SQL Server Query Analyser, run the following to detach the database:
EXEC sp_detach_db @dbname = 'TEST'
2. Copy the mdf file to the desired location, say the c:\mssql7\data\ directory on another machine.
3. In SQL Server Query Analyser on the other machine (or connected via query analyser to the other machine), run the following to attach the database:
EXEC sp_attach_single_file_db @dbname = 'TEST',
@physname = 'c:\mssql7\data\test.mdf'
There are other, more robust methods of copying a database:
Method (c)
Use Data Transformation services or bcp.exe to copy the file (but this method requires connection between machines)
Method (d) **Preferred**
1. Backup the database to a single file (say, "test.bak")
2. Copy the "test.bak" file to the desired drive on the desired machine (say, c:\)
3. In SQL Server Query Analyser , enter and run the following to find out the logical file names of the data file and the log file
RESTORE FILELISTONLY
FROM DISK = 'C:\test.bak'
In this case, the logical file names produced were "Test_dat" and "Test_log"
4. In SQL Server Query Analyser, run the following, to create two new physical files ("test.mdf" and "test.ldf"), and register the database "TEST":
RESTORE DATABASE TEST
FROM DISK = 'C:\Test.bak'
WITH MOVE 'Test_dat' TO 'C:\MSSQL7\Data\Test.mdf',
MOVE 'Test_log' TO 'C:\MSSQL7\Data\Test.ldf'
Method (d) is preferred, as it allows the backup to be transported easily.
via a network or on disk/CD (unlike method (c)),and it doesn't interrupt the SQL Server Service, or any database availability (as for methods (a) and (b)) This allows for minimal downtime, and we move closer to the ideal 24x7 database system.