- Code: Select all
Use MyDatabase
Go
Exec sp_helpfile
This will return all the associated database files. Typically there are only 2 files, MyDatabase.mdf and MyDatabase.ldf. The .mdf is the core data and the .ldf is the log file for the database. The result set from this command(s) will also return the database size in KB, maxsize, and growth percentage.
- Code: Select all
Exec sp_detach_db 'MyDatabase'
This command will detach a given database from the sql server. Keep in mind sql server will not allow you to detach a database if its in use, meaning if there are connections to the database this command will error letting you know its in use. If you are able to detach succesfully, you can then go into the sql data folder where the the files exist (the path on the file system is in the "filename" field in the result set thats created above) and copy/move them to a new location. You can then use the command below to reattach them to sql server.
- Code: Select all
Exec sp_attach_db 'MyDatabase',
'E:\MsSql7\NewHome\MyDatabase_Data.mdf',
'E:\MsSql7\NewHome\MyDatabase_Log.ldf'
One thing to keep in mind is the "sp_attach_db" command can only be used with upto 16 files. If the database has more than 16 files then instead use 'Create Database' with the 'For Attach' clause. Only members of the 'sysadmin' server role can execute 'sp_detach_db' and 'sp_attach_db'.
